September 7, 2009 at 3:21 pm
You are the junior DBA in a large company. A particular table in your database using an almost full tablespace with multiple small data files has been identified as needing reorganization and re-location to another set of disks. (The data alone require at minimum the space of three disks).
Your superior DBA has decided that this has to be done by extracting all data of the table to a sequential set of files on tapes, dropping the current table, recreating it on the new set of disks, and then re-loading the data after properly sorting them. An application programmer will do the tasks of unloading, sorting and reloading the data – that is not your responsibility.
Your task is to drop and recreate the table (and tablespace) on the new set of disks in such a way that after reloading the data, the users will not be impacted in any way when using the table and its data and the new table will provide for 50% free space for future additions to the data.
Explore what information from the existing table and from the catalog you can use, and describe step-by-step what you as DBA would have to do to re-create the table according to specification. Hint: Which objects in the catalog (DDL) relate to or are dependent on a table?
Condition: You can use SQL and DDL, but not an integrated database management tool like the Oracle Enterprise Manager.
September 7, 2009 at 4:00 pm
"Full tablespace"... this is obviously an Oracle interview question. You should consult an Oracle forum because an SQL Server answer will be totally different.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2009 at 8:07 am
As Jeff has mentioned, you would be better off asking this question on a oracle forum. Dropping the table and creating tablespace is not that difficult. but you are asked for a step by step scenario which is designed to test your complete knowledge of the process.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply