-
Range of column names from table
Actually my requirement is i want to select particular range of columns from a table containing 200 columns..for example i want to select all columns except 70th and 80th column..is it possible to select data of that particular 198 columns without specifying all 198 column names.
-
Bizarre requirement. You will need to know the names of the columns. Use CTAS and then drop the two columns you don't want. Then select * from the new table.
-
bizarre requirements may call for bizarre solutions... how about setting those unwanted columns to unused, select * then activate them back? LOL
Last edited by PAVB; 04-12-2010 at 04:53 PM.
Reason: typo
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
What version of Oracle allows you to mark an unused column as usable again?
-
I think it was on 8i the first time we have to recover an unused column - requires a little working knowledge of tab$ and col$ tables but it works just fine.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by stecal
Bizarre requirement. You will need to know the names of the columns. Use CTAS and then drop the two columns you don't want. Then select * from the new table.
Thanks for ur reply, but u don't understand what i asked,u said make that columns as unused and access the table...what abt other users if they want to access that columns and one more thing that two columns has been using for other purpose..but iam not using that columns in my program.
-
Originally Posted by jayasankar
Thanks for ur reply, but u don't understand what i asked,u said make that columns as unused and access the table...what abt other users if they want to access that columns and one more thing that two columns has been using for other purpose..but iam not using that columns in my program.
you need to select the columns you want
-
Originally Posted by PAVB
I think it was on 8i the first time we have to recover an unused column - requires a little working knowledge of tab$ and col$ tables but it works just fine.
you said it is possible to recover an unused columns...so could u please send that process to recover unused columns.
-
Marking a column unused was introduced in 9i. Instead of spending a lot of time waiting for a column to be dropped, Oracle introduced unused. Logically gone, but data still there, waiting to be dropped later when performance wouldn't be as impacted. There is no supported way of marking or setting a column usable again. Any direct edits or hacks on data dictionary tables may render your database unsupportable by Oracle. But go right ahead.
-
Originally Posted by jayasankar
you said it is possible to recover an unused columns...so could u please send that process to recover unused columns.
Please.
I found your business requirement to be a really bizarre one so I just tried to figure out the most bizarre possible solution - I actuallly warned about it in my post.
Do it the right way, query the columns you need, write view, create a temp table - do it the way you want but please don't enter bizarro world.
I appologize if my post confused you or sent you in the wrong direction.
Last edited by PAVB; 04-15-2010 at 11:23 PM.
Reason: typo
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|