Drop table

[kbengmy]

I accidentally drop a table (eg MSF900) in my production server and has been commited.

I restore the entire DB to a offline server then i export (without index) the table (eg MSF900) to a dmp file. From the offline server i copied the dmp file and save it into my production server. From there i import the table back to my DB at production server.

The question here is,
1. i can restore my table but how about my indexes ?
2. should i export indexes as well? if so, what's the command to do so?

 

[SantaMufasa]

KBengmy,

When you export a table, the definition of the indexes also exports to the dump file. When you import the table, the index definitions, by default, also execute and the indexes are re-populated with the imported data.

You can confirm this behaviour by issuing this command for your table (while connected via SQL*Plus to the user that owns the table):

select index_name from user_indexes where table_name = '<your table name>';

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[kbengmy]

hmmm ... i'm not quite understand, my table name is like MSF901 and index name is like MSF90101IX0 ...

i have to monitor the table/index extend everyday as the max extend is 121, therefore i have to keep track of the current extend for table/index.

If the current extend for table/index reaches 121, then i have to 're-initialize' the current extend to 50. How i done this is
1. check the current bytes of the index
2. export the table out then drop the table
3. import the table back
4. run sql script, the content is something like this
CREATE INDEX MSF90101IX0 ON MSF901 ( ....CODE,SEQ_NO_901)
TABLESPACE IDX_900_999
PCTFREE 10
STORAGE(INITIAL 430123 NEXT 4K PCTINCREASE 0 MAXEXTENTS 50)

430123 will replace by the bytes i checked on step 1

Now my question is when i accidentally drop a table without knowing the current bytes of the index, how to run the sql script ? (as i need to change the bytes number in the sql srcipt)

 

[SantaMufasa]

KBengmy,

Instead of using your current storage-parameter strategy, you can avoid your problem virtually forever by using this strategy:

CREATE INDEX MSF90101IX0 ON MSF901 ( ....CODE,SEQ_NO_901) 
TABLESPACE IDX_900_999
PCTFREE 10
STORAGE(INITIAL [b]1M[/b] NEXT [b]1M[/b] PCTINCREASE 0 MAXEXTENTS [b]UNLIMITED[/b])

Let us know if this satisfies your need or if you have any questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[taupirho]

I seem to recall that MAXEXTENTS UNLIMITED does not work in Oracle 7 (at least not in the version 7.3.4 that I was using)

 

[SantaMufasa]

Taupirho,

Good catch...Thinking about it further, I believe I remember something like 512 being the max for V7. In anycase, initial and next extents of 1M allow an object of half a gig, and if that isn't enough, increasing initial and next to 10M allows a 5-gig object, thus, one can manage an object's "girth" by that method.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]