Mir Sayeed Hassan – Oracle Blog

Complete DDL CREATE Commands for Operations and Database Administration

Create user  

SQL> Create USER Robert IDENTIFIED BY Freeman DEFAULT TABLESPACE users_tbs  TEMPORARY TABLESPACE temp QUOTA 100M ON users_tbs QUOTA UNLIMITED ON data_tbs;

Create view 

SQL> Create OR REPLACE VIEW vw_emp_dept_10 AS SELECT * FROM EMP WHERE dept=10;

SQL> Create OR REPLACE VIEW vw_public_email AS SELECT ename_first, ename_last, email_address FROM EMP WHERE public=‘Y’;

Create synonym 

SQL> Create SYNONYM scott_user.emp FOR scott.EMP; 
SQL> Create PUBLIC SYNONYM emp FOR scott.EMP; 

Create role 

SQL> Create ROLE dev_role IDENTIFIED USING develop; 

Create rollback segment 

SQL> Create ROLLBACK SEGMENT r01 TABLESPACE RBS STORAGE (INITIAL 100m NEXT 100M MINEXTENTS 5 OPTIMAL 500M); 

Create sequence 

SQL> Create SEQUENCE mirdb_seq1 START WITH 1 INCREMENT BY 1 MAXVALUE 1000000 CYCLE CACHE; 

Create spfile 

Create SPFILE FROM PFILE;
Create SPFILE='/opt/oracle/admin/mirdbdb/pfile/spfilemirdbbd.ora' FROM PFILE='/opt/oracle/admin/mirdbdb/pfile/initmirdbbd.ora'; 

Create synonym 

SQL> Create SYNONYM scott_user. emp FOR scott.EMP; 
SQL>Create PUBLIC SYNONYM emp FOR scott.EMP; 

Create procedure 

SQL> Create OR REPLACE PROCEDURE emp_salary (p_empid IN NUMBER, p_increase IN NUMBER) AS
BEGIN 
UPDATE emp SET salary=salary*p_increase WHERE empid=p_empid;
END;
/

Create cluster 

SQL> Create CLUSTER pub_cluster (pubnum NUMBER) SIZE 8K PCTFREE 10 PCTUSED 60 TABLESPACE user_data; 

SQL> Create CLUSTER pub_cluster (pubnum NUMBER) SIZE 8K HASHKEYS 1000 PCTFREE 10 PCTUSED 60 TABLESPACE user_data; 

Create control file 

SQL> Create CONTROLFILE REUSE DATABASE "mirdbdb" NORESETLOGS NOARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 3 MAXDATAFILES 200 MAXINSTANCES 1 MAXLOGHISTORY 1000  LOGFILE
GROUP 1 ('/u01/app/oracle/mirdb/mirdb_redo1a.rdo', '/u02/app/oracle/mirdb/mirdb_redo1b.rdo') SIZE 500K,  GROUP 2 ('/u01/app/oracle/mirdb/mirdb_redo2a.rdo', '/u01/app/oracle/mirdb/mirdb_redo2b.rdo') SIZE 500K DATAFILE
'/u01/app/oracle/mirdb/mirdb_system_01.dbf ', '/u01/app/oracle/mirdb/mirdb_users_01.dbf ', '/u01/app/oracle/mirdb/mirdb_undo_01.dbf ', '/u01/app/oracle/mirdb/mirdb_sysaux_01.dbf ', '/u01/app/oracle/mirdb/mirdb_alldata_01.dbf '; 

Create database 

SQL> Create DATABASE prodb
MAXINSTANCES 1 MAXLOGHISTORY 1
MAXLOGFILES 5 MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE ‘/u01/app/oracle/mirdb/system01.dbf’ SIZE 250M REUSE AUTOEXTEND ON NEXT 10240K
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL DEFAULT  TEMPORARY TABLESPACE TEMP
TEMPFILE '/u01/app/oracle/mirdb/temp01.dbf'
SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED SYSAUX TABLESPACE
DATAFILE '/u01/app/oracle/mirdb/sysauxtbs01.dbf'
SIZE 300M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED UNDO TABLESPACE "UNDOTBS1"
DATAFILE '/u01/app/oracle/mirdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1
GROUP 2
GROUP 3 ;

Create database link 

SQL> Create DATABASE LINK mir_db_link
CONNECT TO current_user
USING 'mir_db';
Create PUBLIC DATABASE LINK mir_db_link CONNECT TO remote_user IDENTIFIED BY psicorp USING 'mir_db'; 

Create directory 

SQL> Create OR REPLACE DIRECTORY mirdir AS ‘/u01/app/oracle/mirdb/admin/directories/mirdir’; 

Create function 

SQL> Create OR REPLACE FUNCTION find_value_in_table (p_value IN NUMBER, p_table IN VARCHAR2, p_column IN VARCHAR2)
RETURN NUMBER IS 
v_found NUMBER;
v_sql VARCHAR2(2000);
BEGIN
v_sql:='SELECT 1 FROM '||p_table||' WHERE '||p_column|| ' = '||p_value;
execute immediate v_sql into v_found;
return v_found;
END;
/ 

Create index: Function-Based Index 

SQL> Create INDEX fb_upper_last_name_emp ON emp_info (UPPER(last_name) ); 

Create index: Global Partitioned Indexes

SQL> Create INDEX inx_part_mirdb_tab_01 ON store_sales (invoice_number) GLOBAL PARTITION BY RANGE (invoice_number)
(PARTITION part_001 VALUES LESS THAN (1000),
PARTITION part_002 VALUES LESS THAN (10000), 
PARTITION part_003 VALUES LESS THAN (MAXVALUE) ); 

SQL> Create INDEX inx_part_mirdb_tab_02 ON store_sales (store_id, time_id)
GLOBAL PARTITION BY RANGE (store_id, time_id) (PARTITION PART_001 VALUES LESS THAN 
(1000, TO_DATE('04-01-2026','MM-DD-YYYY') ) 
('/u01/app/oracle/mirdb/redo01.log') SIZE 102400K, ('/u01/app/oracle/mirdb/redo02.log') SIZE 102400K, ('/u01/app/oracle/mirdb/prodb\redo03.log') SIZE 102400K; 

Create index: Local Partitioned Indexes 

SQL> Create INDEX inx_part_mirdb_tab_01 ON mirdb_tab
(col_one, col_two, col_three)
LOCAL (PARTITION tbs_part_01 TABLESPACE part_tbs_01,
PARTITION tbs_part_02 TABLESPACE part_tbs_02,
PARTITION tbs_part_03 TABLESPACE part_tbs_03,
PARTITION tbs_part_04 TABLESPACE part_tbs_04);

SQL> Create INDEXinx_part_mirdb_tab_01 ON mirdb_tab (col_one, col_two, col_three) LOCAL STORE IN (part_tbs_01, part_tbs_02, part_tbs_03, part_tbs_04); Create INDEXinx_part_mirdb_tab_01 ON mirdb_tab (col_one, col_two, col_three) LOCAL STORE IN (part_tbs_01 STORAGE (INITIAL 10M NEXT 10M MAXEXTENTS 200), part_tbs_02,
part_tbs_03 STORAGE (INITIAL 100M NEXT 100M MAXEXTENTS 200), part_tbs_04 STORAGE (INITIAL 1000M NEXT 1000M MAXEXTENTS 200));

Create index: Local Sub partitioned Indexes 

SQL> Create INDEX sales_inx ON store_sales(time_id, store_id) STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED) LOCAL (PARTITION q1_2026,
PARTITION q2_2026, 
PARTITION q3_2026
(SUBPARTITION pq3202601, SUBPARTITION pq3202602, SUBPARTITION pq3202603, SUBPARTITION pq3202604, SUBPARTITION pq3202605),
PARTITION q4_2026
(SUBPARTITION pq4202601 TABLESPACE tbs_1, SUBPARTITION pq4202602 TABLESPACE tbs_1, SUBPARTITION pq4202603 TABLESPACE tbs_1, SUBPARTITION pq4202604 TABLESPACE tbs_1, SUBPARTITION pq4202605 TABLESPACE tbs_1, SUBPARTITION pq4202606 TABLESPACE tbs_1, SUBPARTITION pq4202607 TABLESPACE tbs_1, SUBPARTITION pq4202608 TABLESPACE tbs_1), PARTITION sales_overflow
(SUBPARTITION pqoflw01 TABLESPACE tbs_2, SUBPARTITION pqoflw02 TABLESPACE tbs_2, SUBPARTITION pqoflw03 TABLESPACE tbs_2, SUBPARTITION pqoflw04 TABLESPACE tbs_2)); 

Create index: Nonpartitioned Indexes 

SQL> Create INDEXinx_mirdbtab_01 ON mirdbtab(column_1);

SQL> Create UNIQUE INDEXinx_mirdbtab_01 ON mirdbtab(column_1, column_2, column_3); Create INDEXinx_mirdbtab_01 ON mirdbtab(column_1, column_2, column_3) TABLESPACE mirdb_indexes COMPRESS
STORAGE (INITIAL 10K NEXT 10K PCTFREE 10) COMPUTE STATISTICS;

SQL> Create BITMAP INDEX bit_mirdbtab_01 ON mirdb_tab(col_two) TABLESPACE mirdb_tbs;

Create materialized view 

SQL> Create MATERIALIZED VIEW emp_dept_mview1 TABLESPACE users BUILD IMMEDIATE REFRESH FAST ON COMMIT WITH ROWID ENABLE QUERY REWRITE AS  SELECT d.rowid deptrowid, e.rowid emprowid, e.empno, e.ename, e.job, d.loc FROM dept d, emp e WHERE d.deptno = e.deptno; 

SQL> Create MATERIALIZED VIEW emp_dept_mv3 TABLESPACE users BUILD IMMEDIATE REFRESH FAST ON COMMIT WITH ROWID DISABLE QUERY REWRITE AS SELECT d.rowid deptrowid, e.rowid emprowid, d.dname, d.loc, e.ename, e.job FROM dept d, emp e WHERE d.deptno (+) = e.deptno; 

Create materialized view: Partitioned Materialized View 

SQL> Create MATERIALIZED VIEW part_emp_mview1
PARTITION BY RANGE (hiredate)
(PARTITION month1
VALUES LESS THAN (TO_DATE('01-APR-2020', 'DD-MON-YYYY')) PCTFREE 0 PCTUSED 99 
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0) TABLESPACE users;

Create profile 

SQL> Create PROFILE prod_profile
LIMIT
SESSIONS_PER_USER 2 CONNECT_TIME 100000 IDLE_TIME 100000 LOGICAL_READS_PER_SESSION 1000000
PRIVATE_SGA 10m
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 3
PASSWORD_LOCK_TIME 30
PASSWORD_GRACE_TIME 5; 

Create spfile 

SQL> Create SPFILE FROM PFILE;
SQL> Create SPFILE='/opt/oracle/admin/mirdbdb/pfile/spfilemirdbbd.ora' FROM PFILE='/opt/oracle/admin/mirdbdb/pfile/initmirdbbd.ora'; 

SQL> Create TABLE mirdb_tab
(id NUMBER, current_value VARCHAR2(2000) ) COMPRESS;

SQL> Create TABLE parts (id NUMBER, version NUMBER, name VARCHAR2(30), Bin_code NUMBER, upc NUMBER, active_code VARCHAR2(1) NOT NULL CONSTRAINT ck_parts_active_code_01
CHECK (UPPER(active_code)= 'Y' or UPPER(active_code)='N'), CONSTRAINT pk_parts PRIMARY KEY (id, version)
USING INDEX TABLESPACE parts_index
STORAGE (INITIAL 1m NEXT 1m) )
TABLESPACE parts_tablespace
PCTFREE 20 PCTUSED 60 STORAGE ( INITIAL 10m NEXT 10m PCTINCREASE 0); 

Create tablespace: Permanent Tablespace 

SQL> Create TABLESPACE data_tbs
DATAFILE '/opt/oracle/mirdbdbs/data/mirdbdbs_data_tbs_01.dbf' SIZE 100m;
Create TABLESPACE data_tbs
DATAFILE '/opt/oracle/mirdbdbs/data/mirdbdbs_data_tbs_01.dbf' SIZE 100m FORCE LOGGING BLOCKSIZE 8k;
Create TABLESPACE data_tbs
DATAFILE '/opt/oracle/mirdbdbs/data/mirdbdbs_data_tbs_01.dbf' SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; Create TABLESPACE data_tbs
DATAFILE '/opt/oracle/mirdbdbs/data/mirdbdbs_data_tbs_01.dbf' SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

Create a Bigfile tablespace

SQL> Create BIGFILE TABLESPACE data_tbs DATAFILE ‘/u01/app/oracle/mirdb/oradata/tbs_01.dbf’ SIZE 10G; 

Create tablespace: Temporary Tablespace 

SQL> Create TABLESPACE temp_tbs TEMPFILE ‘/u01/app/oracle/mirdb/oradata/mirdb/temp_tbs_01.tmp' SIZE 100m; 

Create tablespace: Undo Tablespace 

SQL> Create TABLESPACE undo_tbs TEMPFILE ‘/u01/app/oracle/mirdb/oradata/undo_tbs_01.tmp' SIZE 1g RETENTION GUARANTEE; 

Create trigger 

SQL> Create OR REPLACE TRIGGER emp_comm_after_insert BEFORE INSERT ON emp FOR EACH ROW
DECLARE
v_sal number; 
v_comm number;
BEGIN
-- Find username of person performing the INSERT into the table v_sal:=:new.salary;
:new.comm:=v_sal*.10;
END;
/ 

=====Hence created and used in our test env=====