-
I have a table that has been designed wrongly.
The initial extent is 5m and next extent is 10m,etc of which both the initial and the next
extents have been used so far. I want to make the initial and next to
be 10m each.
(NOTE: I know that you can not alter a table to change the initial extent).
But my question is if I export the table, truncate it, then import it
with the idea of making all the extents one. Can I at this point change the initial
extent after I have truncated the table since there will be no data in it
before importing it back.
These are the steps I'm thinking of using:
1.Export the table
2.Truncate the table
3.Alter the table to change the initial and next extents
4.Import the table
P.S. I have always done it like this to change the next extent and it
worked great but I have not done it to change the initial extent before.
Any ideas "guys"?
-
cant we create a new table from the old table and specify storage parameters for the new table while creating that?
CREATE TABLE new(
oldcol1, oldcol2, oldcol3, deptno )
TABLESPACE LARGE_TS
STORAGE (INITIAL 500M NEXT 5M PCTINCREASE 0)
AS
SELECT oldcol1, oldcol2, oldcol3, deptno
FROM old
let me know
-
No, this will not work. In step 2., when you truncate the table, it will preserve the storage parameters and there is no way you can change the initial extent. Basicaly you have two options:
a) If you are on 8i, you can simply reebuild the table with:
ALTER TABLE blahblah ..... MOVE ....;
With this clause you can specify new storage parameters (including INITIAL) and you can rebuild online. No need to export, truncate, import....
b) If you are on pre-8i, you would have to export, drop the table, create the same table table with new storage parameters and import the data. Howevere, you would also have to take care of all constraints, grants, indexes etc... as you will loose all this with DROP command.
HTH,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
1.Export the table with compress=n
2.drop the table
3.Create the table to change the initial and next extents
4.Import the table with ignore=y
Reddy,Sam
-
Didn't want to confuse anyone, my previous post was ment as a response directly to clinton's question, not to yenni143's sugestion...
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
alter table move
I will give to you another sugestion : if the oracle database in runing in 8i,
you can perform the command alter table move tablespace.
I did this test:
create table test (a varchar2(30))
tablespace td_users
pctfree 10
pctused 40
storage (initial 10k next 10k pctincrease 0);
ALTER TABLE TEST MOVE TABLESPACE TD_TOOLS
PCTFREE 5
pctused 50
storage (initial 20k next 50k pctincrease 10);
The results were okay!
After that, you can move the table back to td_users.
Good luck!
Valentin
-
thanks for all the responses
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
|