tablespace reorganization

[fraze]

Ooops - forgot the online keyword in my test- Friday afternoon and all


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.0.0 - 64bit Production
PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for Solaris: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production

SQL> create table t1 (col1 varchar2(10));

Table created.

SQL> alter table t1 move tablespace tools;

Table altered.

[tamilselvan]

Use DBMS_SPACE_ADMIN pkg to change DMT to LMT and vice versa.

No need to move table.

Tamil

[slimdave]

Originally posted by tamilselvan
Use DBMS_SPACE_ADMIN pkg to change DMT to LMT and vice versa.

No need to move table.

Tamil

Disagree -- LMT is not the only desired outcome, and this option would leave the TS's v. messy.

[adrianomp]

What I want to do is:
- create new LMT-ts (small, medium, large and huge);
- 'alter table x move tablespace y online' all the tables to the correct ts;
- 'alter index rebuild' all the indexes;
- drop the now empty old ts.

A piece of extra info: one can't move tables with LONG columns with ALTER TABLE ... MOVE

Don't see an option other than exp/imp for these.

[jmodic]

Originally posted by adrianomp
Don't see an option other than exp/imp for these.

Another option would be using SQL*Plus's COPY command.

[abhaysk]

Originally posted by marist89
I don't think online table moves are available in 8i. Maybe I'm wrong.

Any ways ONLINE only applies to IOT, so what big deal with 9.x or 8.x you gotta ur TRAN to wait until Move.

Originally posted by adrianomp
Don't see an option other than exp/imp for these.

Yet another will be (IF no TRAN going on typically can be used in case of DW systems where TRAN will be done as BATCH processing)

* Disable constraint (FKs)
* Lock Table x in exlusive
* Create Table X_TEMP Select * from X
* Drop X
* Rename X_Temp to X
* Enable constraints.

Abhay.

[pando]

Originally posted by abhaysk

* Disable constraint (FKs)
* Lock Table x in exlusive
* Create Table X_TEMP Select * from X
* Drop X
* Rename X_Temp to X
* Enable constraints.

hmmm if you drop X you drop all constraints associated with it!

[abhaysk]

Originally posted by pando
hmmm if you drop X you drop all constraints associated with it!

Oops, last step shud have been recreate constraints

Abhay.

[jmodic]

Originally posted by abhaysk
Yet another will be (IF no TRAN going on typically can be used in case of DW systems where TRAN will be done as BATCH processing)

* Disable constraint (FKs)
* Lock Table x in exlusive
* Create Table X_TEMP Select * from X

Adrianomp was talking about tables with LONG columns. So no, CTAS will not work for them. The only two options for those are still exp/imp or COPY. No other alternative, AFAIK.

[efrijters]

Wow! The thread live's on...

Recently I did some testing with the OEM's reorganization wizard. It created some nice scripts.
I think I'll test a script on a small table and work my way up to the larger ones...

I'll keep you posted...