Oracle Database
Version 11.2.0.3

General Information
Database Properties set linesize 121
col property_name format a28
col property_value format a28
col description format a70

SELECT *
FROM database_properties
ORDER BY 1;
System Privileges ALTER DATABASE: Used to specify the RECOVER clause requires SYSDBA privileges
 
Create Database
Syntax 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>] -- default is NOARCHIVELOG
[FORCE LOGGING]
EXTENT MANAGEMENT LOCAL
DATAFILE <system_file_specification>
SYSAUX DATAFILE <file_specification>
DEFAULT TABLESPACE <tablespace_name>
 DATAFILE <file_specification>
EXTENT MANAGEMENT LOCAL
 UNIFORM SIZE <integer><M | G | T | P | E>
 <BIGFILE | SMALLFILE> DEFAULT TEMPORARY TABLESPACE
 <tablespace_name>
TEMPFILE <file_specification>
 EXTENT MANAGEMENT LOCAL
 UNIFORM SIZE SIZE <integer><M | G | T | P | E>
<BIGFILE | SMALLFILE> UNDO TABLESPACE <tablespace_name>
 DATAFILE <file_specification>
SET TIME_ZONE = <time_zone_region>;
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
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;
Rename 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 gv$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
'/u03/oracle/oradata/trackfile.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
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 'c:\oragrid\redo5c.log';
Stop Force Logging ALTER DATABASE NO FORCE LOGGING;
ALTER DATABASE NO FORCE LOGGING;
Start Archive Logging ALTER DATABASE ARCHIVELOG MANUAL;
ALTER DATABASE ARCHIVELOG MANUAL;
Stop Archive Logging ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE NOARCHIVELOG;
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>;
ALTER DATABASE NATIONALCHARACTER SET INTERNAL_CONVERT AL16UTF16;
 
Alter Database Hot Backup
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
Recovery clauses will be covered in the Backup-Recovery page when it is built
 
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
Guard: Prevent data in the database from being altered ALTER DATABASE GUARD <ALL | STANDBY | NONE>;
ALTER DATABASE GUARD ALL;
 
Alter Database Standby Database Clauses
Standby Database clauses will be covered in the DataGuard page when it is built
 
Alter Database Startup Clauses
Mount database but do not open ALTER DATABASE MOUNT [<STANDBY | CLONE> DATABASE];
ALTER DATABASE MOUNT;
Open Database Read Only ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE OPEN READ ONLY;
Open Database Read-Write ALTER DATABASE OPEN READ WRITE <RESETLOGS | NORESETLOGS> [<UPGRADE | DOWNGRADE>];
ALTER DATABASE OPEN READ WRITE RESETLOGS;
 
Alter Database Storage Clauses
Rename File ALTER DATABASE RENAME FILE <current_file_name> TO <new_file_name>;
ALTER DATABASE RENAME FILE 'c:\oracle\product\oradata\example.dbf' TO 'c:\oracle\product\oradata\demos.dbf';
Create Datafile ALTER DATABASE CREATE DATAFILE <file_name | file_number> AS <file_specification | NEW>;
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>;
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>
MAXSIZE <UNLIMITED | <integer><M | G | T>]>;
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>;
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> AUTOEXTEND ON NEXT <integer><M | G>
MAXSIZE <UNLIMITED | <integer><M | G>;
/* 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. */

SELECT file_name, status
FROM dba_data_files;

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

SELECT file_name, status
FROM dba_data_files;


ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/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;
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;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;
 
Alter Database Tempfile
Resize Tempfile ALTER DATABASE TEMPFILE <file_name | file_number> RESIZE TO <integer><M | G | T>;
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>
MAXSIZE <UNLIMITED | <integer><M | G>]>;
ALTER DATABASE TEMPFILE AUTOEXTEND ON NEXT 2G MAXSIZE 128G;
Drop Tempfile ALTER DATABASE TEMPFILE <file_name | file_number> DROP;
ALTER DATABASE TEMPFILE 1 DROP;
Place Tempfile Offline ALTER DATABASE TEMPFILE <file_name | file_number> OFFLINE;
ALTER DATABASE TEMPFILE 1 OFFLINE;
Place Tempfile Online ALTER DATABASE TEMPFILE <file_name | file_number> ONLINE;
ALTER DATABASE TEMPFILE 1 ONLINE;
 
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;
 
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;
Properties set pagesize 0
set linesize 121
col value$ format a40
col comment$ format a36

SELECT *
FROM props$;

Related Topics
Backup & Recovery
Block Change Tracking
Control Files
Data Files
Data Guard
Log Files
SecureFiles
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