OraFAQ Forum: Server Utilities » generate DDL for tablespace objects

Home » RDBMS Server » Server Utilities » generate DDL for tablespace objects

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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. Confused

thanks

Report message to a moderator

Re: generate DDL for tablespace objects [message #296708 is a reply to message #296707]

Mon, 28 January 2008 16:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

dbms_metadata.get_ddl

Report message to a moderator

Re: generate DDL for tablespace objects [message #296732 is a reply to message #296707]

Mon, 28 January 2008 21:43

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

1. expdp ... tablespace=my_tbs
2. impdp ... sqlfile

Report message to a moderator

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 , 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" ;

Report message to a moderator

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

Report message to a moderator

Re: generate DDL for tablespace objects [message #296784 is a reply to message #296707]

Tue, 29 January 2008 01:12

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.

Report message to a moderator

Re: generate DDL for tablespace objects [message #296790 is a reply to message #296707]

Tue, 29 January 2008 01:34

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.

Report message to a moderator

Re: generate DDL for tablespace objects [message #296796 is a reply to message #296707]

Tue, 29 January 2008 01:42

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.

Report message to a moderator

Re: generate DDL for tablespace objects [message #296798 is a reply to message #296707]

Tue, 29 January 2008 01:46

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

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 06:29:43 CDT 2024