| 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 |
| 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-2013 Daniel A. Morgan All Rights Reserved | |||||||||
|
||||||||||
