Oracle Database
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose The "Database" commands are those that literally create, alter, and drop a database. Separate library pages, linked at page bottom, go into the specifics of entities created as part of database creation such as Control Files, Data Files, PDBs, Redo Logs, Tablespaces, Tablespace Groups, and Temp Files.
System Privileges
ALTER DATABASE AUDIT SYSTEM CREATE PLUGGABLE DATABASE
ALTER SYSTEM    
 
Create Database
Create 12c
Pluggable
Database
CREATE DATABASE <database_name>
USER SYS IDENTIFIED BY <password>
USER SYSTEM IDENTIFIED BY <password>
CONTROLFILE REUSE
MAXDATAFILES <integer>
MAXINSTANCES <integer>
CHARACTER SET <character_set_name>
NATIONAL CHARACTER SET <character_set_name>
SET DEFAULT <BIGFILE | SMALLFILE> TABLESPACE
LOGFILE
       [GROUP <integer> <file_specification>,]
       [GROUP <integer> <file_specification>,]
       [GROUP <integer> <file_specification>]
MAXLOGFILES <integer>
MAXLOGMEMBERS <integer>
MAXLOGHISTORY <integer>
<ARCHIVELOG | NOARCHIVELOG>
[FORCE] LOGGING
EXTENT MANAGEMENT LOCAL
DATAFILE <system_file_specification>
SYSAUX DATAFILE <file_specification>
DEFAULT TABLESPACE <tablespace_name>
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE <integer><M | G | T | P | E>
<BIGFILE | SMALLFILE>DEFAULT TEMPORARY TABLESPACE <tablespace_name>
TEMPFILE <file_specification> [SIZE <integer><M | G | T | P | E>]
AUTOEXTEND <OFF | ON> NEXT [SIZE <integer><M | G | T | P | E>]>] MAXSIZE [SIZE <integer><M | G | T | P | E>]>]
[<AUTOALLOCATE | UNIFORM [SIZE <integer><M | G | T | P | E>]>]
<BIGFILE | SMALLFILE> UNDO TABLESPACE <tablespace_name>
DATAFILE <file_specification> [SIZE <integer><M | G | T | P | E>]
AUTOEXTEND <OFF | ON> NEXT [SIZE <integer><M | G | T | P | E>]>] MAXSIZE [SIZE <integer><M | G | T | P | E>]>]
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SET TIME_ZONE = <time_zone_region>
ENABLE PLUGGABLE DATABASE SEED <seed_tablespace_clause>;
CREATE DATABASE oratest
USER SYS IDENTIFIED BY oracle1
USER SYSTEM IDENTIFIED BY oracle2
CONTROLFILE REUSE
MAXDATAFILES 1024
MAXINSTANCES 2
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
SET DEFAULT BIGFILE TABLESPACE
LOGFILE
  GROUP 1 ('/app/oracle/oradata/orabase/redo1a.log',
 '/app/oracle/fast_recovery_area/redo1b.log') SIZE 512M,
  GROUP 2 ('/app/oracle/oradata/orabase/redo2a.log',
 '/app/oracle/fast_recovery_area/redo2b.log') SIZE 512M,
  GROUP 3 ('', '')  SIZE 512M
('/app/oracle/oradata/orabase/redo2a.log',
 '/app/oracle/fast_recovery_area/redo2b.log')
SIZE 512M
MAXLOGFILES 24
MAXLOGMEMBERS 4
MAXLOGHISTORY 1
ARCHIVELOG
FORCE LOGGING
EXTENT MANAGEMENT LOCAL
DATAFILE '/app/oracle/oradata/orabase/cdbsystem.dbf' SIZE 700M
  AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILE '/app/oracle/oradata/orabase/cdbsysaux.dbf' SIZE 5G
AUTOEXTEND ON NEXT 1M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BIGFILE DEFAULT TEMPORARY TABLESPACE default_temp
  TEMPFILE '/app/oracle/oradata/orabase/deftemp.dbf' SIZE 250M
  AUTOEXTEND ON NEXT 64K MAXSIZE 1G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE AUTOALLOCATE
BIGFILE UNDO TABLESPACE undotbs1
  DATAFILE '/app/oracle/oradata/orabase/undotbs.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 256K MAXSIZE 5G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SET TIME_ZONE = ''+00:00'
ENABLE PLUGGABLE DATABASE SEED
FILE_NAME_CONVERT = ('/app/oracle/oradata/orabase/', '/app/oracle/oradata/orabase/pdbseed/')
SYSTEM DATAFILES SIZE 250M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 600M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

--  run postcreation scripts:
a. Set the session with a new parameter:
    alter session set "_oracle_script"=true;
b. Close and open the seed PDB:
    alter pluggable database pdb$seed close;
    alter pluggable database pdb$seed open;
c. Execute catalog.sql and other postcreation scripts:
    @?/rdbms/admin/catalog.sql
    @?/rdbms/admin/catblock.sql
    @?/rdbms/admin/catproc.sql
    @?/rdbms/admin/catoctk.sql
    @?/rdbms/admin/owminst.plb
    @?/sqlplus/admin/pupbld.sql

Note: To get the full list of scripts executed and the sequence of execution to follow, run DBCA to create CDB and in the last step, generate the scripts only. The scripts are created in the $ORACLE_BASE/admin/<cdb_name>/scripts directory. The shell script <cdb_name>.sh is the first script to read.
Typical Create 11g Database On Linux / UNIX $ cd /app/oracle/product
$ mkdir -p admin/orabase/adump
$ cd admin/orabase
$ mkdir dpdump
$ mkdir pfile
$ mkdir wallet

$ cd /app/oracle/product
$ mkdir -p fast_recovery_area/ORABASE/ARCHIVELOG
$ cd fast_recovery_area/ORABASE
$ mkdir FLASHBACK
$ mkdir ONLINELOG

$ cd /app/oracle/product
$ mkdir -p oradata/orabase

$ touch /app/oracle/product/admin/orabase/pfile/initorabase.ora
$ cd /app/oracle/product/admin/orabase/pfile
$ vi initorabase.ora

------------------------------------------------------
###########################################
# Cache and I/O
###########################################
db_block_size=8192

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=""
db_name=orabase

###########################################
# File Configuration
###########################################
control_files=(
"/app/oracle/product/oradata/orabase/control01.ctl", "/app/oracle/product/oradata/orabase/control02.ctl", "/app/oracle/product/oradata/orabase/control03.ctl")
db_recovery_file_dest=/app/oracle/product/fast_recovery_area
db_recovery_file_dest_size=10737418240

###########################################
# Miscellaneous
###########################################
compatible=11.2.0.1.0
diagnostic_dest=/app/oracle/product
memory_target=854589440

###########################################
# Security and Auditing
###########################################
audit_file_dest=/app/oracle/product/admin/orabase/adump
audit_trail=DB
remote_login_passwordfile=EXCLUSIVE

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orabaseXDB)"

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
------------------------------------------------------
-- save the file and exit vi

$ cp initorabase.ora $ORACLE_HOME/dbs/initorabase.ora

$ cd $HOME

$ sqlplus / as sysdba

SQL> spool $HOME/CreateDB.log

SQL> create spfile from pfile;

SQL> startup nomount

SQL> define _editor=vi

SQL> commit;

SQL> ed

-- paste the following into the editor, save, and exit
CREATE DATABASE orabase
MAXINSTANCES 8
MAXLOGHISTORY 292
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/app/oracle/product/oradata/orabase/system01.dbf'
SIZE 750M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/app/oracle/product/oradata/orabase/sysaux01.dbf'
SIZE 1G REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '/app/oracle/product/oradata/orabase/temp01.dbf'
SIZE 125M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1"
DATAFILE '/app/oracle/product/oradata/orabase/undotbs01.dbf'
SIZE 500M REUSE
AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
  GROUP 1 (
  '/home/oracle/redo01a.log',
  '/home/oracle/redo01b.log',
  '/home/oracle/redo01c.log') SIZE 100M,
  GROUP 2 (
  '/app/oracle/product/oradata/orabase/redo02a.log',
  '/app/oracle/product/oradata/orabase/redo02b.log',
  '/app/oracle/product/oradata/orabase/redo02c.log') SIZE 100M,
  GROUP 3 (
  '/app/oracle/product/redo03a.log',
  '/app/oracle/product/redo03b.log',
  '/app/oracle/product/redo03c.log') SIZE 100M
SET TIME_ZONE = '+08:00'
USER sys IDENTIFIED BY "&&sysPassword"
USER system IDENTIFIED BY "&&systemPassword"

-- during CREATE DATABASE the following scripts are run. Do not run them a second time.
$ORACLE_HOME/rdbms/admin/dcore.bsq
$ORACLE_HOME/rdbms/admin/dsqlddl.bsq
$ORACLE_HOME/rdbms/admin/dmanage.bsq
$ORACLE_HOME/rdbms/admin/dplsql.bsq
$ORACLE_HOME/rdbms/admin/dtxnspc.bsq
$ORACLE_HOME/rdbms/admin/dfmap.bsq
$ORACLE_HOME/rdbms/admin/denv.bsq
$ORACLE_HOME/rdbms/admin/drac.bsq
$ORACLE_HOME/rdbms/admin/dsec.bsq
$ORACLE_HOME/rdbms/admin/doptim.bsq
$ORACLE_HOME/rdbms/admin/dobj.bsq
$ORACLE_HOME/rdbms/admin/djava.bsq
$ORACLE_HOME/rdbms/admin/dpart.bsq
$ORACLE_HOME/rdbms/admin/drep.bsq
$ORACLE_HOME/rdbms/admin/daw.bsq
$ORACLE_HOME/rdbms/admin/dsummgt.bsq
$ORACLE_HOME/rdbms/admin/dtools.bsq
$ORACLE_HOME/rdbms/admin/dexttab.bsq
$ORACLE_HOME/rdbms/admin/ddm.bsq
$ORACLE_HOME/rdbms/admin/dlmnr.bsq
$ORACLE_HOME/rdbms/admin/daw.bsq

-- verify things look good so far
SQL> desc obj$
SQL> desc tab$

-- continue by creating the data dictionary views
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/catactx.sql
SQL> @?/rdbms/admin/catadvtb.sql

-- verify the following
SQL> @?/rdbms/admin/catptabs.sql
SQL> @?/rdbms/admin/catprc.sql
SQL> @?/rdbms/admin/catexp.sql
SQL> @?/rdbms/admin/catdbsyn.sql
SQL> @?/rdbms/admin/cataudit.sql
SQL> @?/rdbms/admin/catodm.sql
SQL> @?/rdbms/admin/catost.sql
SQL> @?/rdbms/admin/catadv.sql
SQL> @?/rdbms/admin/catsnap.sql
SQL> @?/rdbms/admin/catmgrsv.sql
SQL> @?/rdbms/admin/catxdbv.sql
SQL> @?/rdbms/admin/cdcore.sql
SQL> @?/rdbms/admin/cdpart.sql
SQL> @?/rdbms/admin/dbmsrman.sql

SQL> @?/rdbms/admin/c1101000.sql  -- patching?

GRANT select ON gv_$reserved_words TO PUBLIC;
REVOKE execute ON dbms_lob FROM public;
REVOKE execute ON utl_file FROM public;
REVOKE execute ON utl_http FROM public;
REVOKE execute ON utl_inaddr FROM public;
REVOKE execute ON utl_mail FROM public;
REVOKE execute ON utl_smtp FROM public;
-- you will want to add many more to this list

SELECT table_name
FROM all_tab_privs_made
WHERE privilege = 'EXECUTE'
AND grantee = 'PUBLIC'
AND (table_name LIKE 'DBMS%' OR table_name LIKE 'UTL%')
ORDER BY 1;

spool off
Database Creation For UNIX Used By Older Versions of Oracle's E-Business Suite Applications.

An excellent example of how not to do it in the current millennium.
spool $HOME/CreateDB.log

CREATE DATABASE ctl1102A
maxdatafiles 1022
maxlogmembers 4
character set "WE8ISO8859P1"
DATAFILE '/u03/oradata/ctl1102A/system01.dbf' SIZE 1G
AUTOEXTEND ON
NEXT 25M
MAXSIZE 1G
LOGFILE
 GROUP 1 ('/u05/oradata/redo01a.log','/u06/oradata/redo01b.log') SIZE 500M,
 GROUP 2 ('/u05/oradata/redo02a.log','/u06/oradata/redo02b.log') SIZE 500M,
 GROUP 3 ('/u05/oradata/redo03a.log','/u06/oradata/redo03b.log') SIZE 500M;

CREATE TABLESPACE USERS
DATAFILE '/u03/oradata/ctl1102A/users01.dbf' SIZE 5M
AUTOEXTEND ON NEXT 5 MAXSIZE 2G;

CREATE TABLESPACE RBS
DATAFILE '/u07/oradata/ctl1102A/rbs01.dbf' SIZE 1G
AUTOEXTEND ON NEXT 5 MAXSIZE 2G;

CREATE TABLESPACE TOOLS
DATAFILE '/u03/oradata/ctl1102A/tools01.dbf' SIZE 50M
AUTOEXTEND ON NEXT 5 MAXSIZE 2G;

CREATE TABLESPACE CTXD
DATAFILE '/u13/oradata/ctl1102A/ctxd01.dbf' SIZE 50M
AUTOEXTEND ON NEXT 5 MAXSIZE 2G;

CREATE TABLESPACE TEMP
DATAFILE '/u04/oradata/ctl1102A/temp01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 5 MAXSIZE 2G;

CREATE TABLESPACE AKD
DATAFILE '/u13/oradata/ctl1102A/akd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE AKX
DATAFILE '/u14/oradata/ctl1102A/akx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ALRD
DATAFILE '/u16/oradata/ctl1102A/alrd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ALRX
DATAFILE '/u15/oradata/ctl1102A/alrx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE APD
DATAFILE '/u13/oradata/ctl1102A/apd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE APX
DATAFILE '/u14/oradata/ctl1102A/apx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ARD
DATAFILE '/u16/oradata/ctl1102A/ard01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ARX
DATAFILE '/u15/oradata/ctl1102A/arx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ASD
DATAFILE '/u13/oradata/ctl1102A/asd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ASX
DATAFILE '/u14/oradata/ctl1102A/asx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE AXD
DATAFILE '/u16/oradata/ctl1102A/axd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE AXX
DATAFILE '/u15/oradata/ctl1102A/axx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE AZD
DATAFILE '/u13/oradata/ctl1102A/azd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE AZX
DATAFILE '/u14/oradata/ctl1102A/azx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE BOMD
DATAFILE '/u16/oradata/ctl1102A/bomd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE BOMX
DATAFILE '/u15/oradata/ctl1102A/bomx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CED
DATAFILE '/u13/oradata/ctl1102A/ced01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CEX
DATAFILE '/u14/oradata/ctl1102A/cex01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CHVD
DATAFILE '/u16/oradata/ctl1102A/chvd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CHVX
DATAFILE '/u15/oradata/ctl1102A/chvx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CND
DATAFILE '/u13/oradata/ctl1102A/cnd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CNX
DATAFILE '/u14/oradata/ctl1102A/cnx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CRPD
DATAFILE '/u16/oradata/ctl1102A/crpd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CRPX
DATAFILE '/u15/oradata/ctl1102A/crpx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CSD
DATAFILE '/u13/oradata/ctl1102A/csd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CSX
DATAFILE '/u14/oradata/ctl1102A/csx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CZD
DATAFILE '/u13/oradata/ctl1102A/czd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE CZX
DATAFILE '/u14/oradata/ctl1102A/czx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ECD
DATAFILE '/u16/oradata/ctl1102A/ecd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ECX
DATAFILE '/u15/oradata/ctl1102A/ecx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ENGD
DATAFILE '/u13/oradata/ctl1102A/engd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ENGX
DATAFILE '/u14/oradata/ctl1102A/engx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE FAD
DATAFILE '/u16/oradata/ctl1102A/fad01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE FAX
DATAFILE '/u15/oradata/ctl1102A/fax01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE FLMD
DATAFILE '/u13/oradata/ctl1102A/flmd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE FLMX
DATAFILE '/u14/oradata/ctl1102A/flmx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE FNDD
DATAFILE '/u16/oradata/ctl1102A/fndd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE FNDX
DATAFILE '/u15/oradata/ctl1102A/fndx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE GLD
DATAFILE '/u13/oradata/ctl1102A/gld01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE GLX
DATAFILE '/u14/oradata/ctl1102A/glx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE HRD
DATAFILE '/u13/oradata/ctl1102A/hrd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE HRX
DATAFILE '/u14/oradata/ctl1102A/hrx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE HXTD
DATAFILE '/u16/oradata/ctl1102A/hxtd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE HXTX
DATAFILE '/u15/oradata/ctl1102A/hxtx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ICXD
DATAFILE '/u13/oradata/ctl1102A/icxd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE ICXX
DATAFILE '/u14/oradata/ctl1102A/icxx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE INVD
DATAFILE '/u16/oradata/ctl1102A/invd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE INVX
DATAFILE '/u15/oradata/ctl1102A/invx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE JAD
DATAFILE '/u13/oradata/ctl1102A/jad01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE JAX
DATAFILE '/u14/oradata/ctl1102A/jax01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE JED
DATAFILE '/u16/oradata/ctl1102A/jed01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE JEX
DATAFILE '/u15/oradata/ctl1102A/jex01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE JGD
DATAFILE '/u13/oradata/ctl1102A/jgd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE JGX
DATAFILE '/u14/oradata/ctl1102A/jgx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE JLD
DATAFILE '/u16/oradata/ctl1102A/jld01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE JLX
DATAFILE '/u15/oradata/ctl1102A/jlx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE MFGD
DATAFILE '/u13/oradata/ctl1102A/mfgd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE MFGX
DATAFILE '/u14/oradata/ctl1102A/mfgx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE MRPD
DATAFILE '/u13/oradata/ctl1102A/mrpd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE MRPX
DATAFILE '/u14/oradata/ctl1102A/mrpx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE MSCD
DATAFILE '/u13/oradata/ctl1102A/mscd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE MSCX
DATAFILE '/u14/oradata/ctl1102A/mscx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE OED
DATAFILE '/u16/oradata/ctl1102A/oed01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE OEX
DATAFILE '/u15/oradata/ctl1102A/oex01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE OSMD
DATAFILE '/u16/oradata/ctl1102A/osmd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE OSMX
DATAFILE '/u15/oradata/ctl1102A/osmx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE OTAD
DATAFILE '/u13/oradata/ctl1102A/otad01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE OTAX
DATAFILE '/u14/oradata/ctl1102A/otax01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE PAD
DATAFILE '/u16/oradata/ctl1102A/pad01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE PAX
DATAFILE '/u15/oradata/ctl1102A/pax01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE PJMD
DATAFILE '/u16/oradata/ctl1102A/pjmd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE PJMX
DATAFILE '/u15/oradata/ctl1102A/pjmx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE POD
DATAFILE '/u13/oradata/ctl1102A/pod01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE POX
DATAFILE '/u14/oradata/ctl1102A/pox01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE QAD
DATAFILE '/u16/oradata/ctl1102A/qad01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE QAX
DATAFILE '/u15/oradata/ctl1102A/qax01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE RGD
DATAFILE '/u13/oradata/ctl1102A/rgd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE RGX
DATAFILE '/u14/oradata/ctl1102A/rgx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE RLAD
DATAFILE '/u16/oradata/ctl1102A/rlad01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE RLAX
DATAFILE '/u15/oradata/ctl1102A/rlax01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE SSPD
DATAFILE '/u13/oradata/ctl1102A/sspd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE SSPX
DATAFILE '/u14/oradata/ctl1102A/sspx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE VEHD
DATAFILE '/u16/oradata/ctl1102A/vehd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE VEHX
DATAFILE '/u15/oradata/ctl1102A/vehx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE WIPD
DATAFILE '/u13/oradata/ctl1102A/wipd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE WIPX
DATAFILE '/u14/oradata/ctl1102A/wipx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE WHD
DATAFILE '/u16/oradata/ctl1102A/whd01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

CREATE TABLESPACE WHX
DATAFILE '/u15/oradata/ctl1102A/whx01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1 MAXSIZE 2G;

spool off
 
Alter Database Control File Clauses
Control File Management See the Control Files page in the library: Link below
 
Alter Database Default Settings Clauses
Set Default Tablespace Type ALTER DATABASE SET DEFAULT <BIGFILE | SMALLFILE> TABLESPACE;
ALTER DATABASE SET DEFAULT smallfile TABLESPACE;
Set Default Tablespace ALTER DATABASE SET DEFAULT TABLESPACE <tablespace_name>;
ALTER DATABASE SET DEFAULT TABLESPACE uwdata;
Set Default Temporary Tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <group_name>;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE app_grp;
Set Default Temporary Tablespace Group ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <tablespace_group_name>;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_grp;
Change Global Name ALTER DATABASE RENAME GLOBAL_NAME TO <new_name>;
-- verify global name
SELECT value$
FROM props$
WHERE name = 'GLOBAL_DB_NAME';

-- get db_domain
set linesize 121
col name format a40
col value format a40

SELECT name, value
FROM v$parameter
WHERE name = 'db_domain';

-- backup controlfile
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

-- change the trace file CREATE CONTROLFILE command to
CREATE CONTROLFILE REUSE SET DATABASE "NEW_SID_NAME" RESETLOGS;

SHUTDOWN IMMEDIATE;

-- modify the db_name parameter in the initSID.ora
conn / as sysdba

CREATE spfile FROM pfile='initSID.ora';

STARTUP NOMOUNT

-- execute the create controlfile command

-- recover database USING BACKUP CONTROLFILE until cancel


CANCEL

-- open resetlogs the database and
ALTER DATABASE RENAME GLOBAL_NAME TO new_sid_name;
Disable Block Change Tracking ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Enable Block Change Tracking ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '<file_name>' REUSE;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE
' '/app/oracle/fast_recovery_area/bct.log' REUSE;
Flashback Mode ALTER DATABASE FLASHBACK <ON | OFF>;
ALTER DATABASE FLASHBACK ON;
Set Time Zone By Delta ALTER DATABASE SET TIME_ZONE <+ | -> HH:MI;
ALTER DATABASE SET TIME_ZONE '-5:0';
Set Time Zone By Name ALTER DATABASE SET TIME_ZONE <time_zone_region>;
ALTER DATABASE SET TIME_ZONE 'US/Eastern';
 
Alter Database Log File Clauses
Section Note See the Log Files page in the library: Link below
Clear A Log File ALTER DATABASE CLEAR [UNARCHIVED] LOGFILE <logfile_path_and_name> [UNRECOVERABLE DATAFILE];
ALTER DATABASE CLEAR LOGFILE '/app/oracle/oradata/orabase/redo5c.log';
Stop Force Logging ALTER DATABASE NO FORCE LOGGING;
SELECT force_logging FROM v$database;

ALTER DATABASE NO FORCE LOGGING;

SELECT force_logging FROM v$database;
Start Archive Logging ALTER DATABASE ARCHIVELOG MANUAL;
SELECT log_mode FROM v$database;

ALTER DATABASE ARCHIVELOG MANUAL;

SELECT log_mode FROM v$database;
Stop Archive Logging ALTER DATABASE NOARCHIVELOG;
SELECT log_mode FROM v$database;

ALTER DATABASE NOARCHIVELOG;

SELECT log_mode FROM v$database;
Supplemental DB Logging ALTER DATABASE <ADD | DROP>
SUPPLEMENTAL LOG DATA [(ALL | PRIMARY KEY | UNIQUE | FOREIGN KEY) COLUMNS];
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
Register log files ALTER DATABASE REGISTER [OR REPLACE] <PHYSICAL | LOGICAL> LOGFILE <file_specification>;
ALTER DATABASE REGISTER LOGICAL LOGFILE '/app/oracle/fast_recovery_area/orabase/archivelog/arch_398_1_705869229.arc';
 
Alter Database Character Set Clauses
Set the character set ALTER DATABASE CHARACTER SET INTERNAL_CONVERT <CHARACTER SET>;
ALTER DATABASE CHARACTER SET INTERNAL_CONVERT WE8MSWIN1252;
Set the national character set ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_CONVERT <CHARACTER SET>;
SELECT value
FROM v$nls_valid_values
WHERE parameter = 'CHARACTERSET'
ORDER BY 1;

ALTER DATABASE NATIONALCHARACTER SET INTERNAL_CONVERT AL16UTF16;
 
Alter Database Hot Backup
Section Note I've put this in here for historical, perhaps hysterical, reasons. If you use these commands you are several decades past the mandatory retirement age.
Put the database into hot backup mode ALTER DATABASE BEGIN BACKUP;
ALTER DATABASE BEGIN BACKUP;
Take the database out of hot backup mode ALTER DATABASE END BACKUP;
ALTER DATABASE  END BACKUP;
ALTER SYSTEM ARCHIVE LOG CURRENT;
 
Alter Database Recovery Clauses
Section Note I've put this in here for historical, perhaps hysterical, reasons too. If you use these commands you are several decades past the mandatory retirement age.
 
Alter Database Redo Thread Clauses (RAC)
Disable RAC Thread ALTER DATABASE DISABLE THREAD <integer>;
ALTER DATABASE DISABLE THREAD 7;
Enable RAC Thread ALTER DATABASE ENABLE PUBLIC THREAD <integer>;
ALTER DATABASE ENABLE PUBLIC THREAD 5;
 
Alter Database Security Clause
Prevent data in the database from being altered ALTER DATABASE GUARD <ALL | STANDBY | NONE>;
ALTER DATABASE GUARD ALL;
 
Alter Database Standby Database Clauses
Section Note Standby Database clauses are covered on the DataGuard page linked at page bottom
 
Alter Database Startup Clauses
Mount CDB container but do not open ALTER DATABASE MOUNT [<STANDBY | CLONE> DATABASE];
conn / as sysdba

ALTER DATABASE MOUNT;
Open Database Read Only ALTER DATABASE OPEN READ ONLY;
conn / as sysdba

ALTER DATABASE OPEN READ ONLY;
Open Database Read-Write ALTER DATABASE OPEN READ WRITE <RESETLOGS | NORESETLOGS> [<UPGRADE | DOWNGRADE>];
conn / as sysdba

ALTER DATABASE OPEN READ WRITE RESETLOGS;
 
Alter Database Storage Clauses
Rename File ALTER DATABASE RENAME FILE <current_file_name> TO <new_file_name>;
conn sys@pdborcl as sysdba

ALTER DATABASE RENAME FILE '/app/oracle/oradata/orabase/pdborcl/example.dbf' TO '/app/oracle/oradata/orabase/pdborcl/demos01.dbf';
Create Datafile ALTER DATABASE CREATE DATAFILE <file_name | file_number> AS <file_specification | NEW>;
conn sys@pdbtest as sysdba

col file_name format a50

SELECT file_name, file_id
FROM dba_data_files;

ALTER DATABASE CREATE DATAFILE 8 AS NEW;

SELECT file_name, file_id
FROM dba_data_files;
 
Alter Database Datafile
Resize Datafile ALTER DATABASE DATAFILE <file_name | file_number> RESIZE TO <integer><M | G | T | P>;
ALTER DATABASE DATAFILE 8 RESIZE 15G;
Make Datafile Not Autoextensible ALTER DATABASE DATAFILE <file_name | file_number>
AUTOEXTEND <OFF | ON [NEXT <integer><M | G | T | P>
MAXSIZE <UNLIMITED | <integer><M | G | T | P>]>;
SELECT file_name, autoextensible
FROM dba_data_files;

ALTER DATABASE DATAFILE 8 AUTOEXTEND OFF;

SELECT file_name, autoextensible
FROM dba_data_files;
Make datafile autoextensible ALTER DATABASE DATAFILE <file_name | file_number> AUTOEXTEND ON NEXT <integer><M | G | T>
MAXSIZE <UNLIMITED | <integer><M | G | T | P>;
conn sys@pdbtest as sysdba

SELECT file_name, autoextensible
FROM dba_data_files;

ALTER DATABASE DATAFILE 8 AUTOEXTEND ON NEXT 10G MAXSIZE 100M;

SELECT file_name, autoextensible
FROM dba_data_files;
Take a datafile offline or online: by name ALTER DATABASE DATAFILE <file_name | file_number> <OFFLINE | ONLINE>;
/* To use this form of the ALTER DATABASE statement, the database must be in ARCHIVELOG mode. This requirement prevents you from accidentally losing the datafile, since taking the datafile offline while in NOARCHIVELOG mode is likely to result in losing the file. */

conn sys@pdbtest as sysdba

SELECT file_name, status
FROM dba_data_files;

ALTER DATABASE DATAFILE '/u02/oracle/oradata/orabase/pdbtest/stuff01.dbf' OFFLINE;

SELECT file_name, status
FROM dba_data_files;


ALTER DATABASE DATAFILE '/u02/oracle/oradata/orabase/pdbtest/stuff01.dbf' ONLINE;

SELECT file_name, status
FROM dba_data_files;
Take a datafile offline or online: by number ALTER DATABASE <file_name |file_number> OFFLINE;
conn sys@pdbtest as sysdba

col file_name format a50

SELECT file_name, status
FROM dba_data_files;

ALTER DATAFILE 8 OFFLINE;

SELECT file_name, status
FROM dba_data_files;

ALTER DATAFILE 8 ONLINE;

SELECT file_name, status
FROM dba_data_files;
Offline and drop datafile
-- NOARCHIVELOG mode only
ALTER DATABASE <file_name |file_number> OFFLINE DROP;
conn sys@pdbtest as sysdba

ALTER DATABASE DATAFILE '/u02/oracle/oradata/orabase/pdbtest/users03.dbf' OFFLINE DROP;
 
Alter Database Tempfile
Resize Tempfile ALTER DATABASE TEMPFILE <file_name | file_number> RESIZE TO <integer><M | G | T>;
col file_name format a45
col tablespace_name format a20

SELECT file_name, file_id, tablespace_name, (bytes/1024/1024) SIZE_IN_MB
FROM dba_temp_files;

ALTER DATABASE TEMPFILE 1 RESIZE 25G;
Change Tempfile Autoextend Specification ALTER DATABASE TEMPFILE <file_name | file_number>
AUTOEXTEND <OFF | ON [NEXT <integer><M | G | T>
MAXSIZE <UNLIMITED | <integer><M | G | T>]>;
SELECT file_name, autoextensible
FROM dba_temp_files;

ALTER DATABASE TEMPFILE '/app/oracle/oradata/orabase/temp01.dbf' AUTOEXTEND ON NEXT 2G MAXSIZE 128G;
Drop Tempfile ALTER DATABASE TEMPFILE <file_name | file_number> DROP;
SELECT file_id, file_name, tablespace_name
FROM dba_temp_files;

ALTER DATABASE TEMPFILE 1 DROP;
Place Tempfile Offline ALTER DATABASE TEMPFILE <file_name | file_number> OFFLINE;
SELECT file_name, file_id, status
FROM dba_temp_files;

ALTER DATABASE TEMPFILE 1 OFFLINE;

SELECT file_name, file_id, status
FROM dba_temp_files;
Place Tempfile Online ALTER DATABASE TEMPFILE <file_name | file_number> ONLINE;
SELECT file_name, file_id, status
FROM dba_temp_files;

ALTER DATABASE TEMPFILE 1 ONLINE;

SELECT file_name, file_id, status
FROM dba_temp_files;
 
Drop Database
Do not try this one for testing as it does exactly what it says DROP DATABASE;
SHUTDOWN ABORT;

STARTUP MOUNT RESTRICT;

DROP DATABASE;
 
Related Queries
Installed Options col parameter format a40
col value format a20

SELECT * FROM gv$option;

col comp_name format a30

SELECT comp_name, version, status
FROM dba_registry;
Database Properties from a data dictionary view set linesize 141
col property_name format a28
col property_value format a32
col description format a53

SELECT *
FROM database_properties
ORDER BY 1;
Database Properties  from a data dictionary table set linesize 141
col value$ format a35
col comment$ format a53

SELECT *
FROM props$
ORDER BY 1;

Related Topics
Backup & Recovery
Block Change Tracking
Containers
Control Files
Data Files
Data Guard
Flashback Database
Log Files
SecureFiles
Seed Database
Tablespace
Tablespace Groups

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved