PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

Oracle Database
Version 11.1
 
General
System Privileges ALTER DATABASE
To specify the RECOVER clause requires SYSDBA privileges
Database Properties set linesize 131
col property_name format a28
col property_value format a28
col description format a70

SELECT *
FROM database_properties
ORDER BY 1;
 
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 flash_recovery_area/ORABASE/ARCHIVELOG
$ cd flash_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/flash_recovery_area
db_recovery_file_dest_size=10737418240

###########################################
# Miscellaneous
###########################################
compatible=11.1.0.0.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 1000M 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 50M,
  GROUP 2 (
  '/app/oracle/product/oradata/orabase/redo02a.log',
  '/app/oracle/product/oradata/orabase/redo02b.log',
  '/app/oracle/product/oradata/orabase/redo02c.log') SIZE 50M,
  GROUP 3 (
  '/app/oracle/product/redo03a.log',
  '/app/oracle/product/redo03b.log',
  '/app/oracle/product/redo03c.log') SIZE 50M
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


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


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_inaddr 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

Typical Create 10g Database On Windows		 connect SYS/&&sysPassword as SYSDBA

spool c:\oracle\product\admin\orabase\create\CreateDB.log

startup nomount

pfile="c:\oracle\product\admin\orabase\pfile\init.ora";

CREATE DATABASE oragrid
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 12
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE 'c:\oragrid\system01.dbf'
SIZE 540M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'c:\oragrid\sysaux01.dbf'
SIZE 350M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'c:\oragrid emp01.dbf'
SIZE 25M REUSE
AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1"
DATAFILE 'c:\oragrid\undotbs01.dbf'
SIZE 100M REUSE
AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET US7ASCII
LOGFILE GROUP 1 ('c:\oragrid edo01.log') SIZE 5M REUSE,
        GROUP 2 ('c:\oragrid edo02.log') SIZE 5M REUSE,
        GROUP 3 ('c:\oragrid edo03.log') SIZE 5M REUSE
USER SYS IDENTIFIED BY "&&sysPassword"
USER SYSTEM IDENTIFIED BY "&&systemPassword";

spool off

Database Creation For UNIX Used By Older Versions of Oracle's E-Business Suite Applications		 spool $HOME/CreateDB.log

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

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

CREATE TABLESPACE RBS
DATAFILE '/u07/oradata/ctl1102A/rbs01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 5 MAXSIZE 2000m;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLESPACE GLX
DATAFILE '/u14/oradata/ctl1102A/glx01.dbf' SIZE 1M
aAUTOEXTEND ON NEXT 1 MAXSIZE 2000m;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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_domainco
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 edo5c.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;
 
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 NATIONAL CHARACTER SET INTERNAL_CONVERT AL16UTF16;
 
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\oradataxample.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 15M;

Make Datafile Not
Autoextensible		 ALTER DATABASE DATAFILE <file_name | file_number>
AUTOEXTEND <OFF | ON [NEXT <integer><M | G>
MAXSIZE <UNLIMITED | <integer><M | G>]>
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>
MAXSIZE <UNLIMITED | <integer><M | G>
SELECT file_name, autoextensible
FROM dba_data_files;

ALTER DATABASE DATAFILE 8 AUTOEXTEND ON NEXT 10M
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 DATABASE 8 OFFLINE;

SELECT file_name, status
FROM dba_data_files;

ALTER DATABASE 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 25M;
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 10M MAXSIZE 200M;
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 EXCLUSIVE RESTRICT;

DROP DATABASE;
 
Database Related Queries
Options Installed 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
Control Files
Data Files
DataGuard
Log Files
Tablespace
Tablespace Groups
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2025 psoug.org
-----