Can Any1 Out There Help Me On This?

[Kalashnikov]

I GOT THE FOLLOWING ASSIGNMENT THAT'S DUE IN A FEW DAYS…BT…..I'M KINDA LOST ON IT……….ANY 1 GOT AN IDEA?

Task 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 relocation 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.

Task 2:

Table A has 100,000 rows, each with a key of 10 bytes, and data of 90 bytes.

Table B has 50,000 rows, each with a key of 20 bytes, and data of 80 bytes.

Table C is a dependent to table A with 10 rows for each parent row in A, each row with a key of 12 bytes, and data of 78 bytes.

Table D facilitates a many-to-many relationship between tables A and B, and has 20 bytes of additional data in each row. 10% of the rows in table A have a relationship with at least one row in table B, and 40% of the rows in B have a relationship with at least one row in table A. On average, only 1% of all possible relationships is actually used (i.e. places data into table D).

Table E facilitates a many-to-many relationship between rows in the same table B, based on a unique key in table B, which is 8 bytes long. On average, three-times as many relationships exist between rows, as there are rows in B. No additional data are placed in table E.

Each table has an appropriate primary key, which is indexed (using a B-Tree). Each leaf page in the index can hold key entries in 50% of its page size of 8Kb (8192 bytes). Each key entry is (key-length + 4) bytes long.

Use a spreadsheet to compute a size estimate for a table space that holds these five tables and their indexes and still has 50% free space after loading all data.

For this task you do not need to estimate any additional space for overhead.

Attach a description of the steps you used to derive the estimate, as well as all the formulas used. These details are critical for the evaluation of your results.

[Author]

Posts