generate DDL for tablespace objects [message #296707] |
Mon, 28 January 2008 16:36 |
ORA-MAN
Messages: 24 Registered: January 2008 Location: ===
|
Junior Member |
|
|
hi all,
Is there anyway to generate DDL script for all objects belongs to specific tablespace although those objects are different types and from different schemas.
thanks
|
|
|
|
|
Re: generate DDL for tablespace objects [message #296733 is a reply to message #296707] |
Mon, 28 January 2008 21:48 |
Arju
Messages: 1554 Registered: June 2007 Location: Dhaka,Bangladesh. Mobile:...
|
Senior Member |
|
|
I can give a demo ,
SQL> select table_name from dba_tables where tablespace_name='TBS1';
TABLE_NAME
------------------------------
TAB1
SQL> create directory dexp as '/oradata2';
Directory created.
SQL> host expdp arju/arju tablespaces=TBS1 directory=dexp
Export: Release 10.2.0.2.0 - Production on Tuesday, 29 January, 2008 9:47:30
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_TABLESPACE_01": arju/******** tablespaces=TBS1 directory=dexp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."TAB1" 1.363 MB 50610 rows
Master table "ARJU"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLESPACE_01 is:
/oradata2/expdat.dmp
Job "ARJU"."SYS_EXPORT_TABLESPACE_01" successfully completed at 09:47:55
SQL> host impdp arju/arju directory=dexp sqlfile=myfile.txt
Import: Release 10.2.0.2.0 - Production on Tuesday, 29 January, 2008 9:48:39
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "ARJU"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "ARJU"."SYS_SQL_FILE_FULL_01": arju/******** directory=dexp sqlfile=myfile.txt
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ARJU"."SYS_SQL_FILE_FULL_01" successfully completed at 09:48:41
SQL> !cat /oradata2/myfile.txt
-- CONNECT ARJU
-- new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "ARJU"."TAB1"
( "COL1" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS1" ;
|
|
|
Re: generate DDL for tablespace objects [message #296780 is a reply to message #296733] |
Tue, 29 January 2008 01:08 |
ORA-MAN
Messages: 24 Registered: January 2008 Location: ===
|
Junior Member |
|
|
dbms_metadata.get_ddl >> it will take single object as input, I need to give tablespace and get all objects DDL inside it.
ALSO
expdp is not supported on 9i ....
I am looking for 9i supported way..
thanks in advance
|
|
|
|
|
|
|
Re: generate DDL for tablespace objects [message #296806 is a reply to message #296707] |
Tue, 29 January 2008 02:10 |
ORA-MAN
Messages: 24 Registered: January 2008 Location: ===
|
Junior Member |
|
|
thanks always...
we can enhance the way by using dba_segments to get all the objects with all types in one stmt.
also use
SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT','TABLE_NAME','SCHEMA_NAME')FROM dual;
to get all grants permisions
also if I get time I'll test exp/imp way.
but are you sure by this way will have grants also?
regards
|
|
|