set echo on feedback on lines 80
spool /tmp/exampleuser.lst
create user EXAMPLE identified by EXAMPLE default tablespace SMALLDBDATA 
temporary tablespace temp quota unlimited on SMALLDBDATA 
quota unlimited on SMALLDBINDEX profile DEFAULT
/
grant UNLIMITED TABLESPACE to EXAMPLE
/
/* Execute the following after connecting to the respective owners
Execute the above after connecting to the respective owners */
create role UPDT_SCOTT
/
grant UPDT_SCOTT to EXAMPLE
/
grant CREATE PUBLIC SYNONYM to UPDT_SCOTT
/
/* Execute the following after connecting to the respective owners
grant SELECT on SCOTT.DEPT to UPDT_SCOTT
/
grant UPDATE on SCOTT.DEPT to UPDT_SCOTT
/
grant SELECT on SCOTT.EMP to UPDT_SCOTT
/
grant UPDATE on SCOTT.EMP to UPDT_SCOTT
/
grant SELECT on SCOTT.SALGRADE to UPDT_SCOTT
/
Execute the above after connecting to the respective owners */
set echo off
spool off

set echo on feedback on lines 80
spool /tmp/exampletabs.lst
create table EXAMPLE.ACCOUNTS
(ACCOUNT_ID                              NUMBER    (4,0)   
,BAL                                     NUMBER    (11,2)  
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.ACTION
(ACCOUNT_ID                              NUMBER    (4,0)   
,OPER_TYPE                               VARCHAR2  (1)     
,NEW_VALUE                               NUMBER    (11,2)  
,STATUS                                  VARCHAR2  (45)    
,TIME_TAG                                DATE              
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.BINS
(BIN_NUM                                 NUMBER    (2,0)   
,PART_NUM                                NUMBER    (4,0)   
,AMT_IN_BIN                              NUMBER    (4,0)   
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.DATA_TABLE
(EXPER_NUM                               NUMBER    (2,0)   
,N1                                      NUMBER    (5,0)   
,N2                                      NUMBER    (5,0)   
,N3                                      NUMBER    (5,0)   
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.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)   
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.INVENTORY
(PROD_ID                                 NUMBER    (5,0)   
,PRODUCT                                 VARCHAR2  (15)    
,QUANTITY                                NUMBER    (5,0)   
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.JOURNAL
(ACCOUNT_ID                              NUMBER    (4,0)   
,ACTION                                  VARCHAR2  (45)    
,AMOUNT                                  NUMBER    (11,2)  
,DATE_TAG                                DATE              
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.NUM1_TAB
(SEQUENCE                                NUMBER    (3,0)   
,NUM                                     NUMBER    (4,0)   
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.NUM2_TAB
(SEQUENCE                                NUMBER    (3,0)   
,NUM                                     NUMBER    (4,0)   
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.PURCHASE_RECORD
(MESG                                    VARCHAR2  (45)    
,PURCH_DATE                              DATE              
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.RATIO
(SAMPLE_ID                               NUMBER    (3,0)   
,RATIO                                   NUMBER            
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.RESULT_TABLE
(SAMPLE_ID                               NUMBER    (3,0)   
,X                                       NUMBER            
,Y                                       NUMBER            
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.SUM_TAB
(SEQUENCE                                NUMBER    (3,0)   
,SUM                                     NUMBER    (5,0)   
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
create table EXAMPLE.TEMP
(NUM_COL1                                NUMBER    (9,4)   
,NUM_COL2                                NUMBER    (9,4)   
,CHAR_COL                                VARCHAR2  (55)    
) tablespace SMALLDBDATA
storage (initial 512K next 512K minextents 1)
/
set echo off
spool off

set echo on feedback on lines 80
spool /tmp/examplecons.lst
alter table EXAMPLE.ACCOUNTS
add constraint SYS_C00340
check (ACCOUNT_ID IS NOT NULL
)  
/
alter table EXAMPLE.ACTION
add constraint SYS_C00341
check (ACCOUNT_ID IS NOT NULL
)  
/
alter table EXAMPLE.ACTION
add constraint SYS_C00342
check (OPER_TYPE IS NOT NULL
)  
/
alter table EXAMPLE.ACTION
add constraint SYS_C00343
check (TIME_TAG IS NOT NULL
)  
/
alter table EXAMPLE.BINS
add constraint SYS_C00344
check (BIN_NUM IS NOT NULL
)  
/
alter table EXAMPLE.EMP
add constraint SYS_C00345
check (EMPNO IS NOT NULL
)  
/
alter table EXAMPLE.INVENTORY
add constraint SYS_C00346
check (PROD_ID IS NOT NULL
)  
/
alter table EXAMPLE.JOURNAL
add constraint SYS_C00347
check (ACCOUNT_ID IS NOT NULL
)  
/
alter table EXAMPLE.JOURNAL
add constraint SYS_C00348
check (ACTION IS NOT NULL
)  
/
alter table EXAMPLE.JOURNAL
add constraint SYS_C00349
check (DATE_TAG IS NOT NULL
)  
/
alter table EXAMPLE.NUM1_TAB
add constraint SYS_C00350
check (SEQUENCE IS NOT NULL
)  
/
alter table EXAMPLE.NUM2_TAB
add constraint SYS_C00351
check (SEQUENCE IS NOT NULL
)  
/
alter table EXAMPLE.RATIO
add constraint SYS_C00352
check (SAMPLE_ID IS NOT NULL
)  
/
alter table EXAMPLE.RESULT_TABLE
add constraint SYS_C00353
check (SAMPLE_ID IS NOT NULL
)  
/
alter table EXAMPLE.SUM_TAB
add constraint SYS_C00354
check (SEQUENCE IS NOT NULL
)  
/

set echo on feedback on lines 80
spool /tmp/exampleindx.lst
create UNIQUE index EXAMPLE.ACCOUNTS_INDEX on  EXAMPLE.ACCOUNTS
(ACCOUNT_ID                              
) tablespace SMALLDBINDEX
/
set echo off
spool off

set echo on feedback on lines 80
spool /tmp/exampleseqs.lst
set echo off
spool off

set echo on feedback on lines 80
spool /tmp/exampleview.lst
set echo off
spool off

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP,  Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.