| Script that save all tablespaces to file [message #110432] |
Mon, 07 March 2005 10:25  |
yanivk
Messages: 2
Registered: March 2005
|
Junior Member |
|
|
All,
I need script for Oracle 9i, that save all tablespaces in the database to a file which can be edit and then can be run in diffrent database.
=========
Examples Out Put Of the Scrpt:
=========
CREATE TABLESPACE rbs
DATAFILE '/u02/oracle/V7.1.6/dbs/rbs2V716.dbf' SIZE 52428800
REUSE,'/u02/oracle/V7.1.6/dbs/rbsV716.dbf' SIZE 8388608 REUSE
DEFAULT STORAGE (INITIAL 131072 NEXT 131072
MINEXTENTS 2 MAXEXTENTS 121
PCTINCREASE 0)
ONLINE
/
CREATE TABLESPACE temp
DATAFILE '/u02/oracle/V7.1.6/dbs/tempV716.dbf' SIZE 563200 REUSE
DEFAULT STORAGE (INITIAL 131072 NEXT 131072
MINEXTENTS 1 MAXEXTENTS 121
PCTINCREASE 0)
ONLINE
/
CREATE TABLESPACE users
DATAFILE '/u02/oracle/V7.1.6/dbs/usrV716.dbf' SIZE 1048576 REUSE
DEFAULT STORAGE (INITIAL 10240 NEXT 10240
MINEXTENTS 1 MAXEXTENTS 121
PCTINCREASE 50)
ONLINE
/
thanks a lot
|
|
|
|
| Re: Script that save all tablespaces to file [message #110438 is a reply to message #110432] |
Mon, 07 March 2005 10:54  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
--
-- just spool the output to a file you want
--
mag@mutation_mutation > get ddl.ddl
1 set long 500000
2 set linesize 1000
3 SET HEAD off
4* SELECT upper(DBMS_METADATA.GET_DDL('TABLESPACE',D.TABLESPACE_NAME))||';' FROM dba_tablespaces D;
mag@mutation_mutation > @ddl.ddl
CREATE TABLESPACE "SYSTEM" DATAFILE
'/U01/APP/ORACLE/ORADATA/MUTATION/SYSTEM01.DBF' SIZE 262144000 REUSE
AUTOEXTEND ON NEXT 10485760 MAXSIZE UNLIMITED
LOGGING ONLINE PERMANENT BLOCKSIZE 16384
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
;
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/U01/APP/ORACLE/ORADATA/MUTATION/UNDOTBS01.DBF' SIZE 209715200 REUSE
AUTOEXTEND ON NEXT 5242880 MAXSIZE UNLIMITED
BLOCKSIZE 16384
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'/U01/APP/ORACLE/ORADATA/MUTATION/TEMP01.DBF' SIZE 41943040 REUSE
AUTOEXTEND ON NEXT 655360 MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
;
CREATE TABLESPACE "USERS" DATAFILE
'/U01/APP/ORACLE/ORADATA/MUTATION/USERS01.DBF' SIZE 26214400 REUSE
AUTOEXTEND ON NEXT 1310720 MAXSIZE UNLIMITED
LOGGING ONLINE PERMANENT BLOCKSIZE 16384
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
;
CREATE TABLESPACE "TOOLS" DATAFILE
'/U01/APP/ORACLE/ORADATA/MUTATION/TOOLS01.DBF' SIZE 10485760 REUSE ,
'/U01/APP/ORACLE/ORADATA/MUTATION/TOOLS02.DBF' SIZE 41943040 REUSE ,
'/U01/APP/ORACLE/ORADATA/MUTATION/TOOLS03.DBF' SIZE 10485760 REUSE
LOGGING ONLINE PERMANENT BLOCKSIZE 16384
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
