Oracle CREATE Commands Cheat Sheet for Database administrators as Best Practices.
Posted by Mir Sayeed Hassan on March 28th, 2026
Oracle CREATE Commands Cheat Sheet for Database administrators as Best Practices.
CREATE USER: is used to define and manage user access within the Oracle database system.
CREATE USER mir IDENTIFIED BY mirdb;
or
CREATE USER mir IDENTIFIED BY mir12345 DEFAULT TABLESPACE user_tbs; TEMPORARY TABLESPACE temp QUOTA 512M ON users_tbs QUOTA UNLIMITED ON ora- data_tbs;
CREATE VIEW: is used to define a virtual table based on a query to simplify data access and enhance security in Oracle.
CREATE OR REPLACE VIEW vw_emp_dept_10 AS SELECT * FROM EMP WHERE dept=10; CREATE OR REPLACE VIEW vw_public_email AS SELECT ename_first, enam- e_last, email_address FROM EMP WHERE public='Y'
CREATE TRIGGER is used to define automatic actions that execute in response to specific database events in Oracle.
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; /
CREATE PROCEDURE is used to define a reusable set of SQL and procedural logic stored in the Oracle database.
CREATE OR REPLACE PROCEDURE emp_data _sal(e_empid IN NUMBER, e_in- crease IN NUMBER) AS BEGIN UPDATE emp SET salary=salary*e_increase WHERE empid=e_empid; END; /
CREATE FUNCTION is used to define a reusable database object that returns a value based on input parameters in Oracle.
CREATE OR REPLACE FUNCTION find_value_in_table (e_value IN NUMBER, e_table IN VARCHAR2, e_column IN VARCHAR2) RETURN NUMBER IS v_found NUMBER; v_sql VARCHAR2(2000); BEGIN v_sql:='SELECT 1 FROM '||e_table||' WHERE '||e_column|| ' = '||e_value; execute immediate v_sql into v_found; return v_found; END; /
CREATE PROFILE is used to define resource limits and password policies for managing user sessions and security in Oracle.
CREATE PROFILE prod_profile LIMIT SESSIONS_PER_USER 2 CONNECT_TIME 10000 IDLE_TIME 10000 LOGICAL_READS_PER_SESSION 100000 PRIVATE_SGA 10m FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LIFE_TIME 180 PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 3 PASSWORD_LOCK_TIME 60 PASSWORD_GRACE_TIME 10;
CREATE ROLE is used to define a set of privileges that can be granted to users for simplified access management in Oracle.
CREATE ROLE dev_role IDENTIFIED USING dev123;
CREATE ROLLBACK SEGMENT is used to define storage for managing transaction rollback and read consistency in Oracle.
CREATE ROLLBACK SEGMENT S1 TABLESPACE RBS_TBS STORAGE (INITIAL 50m NEXT 100M MINEXTENTS 5 OPTIMAL 1000M);
CREATE SEQUENCE is used to generate unique numeric values automatically for use in database objects like primary keys in Ora- cle.
CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 MAXVALUE 1000000 CYCLE CACHE;
CREATE SPFILE is used to create a server parameter file that stores database configuration settings for Oracle instance startup.
CREATE SPFILE FROM PFILE; CREATE SPFILE=‘$ORACLE_HOME/dbs/spfilemybd.ora’ FROM PFILE=‘/$ORACLE_HOME/dbs/initmird- b.ora’;
CREATE SYNONYM is used to create an alias for database objects to simplify access and enhance security.
CREATE SYNONYM test_mir1.emp FOR test_mir1.EMP; CREATE PUBLIC SYNONYMmir1_emp FOR test_mir1.EMP;
CREATE TABLE is used to define and create a database table to store structured data efficiently.
CREATE TABLE mir_t1;
CREATE CLUSTER is used to define a storage structure that groups related tables to improve query performance.
CREATE CLUSTER pub_cluster (pubnum NUMBER) SIZE 8K PCTFREE 10 PCTUSED 60 TABLESPACE user_data; CREATE CLUSTER pub_cluster (pubnum NUMBER) SIZE 8K HASHKEYS 1000 PCT- FREE 10 PCTUSED 60 TABLESPACE user_data;
CREATE CONTROLFILE is used to define and recreate the control file, which manages database structure and recovery information.
CREATE CONTROLFILE REUSE DATABASE "mirdb" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32 MAXLOGMEMBERS 3
MAXDATAFILES 200 MAXINSTANCES 1
MAXLOGHISTORY 1000 LOGFILE
GROUP 1 (‘/u01/app/oracle/oradata/mirdb/mirdb_redo1a.redo’, '/u01/app/oracle/ oradata/mirdb/mirdb_redo1b.redo') SIZE 50m, GROUP 2 ('/u01/app/oracle/orada- ta/mirdb/mirdb_redo2a.redo', '/u01/app/oracle/oradata/mirdb/mirdb_redo2b.re- do') SIZE 50m
DATAFILE '/u01/app/oracle/oradata/mirdb/system_01.dbf ', '/u01/app/oracle/oradata/mirdb/users_01.dbf ', ‘/ u01/app/oracle/oradata/mirdb/undo_01.dbf ', '/u01/app/oracle/oradata/mirdb/sysaux_01.dbf ', '/u01/ app/oracle/oradata/mirdb/mirdb_01.dbf ‘;
CREATE DATABASE is used to initialize and create a new database instance with required files and configuration for data storage and management.
CREATE DATABASE mirdb MAXINSTANCES 1 MAXLOGHISTORY 1 MAXLOGFILES 5 MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/u01/app/oracle/oradata/mirdb/system01.dbf' SIZE 250M REUSE AU- TOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL DEFAULT
TEMPORARY TABLESPACE TEMP
TEMPFILE '/u01/app/oracle/oradata/mirdb/temp01.dbf'
SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED SYSAUX TABLESPACE
DATAFILE '/u01/app/oracle/oradata/mirdb/sysauxtbs01.dbf'
SIZE 300M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED UNDO TABLESPACE "UNDOTB- S1"
DATAFILE '/u01/app/oracle/oradata/mirdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED CHARACTER SET WE8M- SWIN1252
NATIONAL CHARACTER SET AL16UTF16 LOGFILE
GROUP 1 ('/u01/app/oracle/oradata/mirdb/redo01.log') SIZE 102400K, GROUP 2 ('/u01/ app/oracle/oradata/mirdb/redo02.log') SIZE 102400K, GROUP 3 ('/u01/app/oracle/orada- ta/mirdb/redo03.log') SIZE 100m;
CREATE DATABASE LINK is used to establish a connection to a remote database for seamless data access and distributed queries.
CREATE DATABASE LINK mirdb_db_link1 CONNECT TO current_user USING 'mirdb'; CREATE PUBLIC DATABASE LINK mirdb_db_link1; CONNECT TO remote_user IDENTIFIED BY hassandb USING ‘mirdb';
CREATE DIRECTORY is used to define a database object that maps to a file system path for secure file access and data operations.
CREATE OR REPLACE DIRECTORY mirdb AS ‘/u01/app/oracle/oradata/directories/mirdb’;
CREATE INDEX (Function-Based Index) is used to create an index on expressions or functions to improve query performance and optimize data retrieval.
CREATE INDEX msh_upper_last_name_emp ON emp_data (UPPER(last_name));
CREATE INDEX (Global Partitioned Index) statement in Oracle is used to create a partitioned index spanning all table partitions to optimize query performance and scalability.
CREATE INDEX inx_p_mir_tab1 ON mir_store_sales (invoice_number) GLOBAL PARTITION BY RANGE (invoice_number) (PARTITION part_01 VALUES LESS THAN (1000), PARTITION part_02 VALUES LESS THAN (10000), PARTITION part_03 VALUES LESS THAN (MAXVALUE));
CREATE INDEX inx_p_mir_tab2 ON mir_store_sales (store_id, time_id)
GLOBAL PARTITION BY RANGE (store_id, time_id) (PARTITION PART_01 VALUES LESS THAN
(1000, TO_DATE('01-01-2026','MM-DD-YYYY') )
TABLESPACE partition_tbl1
STORAGE (INITIAL 100M NEXT 200M PCTINCREASE 0), PARTITION part_02 VALUES LESS THAN
(1000, TO_DATE('01-01-20026','MM-DD-YYYY') ) TABLESPACE partition_tbl2
STORAGE (INITIAL 200M NEXT 400M PCTINCREASE 0),
PARTITION part_003 VALUES LESS THAN (maxvalue, maxvalue) TABLESPACE partition_03 );
CREATE INDEX (Local Partitioned Index) is used to create partitioned indexes aligned with table partitions to improve query per- formance and manageability.
CREATE INDEX inx_part_mir_tab1 ON mir_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, PARTI- TION tbs_part_04 TABLESPACE part_tbs_04); CREATE INDEX ix_part_mir_tab_01 ON mir_tab (col_one, col_two, col_three) LOCAL STORE IN (part_tb- s_01, part_tbs_02, part_tbs_03, part_tbs_04); CREATE INDEX inx_part_mir_tab_01 ON mir_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 Subpartitioned Index) is used to create indexes divided into subpartitions aligned with table subpartitions to enhance performance and manageability.
CREATE INDEX mir_sales_inx ON store_sales(time_id, store_id) STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED) LOCAL (PARTITION p1_2026, PARTITION p2_2026, PARTITION p3_2026 (SUBPARTITION pq3202601, SUBPARTITION pq3202602, SUBPARTITION pq3202603, SUB- PARTITION pq3202604, SUBPARTITION pq3202605), PARTITION p4_2026 (SUBPARTITION pq4202601 TABLESPACE tbs1, SUBPARTITION pq4202602 TABLESPACE tbs1, SUBPARTITION pq4202603 TABLESPACE tbs1, SUBPARTITION pq4202604 TABLESPACE tbs1, SUBPARTITION pq42002605 TABLESPACE tbs1), PARTITION sales_overflow (SUBPARTITION subp01 TABLESPACE tbs2, SUBPARTITION subp02 TABLESPACE tbs2));
CREATE INDEX (Nonpartitioned Index) is used to create a standard index on a table to improve query performance and speed up data retrieval.
CREATE INDEX inx_mirt_01 ON mirt(column_1); CREATE UNIQUE INDEX inx_mirt_01 ON mirt(column_1, column_2, column_3); CREATE INDEX inx_mirt_01 ON mirt(column_1, column_2, column_3) TABLESPACE my_indexes COMPRESS STORAGE (INITIAL 10K NEXT 10K PCTFREE 10) COMPUTE STATISTICS; CREATE BITMAP INDEX bit_mirt_01 ON mirt(col_two) TABLESPACE mir_tbs;
CREATE MATERIALIZED VIEW statement in Oracle is used to create a precomputed, stored query result to improve query perfor- mance and enable efficient data access.
CREATE MATERIALIZED VIEW emp_dept_mir1 TABLESPACE users BUILD IMMEDIATE REFRESH FAST ON COMMIT WITH ROWID ENABLE QUERY RE- WRITE 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;
CREATE MATERIALIZED VIEW (Partitioned Materialized View) statement in Oracle is used to create a partitioned, precomputed dataset to enhance query performance and scalability.
CREATE MATERIALIZED VIEW emp_mir_mv1 PARTITION BY RANGE (hire-
date) (PARTITION month1
VALUES LESS THAN (TO_DATE('01-MAR-2023', 'DD-MON-YYYY')) PCTFREE 0 PCTUSED 99 STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE users,(id NUMBER, current_value VARCHAR2(2000) ) COMPRESS;
CREATE TABLE parts (id NUMBER, version NUMBER, name VARCHAR2(30), Bin_code NUMBER, upc NUM- BER, 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_tbl STORAGE (INITIAL 1m NEXT 1m) ) TABLESPACE parts_tablespace PCTFREE 20 PCTUSED 60 STORAGE (INITIAL 10m NEXT 10m PCTINCREASE 0);
CREATE TABLESPACE (Permanent Tablespace) is used to create a storage area for persistent database objects, ensuring effi- cient data management and performance.
CREATE TABLESPACE mir_data_tbs1 DATAFILE ‘/u01/app/oracle/oradata/mirdb/mirdb_data_tbs01.dbf' SIZE 512m;
CREATE TABLESPACE mir_data_tbs2 DATAFILE '/u01/app/oracle/oradata/mirdb/mirdb_data_tbs01.dbf' SIZE 512m FORCE LOG- GING BLOCKSIZE 8k;
CREATE TABLESPACE mir_data_tbs3 DATAFILE '/u01/app/oracle/oradata/mirdb/mirdb_data_tbs01.dbf' SIZE 512m NOLOGGING DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE TABLESPACE mir_data_tbs4 DATAFILE '/u01/app/oracle/oradata/mirdb/mirdb_data_tbs01.dbf' SIZE 512m NOLOGGING DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE mir_data_tbs5 DATAFILE '/u01/app/oracle/oradata/mirdb/mirdb_data_tbs01.dbf' SIZE 5g;
CREATE TABLESPACE (Temporary Tablespace) is used to create a storage area for temporary data used during sorting and query processing operations.
CREATE TABLESPACE mir_temp_tbs TEMPFILE '/u01/app/oracle/oradata/mirdb/mirdb_temp_tbs01.tmp' SIZE 512m;
CREATE TABLESPACE (Undo Tablespace) is used to create a storage area for undo data to support transaction rollback and read consistency.
CREATE TABLESPACE mir_undo_tbs TEMPFILE '/u01/app/oracle/oradata/mirdb/mirdb_undo_tbs01.tmp' SIZE 512m RETENTION GUARANTEE;