Home » RDBMS Server » Server Utilities » generate DDL for tablespace objects
generate DDL for tablespace objects [message #296707] Mon, 28 January 2008 16:36 Go to next message
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. Confused

thanks
Re: generate DDL for tablespace objects [message #296708 is a reply to message #296707] Mon, 28 January 2008 16:41 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
dbms_metadata.get_ddl
Re: generate DDL for tablespace objects [message #296732 is a reply to message #296707] Mon, 28 January 2008 21:43 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

1. expdp ... tablespace=my_tbs
2. impdp ... sqlfile
Re: generate DDL for tablespace objects [message #296733 is a reply to message #296707] Mon, 28 January 2008 21:48 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I can give a demo , Smile

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 Go to previous messageGo to next message
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 #296784 is a reply to message #296707] Tue, 29 January 2008 01:12 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

To use DBMS_METADATA you can write a script . Take table_name from dba_tables. And then make a scipt.

You can also use transport_tablespace feature with exp with some restrictions. And then invoke imp command with indexfile or show=y.
Re: generate DDL for tablespace objects [message #296790 is a reply to message #296707] Tue, 29 January 2008 01:34 Go to previous messageGo to next message
ORA-MAN
Messages: 24
Registered: January 2008
Location: ===
Junior Member
thanks Arju for your quick response.

we are talking here about thousands of objects with different types and from different schemas in one TBS.
Re: generate DDL for tablespace objects [message #296796 is a reply to message #296707] Tue, 29 January 2008 01:42 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I am also talking about that. Just make a script.

Or use exp/imp.
Re: generate DDL for tablespace objects [message #296798 is a reply to message #296707] Tue, 29 January 2008 01:46 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

For your help I made one, Spool and just run it.

select 'select dbms_metadata.get_ddl(''TABLE'','''||table_name 
||''','''||owner||''')' || 'from dual;' from dba_tables where 
tablespace_name='SYSAUX';

[Updated on: Tue, 29 January 2008 01:50]

Report message to a moderator

Re: generate DDL for tablespace objects [message #296806 is a reply to message #296707] Tue, 29 January 2008 02:10 Go to previous message
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

Previous Topic: ORA-01461: can bind a LONG value only for insert into a LONG column
Next Topic: exporting table in a different tablespace
Goto Forum:
  


Current Time: Sun Dec 04 14:48:43 CST 2016

Total time taken to generate the page: 0.07421 seconds