ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
###########################################
# 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
-- 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
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';
VALUE$
---------
ORABASEXX
SELECT *
FROM global_name;
GLOBAL_NAME
-----------
ORABASEXX
-- 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 '/u01/orabase19/oradata/ORABASEXIX/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 '/u01/orabase19e/fast_recovery_area/orabasexix/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 decades past the mandatory retirement age.
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. If you use these commands you are 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@pdbdev as sysdba
ALTER DATABASE RENAME FILE '/app/oracle/oradata/orabase/pdbdev/example.dbf' TO '/app/oracle/oradata/orabase/pdbdev/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/Online
ALTER DATABASE TEMPFILE <file_name | file_number> OFFLINE;
SELECT file_name, file_id, status
FROM dba_temp_files;
FILE_NAME FILE_ID STATUS
----------------------------------------------- ------- -----------
C:\U01\ORABASE19\ORADATA\ORABASEXIX\TEMP01.DBF 1 ONLINE
ALTER DATABASE TEMPFILE 1 OFFLINE;
Database altered.
SELECT file_name, file_id, status
FROM dba_temp_files;
FILE_NAME FILE_ID STATUS
----------------------------------------------- ------- -----------
C:\U01\ORABASE19\ORADATA\ORABASEXIX\TEMP01.DBF 1 OFFLINE
ALTER DATABASE TEMPFILE 1 ONLINE;
Database altered.
SELECT file_name, file_id, status
FROM dba_temp_files;
FILE_NAME FILE_ID STATUS
----------------------------------------------- ------- -----------
C:\U01\ORABASE19\ORADATA\ORABASEXIX\TEMP01.DBF 1 ONLINE
Drop Database
Do not try this one for testing as it does exactly what it says unless you really want to drop the database removing all files
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 a35
col version format a11
col status format a11
SELECT comp_name, version, status
FROM dba_registry
ORDER BY 1;
set linesize 141
col property_name format a28
col property_value format a32
col description format a25
SELECT *
FROM database_properties
ORDER BY 1;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
---------------------------- -------------------------------- -------------------------
CON_VSN 4 version number for the
CDB or the Application
Container
DBTIMEZONE 00:00 DB time zone
DEFAULT_EDITION ORA$BASE Name of the database
default edition
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent
tablespace
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary
tablespace
DICT.BASE 2 dictionary base tables
version #
DICTIONARY_ENDIAN_TYPE LITTLE Endian type of the data
dictionary
DST_PRIMARY_TT_VERSION 32 Version of primary
timezone data file
DST_SECONDARY_TT_VERSION 0 Version of secondary time
zone data file
DST_UPGRADE_STATE NONE State of Day Light Saving
Time Upgrade
EXPORT_VIEWS_VERSION 8 Export views revision #
Flashback Timestamp TimeZone GMT Flashback timestamp
created in GMT
GLOBAL_DB_NAME ORABASEX Global database name
LOCAL_UNDO_ENABLED TRUE true if local undo is
enabled
MAX_PDB_SNAPSHOTS 8 maximum number of
snapshots for a given PDB
MAX_STRING_SIZE STANDARD MAX_STRING_SIZE parameter
used for dictionary
metadata
NLS_CALENDAR GREGORIAN Calendar system
NLS_CHARACTERSET AL32UTF8 Character set
NLS_COMP BINARY NLS comparison
NLS_CURRENCY $ Local currency
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_LANGUAGE AMERICAN Language
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_RDBMS_VERSION 19.0.0.0.0 RDBMS version for NLS
parameters
NLS_SORT BINARY Linguistic definition
NLS_TERRITORY AMERICA Territory
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone
format
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NO_USERID_VERIFIER_SALT E4FA6F27C073C7D5C89D3E2FBCA3D0C7 Per database random key
for computing faux salt
OLS_OID_STATUS 0 OLS OID Status used for
Label Security
TDE_MASTER_KEY_ID
WORKLOAD_CAPTURE_MODE CAPTURE implies workload
capture is in progress
WORKLOAD_REPLAY_MODE PREPARE implies external
replay clients can
connect; REPLAY implies
workload replay is in
progress
Database Properties from a data dictionary table
set linesize 141
col value$ format a35
col comment$ format a53