OraFAQ Forum: Server Administration » General Oracle script.

Home » RDBMS Server » Server Administration » General Oracle script.

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

General Oracle script. [message #58765]

Mon, 29 September 2003 13:10

Antonio Marcos Souza
Messages: 13
Registered: September 2003

Junior Member

Hi, I need export script of the metadatas (tables, procedures, etc) to a file installation. How do I to generate a complete file script ?

Report message to a moderator

Re: General Oracle script. [message #58766 is a reply to message #58765]

Mon, 29 September 2003 13:19

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

-- 1. do the regular export

exp dbaUser/password owner=scott file=myfile.dmp

-- 2. extract the ddl from the dmp
--    use import
--    this will not do the the import, but read the dump file and generated ddl into a readable text file 
--    in this case the ddl is generated into mYscript.sql

imp dbauser/password fromuser=scott touser=someuser file=myfile.dmp indexfile=mYscript

Report message to a moderator

Re: General Oracle script. [message #58767 is a reply to message #58765]

Mon, 29 September 2003 13:56

Thiru
Messages: 1089
Registered: May 2002

Senior Member

Yet another versatile option : DBMS_METADATA.GET_DDL

SQL> select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;

DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME)
--------------------------------------------------------------------------------

CREATE TABLE "THIRU"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
"GRADE" VARCHAR2(10)
) 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 "USERS"

CREATE TABLE "THIRU"."PLAN_TABLE"
( "STATEMENT_ID" VARCHAR2(30),
"TIMESTAMP" DATE,
"REMARKS" VARCHAR2(80),
"OPERATION" VARCHAR2(30),
"OPTIONS" VARCHAR2(255),
"OBJECT_NODE" VARCHAR2(128),
"OBJECT_OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"OBJECT_INSTANCE" NUMBER(*,0),
"OBJECT_TYPE" VARCHAR2(30),
"OPTIMIZER" VARCHAR2(255),
"SEARCH_COLUMNS" NUMBER,
"ID" NUMBER(*,0),
"PARENT_ID" NUMBER(*,0),
"POSITION" NUMBER(*,0),
"COST" NUMBER(*,0),
"CARDINALITY" NUMBER(*,0),
"BYTES" NUMBER(*,0),
"OTHER_TAG" VARCHAR2(255),
"PARTITION_START" VARCHAR2(255),
"PARTITION_STOP" VARCHAR2(255),
"PARTITION_ID" NUMBER(*,0),
"OTHER" LONG,
"DISTRIBUTION" VARCHAR2(30),
"CPU_COST" NUMBER(*,0),
"IO_COST" NUMBER(*,0),
"TEMP_SPACE" NUMBER(*,0),
"ACCESS_PREDICATES" VARCHAR2(4000),
"FILTER_PREDICATES" VARCHAR2(4000)
) 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 "USERS"

CREATE TABLE "THIRU"."T"
( "OWNER" VARCHAR2(30) NOT NULL ENABLE,

.... and so on.. This has got lots of options to select and filter out , within the database.

-Thiru

Report message to a moderator

Previous Topic:	urgent...
Next Topic:	multi lingual DB configuration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 08:30:50 CDT 2024