Please Help

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Compact Edition » Please Help

Please Help

Rate Topic

Display Mode

Topic Options

Author

Message

twsexela

Posted Monday, September 07, 2009 3:21 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 07, 2009 3:15 PM
Points: 1, Visits: 1

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.

Post #783917

Jeff Moden

Posted Monday, September 07, 2009 4:00 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 18,300, Visits: 12,321

"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

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #783929

Silverfox

Posted Tuesday, September 08, 2009 8:07 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 2,595, Visits: 988

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.

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw

Post #784274

« Prev Topic | Next Topic »

Permissions