OraFAQ Forum: Server Utilities » How to Generate table Creation Scripts of a specific user as it is in ORACLE 8

Home » RDBMS Server » Server Utilities » How to Generate table Creation Scripts of a specific user as it is in ORACLE 8

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

How to Generate table Creation Scripts of a specific user as it is in ORACLE 8 [message #72712]

Wed, 01 October 2003 02:01

Arvind Bhandari
Messages: 50
Registered: May 2003

Member

In Oracle 8, can All Tables,Procedures and all other objects Creation Script with Constraint, inital,next segment, Default tablespace,Primary Key Tablespace,Cluster, Column comment,Table comment with Proper fomrat generated at a 1 time. ie
full user script generate as it is

Example of 1 table

CREATE TABLE ACCMAST
(Acc_Cd Varchar2(6) ,
Acc_Desc Varchar2(50) NOT NULL,
Acc_Type Varchar2(1) NOT NULL,
Grp_cd Varchar2(2) NOT NULL,
Sub_Grp_cd Varchar2(2) ,
Sch_Cd Varchar2(2) ,
Ctrl_cd Varchar2(1) NOT NULL,
Curr_Bal Number(14,2) ,
Curr_Drcr Varchar2(1) ,
Opn_Bal Number(14,2) ,
Opn_Drcr Varchar2(1) ,
SUPP_NO NUMBER(3) ,
Acc_Cd_Type VarChar2(1) Default 'N',
Crt_By Varchar2(6) NOT NULL,
Crt_On Date NOT NULL,
constraint pk_accmast PRIMARY KEY (Acc_cd)
using index tablespace TBSP_TESTIND,
constraint fk_accmast_grp_cd Foreign key(Grp_cd) references bmgrp(Grp_Cd) INITIALLY DEFERRED DEFERRABLE,
constraint fk_accmast_sub_grp_cd Foreign key(Grp_cd, Sub_Grp_Cd) references bmSubgrp(Grp_cd,Sub_Grp_Cd)INITIALLY DEFERRED DEFERRABLE,
constraint chk_accmast_acc_type Check (Acc_Type IN('A', 'L', 'I', 'E')),
constraint chk_accmast_Ctrl_Cd Check (Ctrl_Cd IN('Y', 'N')),
constraint chk_accmast_Curr_DrCR Check (Curr_DrCR in('D', 'C' )),
constraint chk_accmast_Opn_DrCR Check (Opn_DrCR in('D', 'C' )),
constraint chk_accmast_Curr_Bal Check ( Curr_Bal >= 0 ),
constraint chk_accmast_OPn_Bal Check ( Opn_Bal >= 0 ))
TABLESPACE TBSP_TESTDAT
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 400K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50);

Arvind

Report message to a moderator

Re: How to Generate table Creation Scripts of a specific user as it is in ORACLE 8 [message #72718 is a reply to message #72712]

Wed, 01 October 2003 06:35

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

Senior Member
Account Moderator

in 9i, the functionality is built-in within sql using DBMS_METADATA.GET_DDL .
1. for lower versions you have to use any tools like sqlNavigator to extract the ddl.
2. or generate ddl using export / import  with indexfile option.
please refer this posting 
http://www.orafaq.net/msgboard/server/messages/21299.htm
3. i beleive the above method works good only for 8i,9i databases.
for 8.0 databases if indexfile option is not available use show=y during import and redirect the output to a file.
IT WOULD BE A LOT OF PAIN.

Report message to a moderator

Re: How to Generate table Creation Scripts of a specific user as it is in ORACLE 8 [message #72725 is a reply to message #72718]

Mon, 06 October 2003 00:52

Arvind Bhandari
Messages: 50
Registered: May 2003

Member

Thanks Mahesh, i have already tried through Export import utility but it doesnot generate like we want ie all Constraint (PK,FK,Check), inital,next segment, Default tablespace,Primary Key Tablespace,Cluster, table comment,Column comment etc. It generates like

CREATE TABLE "TEST"."MAMUSER"
("USER_CD" VARCHAR2(10) NOT NULL ENABLE,
"USER_NAME" VARCHAR2(50) NOT NULL ENABLE,
"PASSWORD" VARCHAR2(10) NOT NULL ENABLE,
"USER_TYPE" VARCHAR2(1) NOT NULL ENABLE,
"EXORD_FLAG" VARCHAR2(1),
"VALID_UPTO" DATE,
"STATUS" VARCHAR2(1),
"OYEAR" VARCHAR2(1) NOT NULL ENABLE,
"UYEAR" VARCHAR2(1) NOT NULL ENABLE,
"CYEAR" VARCHAR2(1) NOT NULL ENABLE,
"CREAT_BY" VARCHAR2(10) NOT NULL ENABLE,
"OPER_TYPE" VARCHAR2(1) NOT NULL ENABLE,
"OPER_TIME" DATE NOT NULL ENABLE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 153600 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "BSNLDAT"

... 22 rows

is Oracle 8 is not provide SQL Scripts Generator for complete user in 1 command like in SQL Server ?

Arvind

Report message to a moderator

Re: How to Generate table Creation Scripts of a specific user as it is in ORACLE 8 [message #72726 is a reply to message #72725]

Mon, 06 October 2003 06:30

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

Senior Member
Account Moderator

Nope.
there is no  builting functionality in oracle 8 and lower except for the exp/imp utility.
But you can use some tools like sqlnavigator etc.
the open source tool tORA is also very good pick.
I never user Toad ... u may also check into it
else, use dbaStudio
i checked oracle 805. it is having indexfile option in import.
if you can use indexfile option, u will see the
tables are first created, 
the tables are altered to create the constraints
and indexes are created...
something like this
REM  CREATE TABLE "MAG"."T1" ("TABLE_ID" NUMBER) PCTFREE 10 PCTUSED 40 
REM  INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536) TABLESPACE 
REM  "SYSTEM" ;
REM  ... 1 rows
REM  ALTER TABLE "MAG"."T1" ADD PRIMARY KEY ("TABLE_ID") USING INDEX 
REM  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536) TABLESPACE 
REM  "SYSTEM" ENABLE ;

Report message to a moderator