| Export/Import Full, Restructure tablespaces? [message #70572] |
Mon, 01 July 2002 21:51  |
Kevin White
Messages: 1
Registered: July 2002
|
Junior Member |
|
|
I'm using Oracle 9.0.1 on Linux.
After several major changes to our database's definition, I find myself with some tablespace setups that are less than ideal.
I'd LIKE to do a full export, make a new database, make all the tablespaces with the new parameters, then import. However, I fear that the import process will blow away my nicely crafted tablespaces and replace them with the bad ones I'm trying to get rid of. Is this true?
Note that I intend on having all of the same tablespaces: they just won't be all the same size and broken up into as many file parts. Also, I'd like to turn on Segment Space Management in the process.
So, assuming my thoughts about export/import are correct, what is the best way to go about this? I'd really like to get everything else a full export/import gets me, just not the tablespaces.
Thanks...
Kevin White
|
|
|
|
| Re: Export/Import Full, Restructure tablespaces? [message #70575 is a reply to message #70572] |
Tue, 02 July 2002 06:58  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Here is a script that will create a script of your existing tablespaces. This is an old script and may need changed if you use local ts.
spool gents.lst
set serveroutput on size 1000000
DECLARE
CURSOR get_ts IS SELECT * FROM dba_tablespaces
WHERE tablespace_name != 'SYSTEM';
CURSOR get_df (p_ts VARCHAR2) IS
SELECT * from dba_data_files
WHERE tablespace_name = p_ts;
l_str VARCHAR2(10);
BEGIN
FOR ts_rec IN get_ts LOOP
dbms_output.put_line ('CREATE TABLESPACE '||ts_rec.tablespace_name);
-- For each tablespace loop through the datafiles
FOR df_rec IN get_df (ts_rec.tablespace_name) LOOP
IF get_df%ROWCOUNT = 1 THEN
l_str := 'DATAFILE';
ELSE
l_str := ',';
END IF;
dbms_output.put_line (l_str||' '
||chr(39)||df_rec.file_name||chr(39)
||' SIZE '||df_rec.bytes||' REUSE ');
END LOOP;
dbms_output.put_line ('DEFAULT STORAGE (INITIAL '||ts_rec.initial_extent
||' NEXT '||ts_rec.next_extent
||' MINEXTENTS '||ts_rec.min_extents
||' MAXEXTENTS '||ts_rec.max_extents
||' PCTINCREASE '||ts_rec.pct_increase||' ) ONLINE;');
dbms_output.new_line;
END LOOP;
END;
/
spool off
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
