-
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.
-
Use DBMS_SPACE_ADMIN pkg to change DMT to LMT and vice versa.
No need to move table.
Tamil
-
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.
-
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.
An ounce of action is worth a ton of theory.
—Friedrich Engels
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Re: Re: tablespace reorganization
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Re: Re: Re: tablespace reorganization
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!
-
Re: Re: Re: Re: tablespace reorganization
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Re: Re: Re: tablespace reorganization
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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...
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|