Oracle Sql Command Syntexes

Oracle SQL Commands and Functions • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • •

alter database alter index alter session alter system alter table alter tablespace alter user analyze table ascii asciistr audit avg bitand bfilename case when If some sort of if then else logic must be implemented in SQL cast chr ceil comment commit To store changes made within a transaction concat connect_by For hierarchical queries. convert tablespace consecutive range count create context create index create materialized view create profile create role create sequence create synonym create table create tablespace create trigger create type create user create view cv cursor date literal dbtimezone

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • •

decode delete dups diff tables distinct drop drop type existsNode() explain plan extract extractValue() exp flashback table flat hierarchy floor from_tz get most actual row grant greatest group by group by rollup grouping grouping_id hierarchy insert all inline view update initcap instr least length like lnnvl log lower ltrim lpad merge mintrans maxtrans multiset new_time nanvl nls_charset_id nlssort nls_initcap nls_upper nls_lower nvl nvl2 order by

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • •

outer join q'X ... X' (Oracle's quote operator) renaming replace revoke rollback rpad round rtrim same data for 8 mins savepoint set constraints set transaction select sign soundex substr sum adjacent sys connect by path sys_context sys_refcursor sys_xmlagg table() to_char to_nchar to_date to_number to_single_byte translate trim trunc truncate tz_offset updateXML upper userenv vsize with XMLElement XMLForest XMLSequence

Mathematical • • • •

acos asin cos cosh

• • • • •

ln power sin sinh sqrt

Date related • • • •

add_months last_day months_between next_day

Analytical Functions Analytical Functions

Select and subquery Select and Subquery

Shorties SQL Shorties

Emulating a table with sql cast and table

Aggregate functions Aggregate functions allow to group "equal rows" in a result set and to do some (mostly mathematical) operations on a column of this group.

User defined aggregate functions in 9i User defined Aggregate functions in 9i

NULLs and boolean operators NULLs and boolean operators

insert into x returning y Insert into x returning y

hints Hints

Model Clause Model clause is an extension in 10g.

Regular Expressions Regular expressions are definitely going to be very cool.

Validating SQL 92 This SQL validator can obviousy be used to check if an SQL statement is valid SQL-92 according to the standard. I haven't checked it out yet, though.

Misc Pseudo columns Reserved words in Oracle SQL.

alter database in Oracle Whenever the database is altered, the control file should be backed up.

alter database datafile This is the way how the characteristics of datafiles can be changed.

alter database datafile 'DF' end backup This command can be used for a hot backup Note:There is no alter database datafile 'DF' begin backup command.

alter database datafile offline drop This command is not meant to drop a datafile. It is meant to offline the datafile with the intention of dropping the tablespace.

alter database archivelog alter database archivelog alter database archivelog manual alter database noarchivelog Alter database archivelog is simply an instruction to oracle not to overwrite an online redo log that has not yet been archived. Either you archive those manually or have arch do it. See alter system archive log start and log_archive_start. See also archive vs noarchive log mode.

alter database backup controlfile to ['filename' | trace] This command comes in two versions. One backs up the control file in a binary format while the other backs it up in a human readable form. It is required if the database is running in archive log mode and a structural change was made to the database. Backing it up in a binary format:

alter database backup controlfile to '/some/arbitrary/path'; alter database backup controlfile to '/some/arbitrary/path' reuse; Backing it up in a human readable format:

alter database backup controlfile to trace; alter database backup controlfile to trace as '/some/arbitrary/path'; alter database backup controlfile to trace as '/some/arbitrary/path' reuse; If the human readable form is chosen, the file can be made usable if the comments at the beginning are removed and replaced with a connect / as sysdba. If the init.ora file is not at its default location, it has to be appended with a pfile=.... in the line containing a startup.

alter database flashback on | off alter database flashback on; alter database flashback off;

alter database [no] force logging If a database is in force logging mode, all changes, except those in temporary tablespaces, will be logged, independently from any nologging specification. It is also possible to put arbitrary tablespaces into force logging mode: alter tablespace force logging. A force logging might take a while to complete because it waits for all unlogged direct I/O operations to finish.

alter database open .. yet to be finished ..

alter database open resetlogs Online redo logs are re-created . The log sequence is reset to 1. If the databaseis running in archive log mode, the archived redo logs should then be deleted. Otherwise, chances are, that Oracle will eventually try to create an archived redo log whose filename already exists.

alter database open read only

Standby database The following alter database commands are used for a standby database environment.

alter database ACTIVATE [phisical|locigal] standby database [skip [standby logfile]] Changes a database to an active database. This is a preprationlal step for the database to become a primary database. The database must be mounted. physical and logical specify what kind of database is going to be activated.

alter database SET STANDBY DATABASE TO MAXIMIZE [protection| availablity | performance] alter database SET STANDBY DATABASE TO MAXIMIZE protection; alter database SET STANDBY DATABASE TO MAXIMIZE availablity; alter database SET STANDBY DATABASE TO MAXIMIZE performance;

alter database REGISTER [or replace] [physical | logical] logfile [ redo_log_file_spec [, ...]] alter database MOUNT STANDBY database alter database CREATE STANDBY CONTROLFILE alter database create standby controlfile as '/some/path/to/a/file'; See creating physical standby databases: create control file

commit switchover clause Yet to be do

alter database START LOGICAL STANDBY APPLY [ new primary dblink | initial [scn_value]] alter database [STOP | ABORT] logical standby apply

Datafiles [Oracle] List of datafiles in a database select name from v$datafile

Changing the characteristics alter database datafile ...

Setting the size The following statements sets the size of the datafile foo.dbf to 1 gigabyte.

alter database datafile '/db/adpdb/foo.dbf' resize 1024M

Setting the maximum size It makes only sense to set a maximum size for a datafile if autoextend is on. Hence, the statement to set the maximum size requires to specify autoextend on.

alter database datafile '/u01/db/df/ts_data01.dbf' autoextend on maxsize 20M

Autoextend Datafile Headers Find Information about datafile headers in v$datafile_header.

Datafile count limits The maximum number of database files can be set with the init parameter db_files. Regardless of the setting of this paramter, the maximum number of database files in a smallfile tablespace is 1022. A bigfile tablespace can contain only one database file.

Adding datafiles Adding a datafile is a structural change to the database that requires a backup. A datafile is added with a alter tablespace add datafile command.

Standby environment See adding datafiles to primary servers if you want to add a datafile to a primary server in a standby environment.

Renaming datafiles Renaming a datafile is a structural change to the database that requires a backup.

Dropping datafiles Datafiles were not designed to be dropped. (See also Metalink note 111316.1) This is true even though there is alter database datafile offline drop. However, if the datafile is the only datafile in its tablespace, it can be removed together with the tablespace:

DROP TABLESPACE ts_data INCLUDING CONTENTS; With Oracle 10g Release 2, it's finally possible to drop a datafile as long as • it's not the only datafile within the tablespace, and • the tablespace is only and in read-write status :

alter tablespace ts_something drop datafile '/path/to/datafile.dbf'

Changing a datafile's content Whenever an SQL statement makes any changes to a datafile, those changes are recorded in the redo log.

Online and offline datafiles A datafile can either be online or offline. A database cannot be opened if any of the online datafiles media recovery.

Fuzzy datafiles A datafile that contains a block whose SCN is more recent than the SCN of its header is called a fuzzy datafile.

Related links The dba_data_files displays all datafiles along with their respective relevant information. ASM simplifies the optimal layout of datafiles. Moving/renaming datafiles.

Move/rename datafiles in Oracle Moving datafiles of a database: The datafiles reside under /home/oracle/OraHome1/databases/ora9 and have go to /home/oracle/databases/ora9.

SQL> select tablespace_name, substr(file_name,1,70) from dba_data_files; TABLESPACE_NAME SUBSTR(FILE_NAME,1,70) --------------------------------------------------------------------------------------------------SYSTEM /home/oracle/OraHome1/databases/ora9/system.dbf UNDO /home/oracle/OraHome1/databases/ora9/undo.dbf DATA /home/oracle/OraHome1/databases/ora9/data.dbf SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------/home/oracle/OraHome1/databases/ora9/redo1.ora /home/oracle/OraHome1/databases/ora9/redo2.ora /home/oracle/OraHome1/databases/ora9/redo3.ora SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------/home/oracle/OraHome1/databases/ora9/ctl_1.ora /home/oracle/OraHome1/databases/ora9/ctl_2.ora /home/oracle/OraHome1/databases/ora9/ctl_3.ora Now, as the files to be moved are known, the database can be shut down:

SQL> shutdown The files can be copied to their destination:

$ cp /home/oracle/OraHome1/databases/ora9/system.dbf /home/oracle/databases/ora9/system.dbf $ cp /home/oracle/OraHome1/databases/ora9/undo.dbf /home/oracle/databases/ora9/undo.dbf $ cp /home/oracle/OraHome1/databases/ora9/data.dbf /home/oracle/databases/ora9/data.dbf $ $ cp /home/oracle/OraHome1/databases/ora9/redo1.ora /home/oracle/databases/ora9/redo1.ora $ cp /home/oracle/OraHome1/databases/ora9/redo2.ora /home/oracle/databases/ora9/redo2.ora $ cp /home/oracle/OraHome1/databases/ora9/redo3.ora /home/oracle/databases/ora9/redo3.ora $ $ cp /home/oracle/OraHome1/databases/ora9/ctl_1.ora /home/oracle/databases/ora9/ctl_1.ora $ cp /home/oracle/OraHome1/databases/ora9/ctl_2.ora /home/oracle/databases/ora9/ctl_2.ora $ cp /home/oracle/OraHome1/databases/ora9/ctl_3.ora /home/oracle/databases/ora9/ctl_3.ora The init.ora file is also copied because it references the control files. I name the copied file just init.ora because it is not in a standard place anymore and it will have to be named explicitely anyway when the database is started up.

$ cp /home/oracle/OraHome1/dbs/initORA9.ora /home/oracle/databases/ora9/init.ora The new location for the control files must be written into the (copied) init.ora file: /home/oracle/databases/ora9/init.ora

control_files = (/home/oracle/databases/ora9/ctl_1.ora, /home/oracle/databases/ora9/ctl_2.ora, /home/oracle/databases/ora9/ctl_3.ora)

$ sqlplus "/ as sysdba" SQL> startup exclusive mount pfile=/home/oracle/databases/ora9/init.ora SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/system.dbf' to '/home/oracle/databases/ora9/system.dbf'; SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/undo.dbf' to '/home/oracle/databases/ora9/undo.dbf'; SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/data.dbf' to '/home/oracle/databases/ora9/data.dbf'; SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo1.ora' to '/home/oracle/databases/ora9/redo1.ora'; SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo2.ora' to '/home/oracle/databases/ora9/redo2.ora'; SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo3.ora' to '/home/oracle/databases/ora9/redo3.ora'; SQL> shutdown SQL> startup pfile=/home/oracle/databases/ora9/init.ora

To properly move the datafile around or rename the datafile, follow this guide: 1. 2. 3. 4. 5. 6.

Login to SQLPlus. Connect as SYS DBA with CONNECT / AS SYSDBA command. Shutdown the database instance with SHUTDOWN command. Rename or/and move the datafiles at operating system level. Start Oracle database in mount state with STARTUP MOUNT command. Modify the name or location of datafiles in Oracle data dictionary using following command syntax:

ALTER DATABASE RENAME FILE ‘’ TO ‘’; 7. Open Oracle database instance completely with ALTER DATABASE OPEN command.

If the datafiles that need to be changed or moved do not belong to SYSTEM tablespaces, and do not contain active rollback segments or temporary segments, there is another workaround that does not require database instance to be shutdown. Instead, only the particular tablespace that contains the date files is taken offline. 1. 2. 3. 4.

Login to SQLPlus. Connect as SYS DBA with CONNECT / AS SYSDBA command. Make offline the affected tablespace with ALTER TABLESPACE OFFLINE; command. Modify the name or location of datafiles in Oracle data dictionary using following command syntax:

ALTER TABLESPACE RENAME DATAFILE ‘’ TO ‘’; 5. Bring the tablespace online again with ALTER TABLESPACE alter tablespace ONLINE; command.

Alter index in Oracle Alter index ... split partition This is one of the few statements that can make use of the nologging option.

Alter index ... rebuild This is one of the few statements that can make use of the nologging option.

Alter index ... rebuild partition This is one of the few statements that can make use of the nologging option.

alter index ... unusable .... Yet to be finished .... See also using the +append hint with insert statements for a possible use to set an index to unusable.

alter index ... [no]monitoring usage An index can be monitored to verify if it is used within the period between

SQL>alter index index_name monitoring usage; -- and SQL>alter index index_name nomonitoring usage; The result is stored in v$object_usage. See also On verifying if an index is used.

alter session in Oracle Yet to be finished...

alter session set current_schema conn rene/rene create table some_table ( data varchar2(30) ); insert into some_table values ('This table belongs to Rene'); grant select on some_table to bi; conn bi/pw_bi create table some_table ( data varchar2(30) ); insert into some_table values ('This table belongs to bi'); select * from some_table; DATA -----------------------------This table belongs to bi With altering current_schema, the default schema to find objects is changed: alter session set current_schema=rene; select * from some_table; DATA -----------------------------This table belongs to Rene

alter session set events This statement sets a diagnostic event. The following snippet dumps an Index Tree

column object_id new_value ObjectId select object_id from sys.dba_objects where owner = upper('&Owner') and object_name = upper('&IndexName') / prompt prompt alter session set events 'immediate trace name treedump level &ObjectId' set feedback on alter session set events 'immediate trace name treedump level &ObjectId'; List of diagnostic events. where is the result dumped?

nls_date_format The following statement sets the way how a date is represented as a string.

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Caveat: A usual error is to confuse months (being represented by mm) and minutes (being represented by mi).

nls_territory See to_char.

alter session set optimizer_mode optimizer_goal seems to be an alias for optimizer_mode (??) As of 9i, the mode of the optimizer can be set to ALL_ROWS, FIRST_ROWS, RULE, FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000.

alter session set optimizer_mode=<mode>

alter session set plsql_debug This instructs the compiler to generated debug information for the remainder of the session. It does not recompile any existing PL/SQL. See also debugging pl/sql

alter session set skip_unusable_indexes true | false Yet to be finished... See also the +append hint in insert statements.

alter session set sql_trace alter session set sql_trace=true Setting sql_trace=true is a prerequisite for using tk prof.

alter session set timed_statistics Setting timed_statistics=true might be usefule when using tk prof.

alter session set tracefile_identifier=... The default name for a trace files is PID_ora_TRACEID. The value of tracefile_identifier specifies the traceid for the trace file. After setting the trace file identifier, it can be queried through the traceid column in v$process

Alter system in Oracle Alter system archive log [start|stop|all|...] alter system archive log all; alter system archive log next; alter system archive log sequence 104; alter system archive log current; alter system archive log current noswitch; The following command can be used to stop arch.

alter system archive log stop Similarly, arch is started with

alter system archive log start However, changing the archiver this way doesn't last when the database is restarted. When the database is started, it consults log_archive_start in the initialization file to determine if arch is started.

Alter system archive log all This command is used to manually archive redo logs.

alter system disconnect session alter system kill session alter system kill session 'session-id,session-serial' This command kills a session. The session-id and session-serial parameters are found in the v$session view (columns sid and serial#.

alter system checkpoint Performs a checkpoint

alter system checkpoint

alter system dump datafile This command can be used in order to dump one ore more blocks of a datafile. The following command dumps blocks 50 through 55 of file 5. Which file 5 is can be found out with v$datafile

alter system dump datafile 5 block min 50 block max 55; Note: trace files are only readable by the Oracle account. If you want to change this, set the undocumented initialization parameter _trace_files_public to true. Doing so will, however, cause a big security risk.

alter system flush buffer_cache alter system flush buffer_cache; This command is not available prior to 10g. It flushes the buffer cache in the SGA.

9i had an undocumented command to flush the buffer cache:

alter session set events = 'immediate trace name flush_cache';

alter system flush shared_pool alter system flush shared_pool; This command flushed the shared pool.

alter system quiesce restricted alter system suspend|resume alter system switch logfile Causes a redo log switch.

alter system switch logfile; If the database is in archive log mode, but the ARCH process hasn't been startedm, the command might hang, because it waits for the archiving of the 'next' online redo log.

alter system register Forces the registration of database information with the listener.

alter system register

Alter system set timed_statistics Setting timed_statistics=true might be usefule when using tk prof.

Alter system set sql_trace Setting sql_trace=true is a prerequisite when using tk prof.

Alter system set .... deferred Alter system can be used to change initialization parameters on system level. However, some parameters, when changed with alter system don't affect sessions that are already opened at the time when the statement is executet; it only affects sessions started later. These parameters must be changed with alter system set DEFERRED, otherwise a ORA-02096: specified initialization parameter is not modifiable with this option error is returned. These parameters can be identified as they have a DEFERRED in the isses_modifiable column of v$parameter.

Alter system reset <parameter_name> Resets a parameter.

alter system reset some_param scope=both sid='*';

scope scope=memory Changes the parameter's value for the running instance only. As soon as the instance is stopped and started, this change will be lost.

scope=spfile Alters an initialization parameter in the spfile scope=both Alters an initialization parameter in the spfile as well as in the running instance.

alter table Adding Foreign key ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (col1, col2) REFERENCES table_2 (cola,colb);

Adding a unique constraint The following example adds a unique constraint on the column column_name in the table whose name is table_name. The name of the constraint is constraint_name.

alter table table_name add constraint constraint_name unique (column_name)

Disabling constraints Integrity constraints can be disabled with the alter table command.

alter table table-name disable constraint-specification; alter table table-name disable constraint constraint-name;

Adding a new Column alter table foo_table add bar_column char(1);

Modifying a column Renaming a column name alter table some_table rename column column_name to new_column_name;

Changing a column's type A column's type can be changed if the column is empty within the entire table, otherwise Oracle gives an ORA01440: column to be modified must be empty to decrease precision or scale.

alter table some_table modify (column_name number);

Changing null to not null or vice versa alter table some_table modify (column_name not null); alter table some_table modify col_not_null number null; The parenthesis after the modify are optional.

alter table ... move alter table table_name move tablespace new_tablespacename;

Alter table ... move partition This is one of the few statements that can make use of the nologging option.

Alter table .. rename to ... alter table old_table_name rename to new_table_name; This is equivalent to rename old_table_name to new_table_name.

Alter table ... split partition This is one of the few statements that can make use of the nologging option.

Specifying tablespace for index alter table tbl add constraint pk_tbl primary key (col_1, col_2) using index tablespace ts_idx

Removing a constraint alter table table_name drop constraint constraint_name;

alter tablespace in oracle alter tablespace TS add datafile alter tablespace ts_sth add datafile 'c:\xx\sth_04.dbf' size 4M autoextend off; Adding a datafile to a database is a structural change that requires a backup.

alter tablespace TS begin backup This command can be used for a hot backup

alter tablespace TS end backup This command can be used for a hot backup

alter tablespace flashback on | off alter tablespace TS force logging If a tablespace in in force logging mode, redo is generated, even if an operation uses nologging. The entire database can be put into force logging mode using alter database force logging.

Alter user in Oracle SQL Alter user <user> quota alter user <user_name> quota unlimited on ; alter user <user_name> quota 1M on ; Sets the quota for a user on a tablespace.

Alter user <user> identified by NEW_PASSWORD Sets a new password for a user. Alternatively, the SQL*Plus command password can be used.

Alter user <user> connect through <proxy_user> See this link.

Alter user <user> profile This command allows to define a profile for a user:

alter user <user_name> profile change_password_profile

The ARCHIVE LOG command in SQL*PLUS archive archive archive archive archive archive

log log log log log log

list stop start next all n

From within SQL*PLUS, an archive log {opt} command can be started that can be used to start or stop archiving online redo log files. Further, it can be used to display inofrmation about redo log files.

archive log list This command displays: • If the database is in archive log or not • If automatic archival is enabled • The current log file group's sequence number • The current archive destination (as per LOG_ARCHIVE_DEST) • Next sequence to be archived • Oldest online log sequence

archive log start [to destination] Starts automatic archiving of redo logs (actually starts the arch process). If destination is specified, it uses it a archiving destination. Otherwise log_archive_dest_n is used to find out the destination.

audit [Oracle SQL] audit audit audit audit

sql statement; all; system privilege; all privileges;

audit by session; audit by access; audit whenever successful; audit whenever not successful; audit by session whenever successful; audit by access whenever not successful;

By session This is the default for most audit statements. If an audit is specified by session, at most one record will be inserted in the audit trail per audited action for a session

By access If an audit is specified by access, each execution of an audited action will be recorded in the audit trail.

SQL Statement

Init parameters Where (and if) the audited data is stored is controlled by the audit_trail. This parameter can be of one of the following values: • none • false deprecated? • os • db • db,extended since Oracle 10g? • true deprecated? • xml since Oracle 10g • xml,extended since Oracle 10g No auditing is made if set to none or false. Audit data goes to the operating system's audit trail if set to os. Audit data is written into sys.aud$. A convenient way to read sys.aud$ is by using the dba_audit_trail view.

Links See also auditing with Oracle. Undo audit with noaudit.

Convert tablespace in Oracle This is a feature that is available with Oracle 10g.

RMAN> convert tablespace ts_data, ts_idx to platform 'Microsoft Windows NT' format '/temp/%U';

create context [Oracle] create context context-namespace using package-name; create context context-namespace using package-name initialized externally; create context context-namespace using package-name initialized globally; create context context-namespace using package-name accessed globally; create context creates a context namespace. Such a context namespace is a container, if you will, for attribute value pairs. The value of an attribute can the be queried with sys_context('context-namespace', 'attributename'). A value for an attribute is set using dbms_session.set_context('context-namespace', 'attribute-value', value). For security reasons, Oracle only allows to execute dbms_session.set_context from within the package package-name

Simple examples «normal» context conn / as sysdba create user rene identified by rene default tablespace users temporary tablespace temp; grant create session, create procedure, create any context to rene; conn rene/rene create context some_context using some_package; create package some_package as procedure set_value_in_context(some_value in varchar2); end some_package; / create package body some_package as procedure set_value_in_context(some_value in varchar2) is begin dbms_session.set_context('some_context', 'some_attribute', some_value); end set_value_in_context; end some_package; / exec some_package.set_value_in_context('foo'); select sys_context('some_context', 'some_attribute') from dual;

SYS_CONTEXT('SOME_CONTEXT','SOME_ATTRIBUTE') ---------------------------------------------------------foo A «normal» context is not valid accross sessions:

conn rene/rene; select sys_context('some_context', 'some_attribute') from dual; This time, sys_context returns null since I reconnected.

accessed globally conn rene/rene create context global_context using global_package accessed globally; create package global_package as procedure set_value_in_context(global_value in varchar2); end global_package; / create package body global_package as procedure set_value_in_context(global_value in varchar2) is begin dbms_session.set_context('global_context', 'global_attribute', global_value); end set_value_in_context; end global_package; / exec global_package.set_value_in_context('valid accross sessions'); I've set the context in a session and will connect again (thus starting a new session):

conn rene/rene select sys_context('global_context', 'global_attribute') from dual; SYS_CONTEXT('GLOBAL_CONTEXT','GLOBAL_ATTRIBUTE') ------------------------------------------------------------------------------valid accross sessions The context is accessed globally, so the value of the context/attribute tuple is visible across sessions.

Misc Contexts live in the SYS schema. This has two implications: • A context cannot be created like create context some_schema.some_context • The required system privilege is create ANY context. There is no create context system privilege.

Creating an Oracle 10g database from the command line only This article is the successor to Creating an Oracle 9i database on NT from the command line only. There are basically three ways to create an Oracle database: • Using the Database Configuration Assistant (DBCA) • With the SQL create database statement • Through upgrading an existing database. This article focuses on the second option. It can be completed on the command line only, that is, without any GUI tool. Also, the article is a bit biased towards Windows and its command prompt (cmd.exe: start->run->cmd).

Specifying the Instance's SID There can be more than one Oracle instance on a single machine. In order to be able to distinguish these instances, Oracle uses a SID (System Identifier) which is a string. The SID can be set through the ORACLE_SID environment variable.

D:\oracle\product\10.1.0>set ORACLE_SID=ORA10

Creating an Oracle Service On Windows, each instance requires a Windows service. This service must first be created with oradim:

D:\oracle\product\10.1.0\Db_1>oradim -new -sid %ORACLE_SID% -intpwd MYSECRETPASSWORD -startmode M Instance created. It can be verified that a Windows service was created by typing services.msc into the console. A service named OracleServiceORA10 (ORA10 = %ORACLE_SID%) will be found. Also, the startup type is manual as was requested by -startmode M. Oracle also created a password file under %ORACLE_HOME%\database:

D:\oracle\product\10.1.0\Db_1>dir database Volume in drive D has no label. Volume Serial Number is C4E9-469A Directory of D:\oracle\product\10.1.0\Db_1\database 03/05/2005 03/05/2005 03/05/2005 03/05/2005 03/05/2005

03:54 03:54 11:16 11:13 03:54

PM PM AM AM PM



. .. archive 31,744 oradba.exe 2,560 PWDORA10.ORA

As can be seen, the SID is in the password file's name.

Creating the initialization parameter file When an Oracle instance starts up, it requires either an initialization paramter file (init.ora) or an SPFILE. SPFILES have binary content and must be created from init.ora files. Therefore, the init.ora file (which is an ordianary text file) is created first. Here's a minimal init.ora (under $ORACLE_HOME/dbs if it is Unix, or %ORACLE_HOME%\database, if it is windows) just to demonstrate how the control files are found. Of course, you will add more init params into the init.ora file. D:\oracle\product\10.1.0\Db_1\database\initORA10.ora

control_files = (d:\oracle\databases\ora10\control01.ora, d:\oracle\databases\ora10\control02.ora, d:\oracle\databases\ora10\control03.ora) undo_management = auto db_name = ora10 db_block_size = 8192 The undo_management parameter is necessary if we want to use automatic undo management. Although the above seems to be the bare required minimum, you probably also want do define background_dump_dest, core_dump_dest and user_dump_dest.

Starting the instance Now, that we have created an Oracle service and the init.ora file, we're ready to start the instance:

D:\oracle\product\10.1.0\Db_1>sqlplus /nolog SQL*Plus: Release 10.1.0.2.0 - Production on Sat Mar 5 16:05:15 2005 Copyright (c) 1982, 2004, Oracle.

All rights reserved.

SQL> connect sys/MYSECRETPASSWORD as sysdba Connected to an idle instance. SQL*Plus tells us that we're connected to an idle instance. That means that it is not yet started. So, let's start the instance. We have to start the instance without mounting (nomount) as there is no database we could mount at the moment.

SQL> startup nomount ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers

113246208 787708 61864708 50331648 262144

bytes bytes bytes bytes bytes

This created the SGA (System Global Area) and the background processes.

Creating the database We're now ready to finally create the database:

SQL>create database ora10 logfile group 1 ('D:\oracle\databases\ora10\redo1.log') size 10M, group 2 ('D:\oracle\databases\ora10\redo2.log') size 10M, group 3 ('D:\oracle\databases\ora10\redo3.log') size 10M character set WE8ISO8859P1 national character set utf8 datafile 'D:\oracle\databases\ora10\system.dbf' size 50M autoextend on next 10M maxsize unlimited extent management local sysaux datafile 'D:\oracle\databases\ora10\sysaux.dbf' size 10M autoextend on next 10M maxsize unlimited undo tablespace undo datafile 'D:\oracle\databases\ora10\undo.dbf' size 10M default temporary tablespace temp tempfile 'D:\oracle\databases\ora10\temp.dbf' size 10M; If something goes wrong with the creation, Oracle will write an error into the alert.log. The alert log is normaly found in the directory that is specified with the background_dump_dest. If this parameter was not specified (as is the case in our minimal init.ora), the alert.log will be written into %ORACLE_HOME%/RDMBS/trace. If an ORA-01031: insufficient privileges is returned, that means most likely, that the current user is not in the dba group (on unix), or the ORA_DBA (windows). If the init.ora file is not at its default location or has not been found with the pfile attribute, an ORA-01078: failure in processing system parameters and an LRM-00109: could not open parameter file '/appl/oracle/product/9.2.0.2/dbs/initadpdb.ora' error is issued. The create database command also executes a file whose name is determined by the (hidden) init parameter _init_sql_file (which seems to default to sql.bsq) After the creation of the database, it can be mounted and opened for use.

Completing the DB creation In order to complete the db creation, the following scripts must be run as sys: • %ORACLE_HOME%/rdbms/admin/catalog.sql

• %ORACLE_HOME%/rdbms/admin/catproc.sql and SQL*Plus provides a shortcut to refer to the ORACLE_HOME directory: the question mark (?). Therefore, these scripts can be called like so: SQL> @?/rdbms/admin/catalog.sql SQL> @?/rdbms/admin/catproc.sql catalog.sql creates the data dictionary. catproc.sql creates all structures required for PL/SQL. catalog.sql calls, for example, catexp.sql which is a requirement for exp, or dbmsstdx.sql which is a requirement to create triggers. The user system might also want to run ?/sqlplus/admin/pupbld.sql. pupbld.sql creates a table that allows to block someone from using sql plus.

SQL> connect system/manager SQL> @?/sqlplus/admin/pupbld Of course, tablespaces, users, tables and so on must be created according to the use of the database.

Setting up database to using java Also call @?/javavm/install/initjvm if you want to enable the JServer option(?).

Oracle managed files Refer also to DB_CREATE_ONLINE_LOG_DEST_n and DB_CREATE_FILE_DEST for Oracle-managed files.

Errors while creating database If there is an error while the database is created, such as a ORA-01092: ORACLE instance terminated. Disconnection forced, the alert log should be consulted. This file most probably contains a more desriptive error message. If the error occurs at a very early stage, there won't be an alert.log. In this case, the error will most probably be found in a trace file in udump directory.

Create Index in Oracle create index is one of the few statements that can make use of the nologging option. create index requires a temporary segment if the statement cannot be completed in memory.

Function Based Indexes Function based indexes require query_rewrite_enabled be set to true and query_rewrite_integrity be set to trusted. Thanks to Clark Lu who reported an error on this page.

Create materialized view set feedback off drop table t_; drop materialized view t_mat_; create table t_ ( a number, b varchar2(10) ); begin for i in 1 .. 1000 loop insert into t_ values ( mod(i,trunc(dbms_random.value(1,i))), dbms_random.string('a',10)); end loop; end; / commit; begin dbms_stats.gather_table_stats( user,'T_', method_opt=>'FOR ALL COLUMNS'); end; / create materialized view t_mat_ build immediate refresh on commit enable query rewrite as select count(*),a from t_ group by(a); begin dbms_stats.gather_table_stats( user,'T_MAT_', method_opt=>'FOR ALL COLUMNS'); end; / alter session set query_rewrite_enabled=false; explain plan for select count(*),a from t_ group by(a); select * from table(dbms_xplan.display);

alter session set query_rewrite_enabled=true; alter session set query_rewrite_integrity=enforced; explain plan for select count(*),a from t_ group by(a); select * from table(dbms_xplan.display);

Oracle SQL: Create Profile create profile profile-name limit kernel-limit n; create profile profile-name limit kernel-limit unlimited; create profile profile-name limit kernel-limit default; create profile profile-name limit password-limit expression; create profile profile-name limit password-limit unlimited; create profile profile-name limit password-limit default; create profile profile-name limit password_verify_function function-name; create profile profile-name limit password_verify_function null; create profile profile-name limit password_verify_function default; create profile profile-name limit kernel-limit-1 xyz, kernel-limit-2 abc kernel-limit-3 qrp password-limit-1 foo password-limit-2 bar password-limit-3 baz; Note: In order to enforce kernel limits, resource_limit must be set to true.

create profile appl_profile limit sessions_per_user 2 cpu_per_session 10000 cpu_per_call 1 connect_time unlimited idle_time 30 logical_reads_per_session default logical_reads_per_call default -- composite_limit default private_sga 20M failed_login_attempts 3 password_life_time 30 password_reuse_time 12 password_reuse_max unlimited password_lock_time default password_grace_time 2 password_verify_function null;

----------------

hunderth of seconds hunderth of seconds minutes minutes db blocks db blocks

days days days

create role in Oracle create create create create create create

role role role role role role

role-name; role-name identified by password; role-name identified using package-name; role-name identified externally; role-name identified globally; role-name not identified;

Create sequence create create create create create create create create create create create create create

sequence sequence sequence sequence sequence sequence sequence sequence sequence sequence sequence sequence sequence

sequence-name; sequence-name increment by n; sequence-name start with n; sequence-name max value n; sequence-name nomaxvalue; sequence-name min value n; sequence-name nominvalue; sequence-name cycle; sequence-name nocycle; sequence-name cache n; sequence-name nocache; sequence-name order; sequence-name noorder;

A sequence is a highly scalable, non-blocking ID generator. Sequences can be used, for example, to generate primary keys.

create sequence seq_foo_bar start with 1000 increment by 20;

create synonym [Oracle SQL] create synonym synonym-name for object; create public synonym synonym-name for object; The object for which a synonym is created does not necessarily

create table [Oracle SQL] Prerequisites For a user to be able to create a table, he needs the create table system privilege, otherwise he'll receive the ORA-01031: insufficient privileges error message. Additionally, the user needs to have enough quota on the tablespace where he wants to create the table.

Heap tables Usually, if we refer to tables, we mean heap tables, although there are other types as well. A heap table is created like this:

create table t ( a number, b varchar2(10) ) It is possible to create the constraints together with the create statement. As a foreign key references a known type, it is not necessary to specify the foreign key's column type.

create table order_id order_dt cust_id )

orders ( number primary key date, references customers

A primary key needs to have an associated (unique) index. It is possible to specify on what tablespace this index is going to be created:

create table orders ( order_id number, order_dt date, cust_id references customer constraint pk_orders (order_id) using index tablespace ts_idx )

Index organized tables (IOT) create table iot_ ( a number, b varchar2(10), constraint pk_iot_ primary key (a, b) ) organization index;

Global temporary tables The following example shows the difference for redo generated when using global temporary tables and "ordinary" heap tables. It uses the redo_diff package.

create global temporary table gtt_ ( x_ varchar2(100) ) on commit delete rows; create table t_ ( x_ varchar2(100) ); exec redo_diff.diff_it; declare

i number; begin for i in 1 .. 1000 loop insert into gtt_ values(dbms_random.string('a',99)); end loop; end; / exec redo_diff.diff_it; declare i number; begin for i in 1 .. 1000 loop insert into t_ values(dbms_random.string('a',99)); end loop; end; / exec redo_diff.diff_it;

Organization external The following create table statement creates an external table.

create table (....) organization external ( type oracle_loader default directory some_dir access parameters ( records delimited by newline fields terminated by ',' missing field are values null ) location ('some_file.csv') ) reject limit unlimited;

Nested tables create or replace type item as object ( item_id Number ( 6 ), descr varchar2(30 ), quant Number ( 4,2) ); / create or replace type items as table of item; / create table bag_with_items ( bag_id number(7) primary key, bag_name varchar2(30) not null, the_items_in_the_bag items ) nested table the_items_in_the_bag store as bag_items_nt; Adding a unique constraint for item_id:

alter table bag_items_nt add constraint uq_item_id unique(item_id);

of XMLType create table table_name of xmltype;

create table table_name of xmltype XMLSchema "some_url" element "root-element"; create table table_name (columns..., xml_doc) XMLType xmltype column xml_doc element "root-element"; Creates a table for XML DB.

Create table ... as select This is one of the few statements that can make use of the nologging option. In fact, if the database is running noarchive log, the create table .. as select statement is nologging..

Mintrans and Maxtrans See mintrans and maxtrans

pctfree and pctused See pctfree and pctused

Displaying a tables definition In SQL*Plus, a table's definition can be displayed with describe. A more verbose (and more complete) output can be optained with dbms_metadata.get_ddl. See also On a better describe

Create tablespace in Oracle The create tablespace statement is used to create a tablespace.

Permanent tablespace create tablespace ts_something logging datafile '/dbf1/ts_sth.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; create tablespace data datafile '/home/oracle/databases/ora10/data.dbf' size 10M autoextend on maxsize 200M extent management local uniform size 64K;

Temporary tablespace create temporary tablespace temp_mtr tempfile '/dbf1/mtr_temp01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; Note, a temporary tablespace has tempfiles, not datafiles.

Undo tablespace create undo tablespace ts_undo datafile '/dbf/undo.dbf' size 100M;

Misc More than one datafile can be created with a single create tablespace command:

create tablespace ts_sth datafile 'c:\xx\sth_01.dbf' size 4M autoextend off, 'c:\xx\sth_02.dbf' size 4M autoextend off, 'c:\xx\sth_03.dbf' size 4M autoextend off logging extent management local; Adding a tablespace to a database is a structural change that requires a backup

Create trigger in Oracle create trigger trigger-name before event create trigger trigger-name after event create trigger trigger-name instead of event

Event The code associated with a trigger is fired when a specified event occurs. The events can either be a DML event, a DDL event or a database event. (DDL event and database event triggers are also called system triggers)

DML event delete insert update update update

on on on of of

table-name table-name table-name column-name on table-name column-name-1, column-name-2 on ...

delete or insert on table-name delete or update on table-name ....

DDL event ddl-event on schema ddl-event or ddl-event on schema ddl-event or ddl-event or ddl-event ... on schema ddl-event on database schema ddl-event or ddl-event on database ddl-event or ddl-event or ddl-event ... on database

Database event database-event on schema database-event or database-event on schema database-event or database-event or database-event on schema ... database-event on database database-event or database-event on database database-event or database-event or database-event on database ... The following database events can be caught: • Servererror • logon • Logoff • startup • shutdown • suspend

Prerequisites dbmsstdx.sql must have been called. Normally, catalog.sql is run after the creation of a database. Catalog.sql in turn calls dbmsstdx.sql.

before insert or update create or replace trigger before insert or update on for each row declare begin exception <EXCEPTION HANDLERS> end ; /

After Logon on database create table logon_tbl (who varchar2(30), when date); create or replace trigger trg_logon_db after logon on database begin insert into logon_tbl (who, when) values (user, sysdate); end; /

Trigger related functions So called event attribute functions can be used within a trigger.

Disabling system triggers System triggers can be disabled by setting _system_trig_enabled to false.

create type [Oracle SQL] The create type creates a specification for either • object type • SQLJ object type • varray • nested table type • incomplete object type

Object types create or replace type base-type-name is object (....); create or replace type derived-type-name under base-type-name(....); create or replace type .... not final create or replace type .... final; create or replace type .... not instantiable; create or replace type .... instantiable;

Varray type create or replace some_type_name as varray (n) of some_other_type_name; See also declaration of varray types.

Nested type create or replace some_type_name as table of some_other_type_name; See also declaration of nested table types.

Examples set feedback off; create or replace type A as object ( x_ number, y_ varchar2(10), member procedure to_upper ); / create type body A is member procedure to_upper is begin y_ := upper(y_); end; end; / create or replace type AList as table of A; / create or replace type B as object ( l_ char(2), a_ AList, member procedure to_upper

); / create type body B is member procedure to_upper is i number; begin i := a_.first; while i is not null loop a_(i).to_upper; i:=a_.next(i); end loop; end; end; / declare b1 B; b2 B; b3 B; begin b1 := B('fr',AList(A('1','un' ),A('2','deux'))); b2 := B('de',AList(A('1','eins'),A('2','zwei'))); b3 := B('en',AList(A('1','one' ),A('2','two' ))); b2.to_upper(); dbms_output.put_line(b2.a_(2).y_); end; / drop type B; drop type AList; drop type A;

Requirements In order to create user defined types the objects option is required.

Create user [Oracle SQL] The statement create user creates a user. In the most simple form, the create user statement is one of the following three:

create user alfredo identified by alfredos_secret; create user alfredo identified externally; create user alfredo identified globally as 'external_name'; The first one creates a local user, the second one creates an external user while the last one creates global user.

Default tablespaces When a user is created, his default tablespace as well as his temporary tablespace can be specified.

create user identified by default tablespace temporary tablespace

alfredo alfredos_secret ts_users ts_temp;

Locked users A user can be created locked, that is, the user cannot connect to the database.

SQL> create user alfredo identified by passw0rd account lock; The user is now created, he can be granted some rights, for example the right to connect to the database:

SQL> grant connect to alfredo; Now, if the user tries to connect to the database, he will get ORA-28000:

SQL> connect alfredo/passw0rd ERROR: ORA-28000: the account is locked The user can now be unlocked with an alter user statement:

SQL> alter user alfredo account unlock; Which allows Alfredo to log on to the database now:

SQL> connect alfredo/passw0rd Connected.

Expiring password A user can be created such that he needs to change his password when he logs on. This is achieved with the password expire option.

SQL> create user dilbert identified by tie password expire; Now, Dilbert connecting:

SQL> connect dilbert/tie ERROR: ORA-28001: the password has expired Changing password for dilbert New password:

Assigning profiles A user can be assigned a profile when (s)he is created.

create user berta profile appl_profile The profile being assigned must be created

Displaying existing users The dba_users view shows already created users.

Restrictions on passwords The following restrictions apply to a password: • Its length must be between 1 and 30 characters • They are case insensitive • The only characters allowed are A-Z (a-z), 0-9, the underscore (_), the dollar sign ($), the hash symbol (#). • The first character must be one of A-Z or 0-9. • The password must not be a reserved oracle word (see v$reserved_words).

Public role When a user is created, the role public is automatically assigned to this user. However,

Create View in Oracle This view shows the most actual prize in table prices_

set pagesize 500 alter session set nls_date_format = 'DD.MM.YYYY'; create table sku price valid_from );

prices_ ( varchar2(38), number, date

insert insert insert insert

into into into into

prices_ prices_ prices_ prices_

values values values values

('4711', ('4711', ('4711', ('4711',

18, 19, 20, 21,

'08.01.2003'); '01.05.2000'); '18.01.2001'); '09.01.2000');

insert insert insert insert insert

into into into into into

prices_ prices_ prices_ prices_ prices_

values values values values values

('beer', ('beer', ('beer', ('beer', ('beer',

14, 15, 16, 19, 17,

'07.03.2000'); '10.01.2003'); '18.01.2001'); '16.11.2001'); '19.02.2002');

create view prices_today_ as select sku, price, valid_from from (select sku, price, valid_from, rank() over (partition by sku order by valid_from desc) r from prices_ ) where r=1; select * from prices_today_; drop table prices_; drop view prices_today_;

Cursor for nested cursors create or replace function func_sys_refcursor ( rc in sys_refcursor ) return number as v_a number; v_b varchar2(10); v_ret number := 0; begin loop fetch rc into v_a, v_b; exit when rc%notfound; v_ret := length(v_b) * v_a + v_ret; end loop; return v_ret; end; / select func_sys_refcursor( cursor ( select * from table_ref_cursor ) )

Diff between tables in Oracle set feedback off set pages 5000 create table foo ( a number, b varchar2(20) ); insert insert insert insert insert insert insert insert insert

into into into into into into into into into

foo foo foo foo foo foo foo foo foo

values values values values values values values values values

(1,'one'); (2,'two'); (3,'three'); (4,'four'); (5,'five'); (6,'six'); (7,'seven'); (8,'eight'); (9,'nine');

commit; create table foo_save as select rowid rowid_,foo.* from foo; insert update delete update delete insert update

into foo values (10,'ten'); foo set b = 'Nine' where a = 9; from foo where a = 4; foo set b = 'Sixty six', a=66 where a=6; from foo where a=2; into foo values(20,'twenty'); foo set b='Twenty' where a = 20;

select max(case when c_ = 1 then s_ else 'upd' end) s_, max(case when c_ = 1 then a else case when r_ = 1 then a else null end end) a, max(case when c_ = 1 then b else case when r_ = 1 then b else null end end) b, max(case when c_ = 1 then null else case when r_ = 2 then a else null end end) a_changed, max(case when c_ = 1 then null else case when r_ = 2 then b else null end end) b_changed from ( select row_number() over (partition by rowid_ order by s_ desc) r_, count(*) over (partition by rowid_) c_,rowid_, s_, a,b from ( select * from (select 'del' s_, foo_save.* from foo_save minus select 'del' s_, rowid rowid_, foo.* from foo) union select * from (select 'add' s_,rowid rowid_, foo.* from foo minus select 'add' s_,foo_save.* from foo_save) ) ) group by rowid_; drop table foo; drop table foo_save;

drop A truncate table or truncate cluster statement cannot be used on a synonym.

drop tablespace including contents This command will remove the tablespace, its datafiles and the objects (segments) from the data dictionary.

drop table t_foo With Oracle 10g and Flashback DB turned on, a drop table doesn't drop the table but renames it. Also, indexes and other structures remain. This makes it possible to quickly recover from human errors. Dropped objects can obviously be seen in dba_recyclebin. Alternatively, use show recyclebin in SQL*PLUS. See also drop table.

Definitely dropping a table In 10g, a table that is dropped goes into the recycle bin from where it can be recovered. In order to definitely drop (that is: purge) a table, use the following syntax:

drop table t_unused purge See also _recyclebin.

explain plan [Oracle SQL] explain plan for sql-statement; explain plan set statement_id='some identifier' for sql-statement; explain plan set statement_id='some identifier' into table_name for sqlstatement;

explain plan into table_name for sql-statement;

Oracle's explain plan Whenever you read or write data in Oracle, you do so by issuing an SQL statement. One of Oracle's task when it receives such a statement is to build a query execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan. If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this. The general syntax of EXPLAIN PLAN is:

explain plan for your-precious-sql-statement; If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the following SQL command:

explain plan into table_name for your-precious-sql-statement; If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

The Plan Table The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.

The fields (attributes) within the plan table Arguably, the most important fields within the plan table are operation, option, object_name, id, and parent_id. The pair operation and object_name define what operation would be done on (or with) object_name. If an operation has an id which other operations have as parent_id, it means the other operations feed their result to the parent. Possible values for operation are: • DELETE STATEMENT • INSERT STATEMENT • SELECT STATEMENT • UPDATE STATEMENT • AND-EQUAL • CONNECT BY • CONCATENATION • COUNT • DOMAIN INDEX • FILTER • FIRST ROW • FOR UPDATE • HASH JOIN • INDEX • INLIST ITERATOR • INTERSECTION • MERGE JOIN • MINUS • NESTED LOOPS

• • • • • • •

PARTITION, REMOTE SEQUENCE SORT TABLE ACCESS UNION VIEW Option tells more about how an operation would be done. For example, the operation TABLE ACCESS can have the options: FULL or BY ROWID or many others. Full in this case means, that the entire table is accessed (takes a long time if table is huge) whereas BY ROWID means, Oracle knows where (from which block) the rows are to be retrieved, which makes the time to access the table shorter.

dbms_xplan As of 9i, dbms_xplan can be used to format the plan table.

Operations The following table is used to demonstrate EXPLAIN PLAN:

create table test_for_ep (a number, b varchar2(100)); Now, let's explain the plan for selecting everything on that table:

delete plan_table; explain plan for select /*+ rule */ * from test_for_ep where a = 5;

Displaying the execution plan In order to view the explained plan, we have to query the plan table:

select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object" from plan_table start with id = 0 connect by prior id=parent_id; This statement is a simplified version of utlxpls.sql. utlxpls.sql is a script that Oracle ships. Here's the output of the explain plan:

SELECT STATEMENT () TABLE ACCESS (FULL)

TEST_FOR_EP

First, take a look at the indention: TABLE ACCESS is indented right. In an explain plan output, the more indented an operation is, the earlier it is executed. And the result of this operation (or operations, if more than one have are equally indented AND have the same parent) is then feeded to the parent operation. In this case, TABLE ACCESS is made first, and its result feeded to SELECT STATEMENT (which is not an actual operation). Note the FULL in paranthesis in TABLE ACCESS: this means that the entire table is accessed. Btw, sql*plus automatically explains the plan for you if autotrace is enabled. Now, let's create an index on that table:

create index test_for_ep_ix on test_for_ep (a); And do the same select statement again:

delete plan_table; explain plan for select /*+ rule */ * from test_for_ep where a = 5; The plan is now:

SELECT STATEMENT () TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP INDEX (RANGE SCAN) TEST_FOR_EP_IX

Obviously, the index (TEST_FOR_EP_IX) is used first (most indented) then used for a TABLE ACCESS, second most indented, then the result is returned. The table access is not done by a full table scan but rather by using the data's rowid.

INDEX In the last example, Oracle employed an INDEX (RANGE SCAN). The RANGE SCAN basically means, that the index was used, but that it can return more than one row. Now, we create a unique index to see how this alters the explain plan:

create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a)); delete plan_table; explain plan for select /*+ rule */ * from test_for_ep where a = 5; The explained plan is:

SELECT STATEMENT () TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP INDEX (UNIQUE SCAN) UQ_TP INDEX (UNIQUE SCAN) means, that this index is used, and it sort of guarantees that this index returnes exactly one rowid. What happens, if we query the field not for equality but for greater than (a>5)?

explain plan for select /*+ rule */ * from test_for_ep where a > 5; Here, we see that the index is used, but for a RANGE SCAN:

SELECT STATEMENT () TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP INDEX (RANGE SCAN) UQ_TP If we only query fields of a table that are already in an index, Oracle doesn't have to read the data blocks because it can get the relevant data from the index:

create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a)); delete plan_table; explain plan for select /*+ rule */ a from test_for_ep where a > 5 and a < 50; Here's the query execution planexecution plan. No table access anymore!

SELECT STATEMENT () INDEX (RANGE SCAN)

UQ_TP

MERGE JOIN See here. The first table's join key is ba while the second table's join key is aa.

create table test_for_ep_a (aa number, ab varchar2(100)); create table test_for_ep_b (ba number, bb varchar2(100)); Note, there are no indexes on both of the tables. Now, we join the tables on aa and ba:

explain plan for select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where a.aa=b.ba and a.aa > 5; As there are no indexes, both tables must be TABLE ACCESSed (FULL). After these accesses, their results are sorted.

SELECT STATEMENT () MERGE JOIN () SORT (JOIN) TABLE ACCESS (FULL) SORT (JOIN) TABLE ACCESS (FULL)

TEST_FOR_EP_B TEST_FOR_EP_A

Note MERGE JOINs can only be used for equi joins, as is demonstrated in NESTED LOOPS

NESTED LOOPS For each relevant row in the first table (driving table), find all matching rows in the other table (probed table). See also here.

create table test_for_ep_a (aa number, ab varchar2(100)); create table test_for_ep_b (ba number, bb varchar2(100)); explain plan for select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where a.aa > b.ba and a.aa > 5; Note, there is no equi join to join test_for_ep_a and test_for_ep_b, (a.aa > b.ba)

SELECT STATEMENT () NESTED LOOPS () TABLE ACCESS (FULL) TABLE ACCESS (FULL)

TEST_FOR_EP_B TEST_FOR_EP_A

Now, we put an index on TEST_FOR_EP_B and see how that influences our nested loop:

create table test_for_ep_a (aa number, ab varchar2(100)); create table test_for_ep_b (ba number, bb varchar2(100), constraint uq_ba unique(ba)); delete plan_table; explain plan for select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where a.aa > b.ba; The plan is:

SELECT STATEMENT () NESTED LOOPS () TABLE ACCESS (FULL) INDEX (RANGE SCAN)

TEST_FOR_EP_A UQ_BA

Interpreted, this means: TEST_FOR_EP_A is fully accessed and for each row, TEST_FOR_EP_B (or more accurately, its index UQ_BA) is probed. Thinking about it, this makes sense, doing the costly TABLE ACCESS once and use the index for each row. Then again, thinking about it, if TEST_FOR_EP_A is very small nad TEST_FOR_EP_B is large, this doesn't make sense anymore. This is when the Cost Based Optimizer comes into play.

Sorts Aggregate Sorts Whenever a result set must be sorted, the operation is sort. If this sort is used to return a single row (for example max or min) the options is AGGREGATE. Consider the following example:

create table t_ep ( w date, v number, x varchar2(40) ); delete plan_table; explain plan for select /*+ rule */ max(w) from t_ep where v=4; SELECT STATEMENT () SORT (AGGREGATE) TABLE ACCESS (FULL) T_EP Now: creating an index:

alter table t_ep add constraint uq_t_ep unique(v); delete plan_table; explain plan for select /*+ rule */ max(w) from t_ep where v=4; SELECT STATEMENT ()

SORT (AGGREGATE) TABLE ACCESS (BY INDEX ROWID) T_EP INDEX (UNIQUE SCAN) UQ_T_EP

TKPROF If you want to know, how much time an SQL statement acutally used, use TKPROF

flashback table [Oracle SQL] flashback table table-name-1 [, table-name-2, ...] to scn scn-expr; flashback table table-name-1 [, table-name-2, ...] to scn scn-expr enable triggers; flashback table table-name-1 [, table-name-2, ...] to scn scn-expr disable triggers; flashback table table-name-1 [, table-name-2, ...] to timestamp timestamp-expr flashback table table-name-1 [, table-name-2, ...] to timestamp timestamp-expr enable triggers; flashback table table-name-1 [, table-name-2, ...] to timestamp timestamp-expr disable triggers; flashback table table-name-1 [, table-name-2, ...] to restore point restorepoint flashback table table-name-1 [, table-name-2, ...] to restore point restorepoint enable triggers; flashback table table-name-1 [, table-name-2, ...] to restore point restorepoint disable triggers; flashback table table-name-1 [, table-name-2, ...] to before drop; flashback table table-name-1 [, table-name-2, ...] to before drop rename to other-table-name;

grant [Oracle SQL] grant system_privilege to username; grant system_privilege_1, system_privileges_2, ..,system_privileges_n to username; grant system_privilege_1 to username with admin option; grant object_privilege to username; grant object_privilege to username with grant option; grant object_privilege to username with hierarchy option; The SQL command grant allows to assign system privileges and object privileges to users and roles. For example, granting select and update to a table:

grant select, update on table_foo to user_bar;

grant ... ANY ... If a privilege is granted on ANY object (grant create any table to some_one), the user (or role) is given this privilege in all schemas, even in schema SYS. In order to prevent this, the initialization parameter o7_dictionary_accessibility must be set to false.

with admin option If with admin option is specified, the grantee can grant the granted privilege to someone else.

with hierarchy option This clause only makes sense with the select privilege.

Showing granted privileges for a user This script allows to recursively list users, their roles and privileges.

Initialization Parameters The initialization parameters can be set in the init.ora file. There are two common ways to find out to what value an initialization parameter is set: • show parameter <param_name> in sql*plus, or • select value from v$parameter where name = lower('param_name') The parameters can be changed for the currently connected session with a alter session set ... command. If a parameter should be set in another session, dbms_system.set_bool_param_in_session or dbms_system.set_int_param_in_session can be used.

Audit related parameters • • • • •

audit_file_dest audit_syslog_level audit_sys_operations audit_trail transaction_auditing

NLS related parameters • • • • • • • • • • • • • • • • • •

nls_calendar nls_comp nls_characterset nls_currency nls_date_format nls_date_language nls_dual_currency nls_iso_currency nls_language nls_length_semantics nls_nchar_conv_excp nls_numeric_characters nls_sort nls_territory nls_time_format nls_time_tz_format nls_timestamp_format nls_timestamp_tz_format

Optimizer related parameters • • • • • • • •

optimizer_dynamic_sampling optimizer_features_enable optimizer_index_caching optimizer_index_cost_adj optimizer_max_permutations optimizer_mode optimizer_percent_parallel optimizer_secure_view_merging

PL/SQL related parameters See here.

Parameters affecting SGA The following initialization parameters affect the size of the SGA: • db_block_buffers, • db_block_size, • db_cache_size, • db_keep_cache_size, • db_recycle_cache_size, • java_pool_size. • large_pool_size,

• • •

log_buffer, shared_pool_size, streams_pool_size,

Other parameters ALLOW_FREELIST_GROUPS This parameter was obsoleted after Oracle V6 and default to true since. At that time, it needed to be set in order to specify the freelist groups parameter in the storage clause.

ALWAYS_ANTI_JOIN This parameter became obsolete in 9i.

BACKGROUND_DUMP_DEST background_dump_dest specifies the directory (folder) where trace files of background processes are being written. It also specifies the location for the alert.log file. It is also used for ORA-00600 errors. See also max_dump_file_size.

BITMAP_MERGE_AREA_SIZE BUFFER_POOL_KEEP Deprecated in favour of db_keep_cache_size

BUFFER_POOL_RECYCLE Deprecated in favour of db_recycle_cache_size

COMMIT_WRITE Comes new with Oracle 10g R2, see also commit (sql) and On setting commit_write.

COMMIT_WORK Comes new with Oracle 10g R2, see also here.

COMPATIBLE The value of this parameter specifies the version that the database must adhere to. With Oracle 10g, the value of this parameter must be set at least to 9.2; and once it was set to 10 it cannot be lowered afterwards. The value of this parameter can be determined with dbms_utility.db_version.

CONTROL_FILES Every database must have at least one control file that describes important characteristics of the database. This parameter specifies their location. See Creating a Database

CONTROL_FILE_RECORD_KEEP_TIME This parameter controls the minimum number of days that a reusable record is kept in the control file. Its range is 0 .. 365 (=1 year) control_file_record_keep_time also governs the size of controlfiles.

CORE_DUMP_DEST See also max_dump_file_size.

CURSOR_SHARING This parameter influences hard parses and soft parses and is, according to metalink note 223299.1, one of the top parameters affecting performance. The parameter can be set to either exact, similar or force.

DB_BLOCK_CHECKSUM Specifies if integrity checking is enabled as block level. See also log_block_checksum

DB_BLOCK_LRU_LATCHES This parameter became obsolete in 9i.

DB_BLOCK_MAX_DIRTY_TARGET This parameter became obsolete in 9i.

DB_CACHE_ADVICE According to metalink note 223299.1, this is one of the top parameters affecting performance.

DB_CREATE_FILE_DEST DB_CREATE_FILE_DEST sets the default location for Oracle-managed datafiles. This location is also used as the default for Oracle-managed control files and online redo logs if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

You can specify a file system directory as the default location for the creation of datafiles, control files, and online redo logs. However, the directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it. Oracle generates unique names for the files, and a file thus created is an Oracle-managed file. This parameter can be useful while creating a database.

DB_CREATE_ONLINE_LOG_DEST_n DB_CREATE_ONLINE_LOG_DEST_ n(where n= 1, 2, 3, ... 5) sets the default location for Oracle-managed control files and online redo logs. You should specify at least two parameters: DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2. This provides greater fault tolerance for the logs if one of the destinations should fail. If more than one directory is specified, then the control file or online redo log is multiplexed across the directories. One member of each online redo log is created in each directory, and one control file is created in each directory. The directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it. Oracle generates unique names for the files, and a file thus created is an Oraclemanaged file. This parameter can be useful while creating a database

DB_DOMAIN DB_FILE_DIRECT_IO_COUNT This parameter became obsolete in 9i.

DB_FILE_MULTIBLOCK_READ_COUNT This parameter specifies how many blocks will be read at once when Oracle performs a full table scan or an index range scan. It doesn't affect reads on blocks that are indexed (in which case only one block is read). The value for this parameter should be chosen carefully. The OS on which Oracle is running should be capable of reading db_file_multiblock_read_count*db_block_size in one I/O request. If it is set too high, the optimizer will think that full table scan are cheap and will prefer them to the usage of indexes. On the other hand, setting it to low makes the optimizer choose indexes more often than necessary. By the way, the preference of indexes or full table scans is also influenced by optimizer_index_cost_adj.

DB_FILE_NAME_CONVERT This parameter is needed if a standby database does not have the same layout on the disk for its files as the primary database. See also log_file_name_convert

DB_FILES The maximum number of database files that can be opened for a database.

DB_FLASHBACK_RETENTION_TARGET This is one of the relevant parameters for Flashback DB.

DB_NAME This parameter must have the same value as the database name.

DB_RECOVERY_FILE_DEST This is one of the relevant parameters for Flashback DB.

DB_RECOVERY_FILE_DEST_SIZE This is one of the relevant parameters for Flashback DB.

DB_WRITER_IO_SLAVES db_writer_io_slaves simulates asynchronous IO, but they do not perform asynchronous IO, and thus, they're only meaningful if the OS does not support asynchronous IO. If the OS supports asynchronous, multible dbwr processes should be used and disk_asynch_io be set to true.

DB_16K_CACHE_SIZE According to metalink note 223299.1, this is one of the top parameters affecting performance.

DB_2K_CACHE_SIZE According to metalink note 223299.1, this is one of the top parameters affecting performance.

DB_32K_CACHE_SIZE According to metalink note 223299.1, this is one of the top parameters affecting performance.

DB_4K_CACHE_SIZE According to metalink note 223299.1, this is one of the top parameters affecting performance.

DB_8K_CACHE_SIZE According to metalink note 223299.1, this is one of the top parameters affecting performance.

DISK_ASYNCH_IO See also db_writer_io_slaves.

EVENT , anchor=>'event')

event=event_name action This parameter allows to set a diagnostic event. Multiple events must be seperated by colons:

event="<event 1>:<event 2>: <event 3>: <event n>"

GC_DEFER_TIME This parameter became obsolete in 9i.

GC_RELEASABLE_LOCKS This parameter became obsolete in 9i.

GC_ROLLBACK_LOCKS This parameter became obsolete in 9i.

GLOBAL_NAMES HASH_AREA_SIZE The default is 64K, which is far too small for most cases. A range of 512KB to 1MB should be considered. The memory for a hash join (up to the value specified with hash_area_size) is allocated from the cursor work heap (in the uga.) See also sort_area_size.

HASH_MULTIBLOCK_IO_COUNT This parameter became obsolete in 9i.

INSTANCE_NAME INSTANCE_NUMBER INSTANCE_NODESET This parameter became obsolete in 9i.

JOB_QUEUE_INTERVAL This parameter became obsolete in 9i.

JOB_QUEUE_PROCESSES Controls how many jobs can run; see also dbms_job.

LM_LOCK This parameter became obsolete in 9i.

LM_RESS This parameter became obsolete in 9i.

LOCK_NAME_SPACE LOCK_SGA On platform that support it, this parameter can be set to true which will lock the entire SGA into physical memory.

LOG_ARCHIVE_DEST Deprectated in Enterprise Edition in favour of log_archive_dest_n.

LOG_ARCHIVE_DEST_n LOG_ARCHIVE_DEST_n (as well as log_archive_dest) can only be used if the database is running in archive log mode. A common misstake when moving from the (deprecated) log_archive_dest to log_archive_dest_n is to forget one of the attributes such as SERVICE= or LOCATION= which causes a ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE when it altered with the alter system command. Attributes: • SERVICE A standby destination See archiving to standby. Use the lgwr option to specify LGWR transmission or the arch option to specify ARCH transmission. • LOCATION A local file system path, must be defined at least once. • DELAY=minutes delays applying of the redo log at the standby site.

v$archive_dest_status allows to query the status (and possibly the errors) for each of the defined archive destinations.

LOG_ARCHIVE_DEST_STATE_n log_archive_dest_state_N specifies the state for log_archive_dest_N.

LOG_ARCHIVE_FORMAT The following expandables can be used: • %s: log sequence number • %S: log sequence number, zero filled • %t: thread number • %T: thread number, zero filled • %d: DBID

LOG_ARCHIVE_START This parameter is deprecated in Oracle 10g This parameter determines if the background process ARCH is started. It can be set to either true or false. Of course, it makes no sense, if this parameter is set to true if the database is running in noarchive log mode. If ARCH is started with the database being in noarchive log mode, messages like media recovery disabled will be written into the alert.log file.

LOG_BLOCK_CHECKSUM See also DB_BLOCK_CHECKSUM

LOG_CHECKPOINT_INTERVAL See Events that trigger a checkpoint The unit of this parameter is measured in physical operating system blocks, not DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.

LOG_CHECKPOINT_TIMEOUT See Events that trigger a checkpoint

LOG_FILE_NAME_CONVERT This parameter is needed if a standby database does not have the same layout on the disk for its files as the primary database. See also db

FIXED_DATE Fixed date can be set to a date in the following format: YYYY-MM-DD HH24-MI-SS If set, sysdate returns this date instead of the current date.

alter session set nls_date_format = 'dd.mon.yyyy hh24:mi:ss'; alter system set fixed_date='2004-03-02 22:23:24'; select sysdate from dual; SYSDATE -------------------02.mar.2004 22:23:24

MAX_DUMP_FILE_SIZE This parameter specifies the maximum size for dump files such as trace files. The unit of this parameter is measured in physical operating system blocks unless it has a suffix M or K, in which case the unit is Megabyte and Kilobyte, respectively. Note, the size of physical operating system blocks is not equal to the size of DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.

MAX_IDLE_TIME O7_DICTIONARY_ACCESSIBILITY Default was true until 8i, and is false since 9i. false: only privileged users can access the data dictionary. true: any user who has been granted select any table can select from tables owned by sys. Alternatively, select_catalog_role can be granted. The parameter should (probably) be set to false. Users that need access to sys owned table should then be granted the select any dictionary privilege. The setting of this parameter influences grant ... ANY .. to ... statements.

OS_AUTHENT_PREFIX See OS authentication

OPEN_CURSORS This parameter defines how many cursors a session (not the cumulative sum of all sessions) can open at most.

PGA_AGGREGATE_TARGET According to metalink note 223299.1, this is one of the top parameters affecting performance.

PROCESSES The value of processes affects the value that the kernel parameter SEMMSL (Maximum number of semaphores in a semaphore set): it should be equal to the value of processes + 10. If there are more than on instance on a box, the value of the instance with the greatest processes must be taken. It affects also the optimal setting for SEMMNS (Number of semaphores in the system): 2*highets process value + 1*other process values + 10 * count of instances.

QUERY_REWRITE_ENABLED See Create Materialized View This parameter must be set to true to make use of function based indexes. Additionally query_rewrite_integrity must be set to trusted.

QUERY_REWRITE_INTEGRITY This parameter can be set to either • enforced • trusted • stale_tolerated This parameter must be set to trusted to make use of function based indexes. Additionally query_rewrite_enabled must be set to true. See also create materialized view

REMOTE_ARCHIVE_ENABLE REMOTE_LISTENER REMOTE_LOGIN_PASSWORDFILE remote_login_passwordfile specifies if Oracle checks for a password file and if this password file is shared among databases. The following values are possible: • none Oracle ignores the password file if it exists. • exclusive Password file is exclusively used by one database. Any user can be added to the password file. • internal Used for Oracle Parallel Server • shared The password file is shared among databases. However, the only users that can be authenticated are sys (and obsoletly: internal). If the password file is shared, only SYS can be added to the password file.

RESOURCE_LIMIT This parameter must be set to true to enforce resource limits assigned to a user through profiles See also On profile.

RESOURCE_MANAGER_PLAN Setting this parameter activates the resource manager. If the paramter is set with a prepending FORCE:, the plan can only be changed by the database administrator.

ROLLBACK_SEGMENTS Defines the rollback segments that the instance will aquire at startup On startup, Oracle devides transactions by transactions_per_rollback_segment. If the result is greater than the number of rollback segments actually brought online by the rollback_segments init param, additional rollback segments will be brought online.

SESSIONS SESSION_CACHED_CURSORS See Cached cursors.

SHARED_SERVERS SORT_AREA_SIZE The default is 64K, which is far too small for most cases. A range of 512KB to 1MB should be considered.

The memory for a sort (up to the value specified with sort_area_size) is allocated from the cursor work heap (in the uga). See also hash_area_size.

SORT_AREA_RETAINED_SIZE SORT_MULTIBLOCK_READ_COUNT This parameter became obsolete in 9i.

SPFILE Specifies the spfile to be used.

STANDBY_ARCHIVE_DEST This parameter specifies the location of archived redo logs that come from a primary database. The value of this parameter is displayed in the v$archive_dest view.

STAR_TRANSFORMATION_ENABLED STANDBY_FILE_MANAGEMENT If not set to auto, newly created tablespaces in a standby environment must be recreated manually on the standby servers as well. Similarly, newly added datafiles must be copied to the standby servers as well.

STATISTICS_LEVEL According to metalink note 223299.1, this is one of the top parameters affecting performance. It can be set to one of • ALL • TYPICAL • BASIC

SQL_TRACE Setting sql_trace=true is a prerequisite for using tkprof. It can also be set for a single session with alter session set sql_trace. After setting sql_trace to true, a trace file will be written. There is also dbms_support that should allow to trace sessions with more information. See also diagnostic event 10046. sql_trace seems to be deprecated since 10.2, but not removed. It still behaves as in earlier versions of Oracle.

TEXT_ENABLE This parameter became obsolete in 9i.

TIMED_STATISTICS This parameter must be true in order to gather timing information in v$system_event It is also useful when using tk prof.

TRANSACTIONS TRANSACTIONS_PER_ROLLBACK_SEGMENT UNDO_MANAGEMENT Set to AUTO to use Oracle 9i's new automatic undo management. See Undo Tablespaces.

UNDO_RETENTION Specifies for how many seconds undo information is kept. See Undo Tablespaces and dbms_flashback.

UNDO_SUPPRESS_ERRORS This parameter is important if • An Oracle database is upgraded to version 9i, • the upgraded database uses Undo Tablespaces, • There are still applications that use SET TRANSACTION USE ROLLBACK SEGMENT If in such a case the parameter is set to TRUE (default is FALSE), there won't be any errors; although it gets written into the alert log. See undo tablespaces.

UNDO_TABLESPACE Specifies the undo tablespaces when using automatic undo management.

USER_DUMP_DEST The value of user_dump_dest specifies the destination (path to a operating system directory) where user processes will write trace files. It is also used for ORA-00600 errors. See also max_dump_file_size.

USE_POST_WAIT_DRIVER Setting this value to true makes Oracle use post-wait drivers instead of semaphores.

UTL_FILE_DIR This parameter specifies one more more locations to where files can be written and from where files can be read using utl_file. Specifying multiple directories in the spfile:

alter system set utl_file_dir='/foo/bar/dir1','/foo/baz/dir2','/tmp' scope=spfile Thanks to Timothy Trauernicht who notified me of an error here.

WORKAREA_SIZE_POLICY According to metalink note 223299.1, this is one of the top parameters affecting performance.

Hidden parameters Parameters whose name starts with an underscore are hidden. Usually, they should not be touched! Oracle won't probably support the database if one of these parameters were changed.

_ALLOW_RESETLOGS_CORRUPTION Allows resetlogs even if it will cause corruption.

_COLUMN_TRACKING_LEVEL If set to 1 (the default), will cause SMON to update sys.col_usage$ with information regarding access patterns on table columns.

_DB_AGING_COOL_COUNT Touch count set when buffer cooled.

_DB_AGING_FREEZE_CR Make CR buffers always be too cold to keep in cache.

_DB_AGING_HOT_CRITERIA Touch count which sends a buffer to head of replacement list.

_DB_AGING_STAY_COUNT Touch count set when buffer moved to head of replacement list.

_DB_AGING_TOUCH_TIME This parameter specifies a time period in which the touch count of a buffer within the buffer cache can at most be increased once.

_DB_PERCENT_HOT_DEFAULT Percent of default buffer pool considered hot.

_DB_PERCENT_HOT_KEEP Percent keep buffer pool considered hot.

_DB_PERCENT_HOT_RECYCLE Percent recycle buffer pool considered hot.

_INIT_SQL_FILE This parameter points to the file that is executed upon creation of the database (create database). As of 9i, the value is ?/rdbms/admin/sql.bsq.

_KGHDSIDX_COUNT Controls the number of shared area subpools.

_LOG_IO_SIZE The unit of this parameter is measured in physical operating system blocks, not DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.

_REALFREE_HEAP_PAGESIZE_HINT _RECYCLEBIN This hidden parameter is available in 10g. If set to false, then tables are purged immediately at a drop table.

_SMALL_TABLE_THRESHOLD _SYSTEM_TRIG_ENABLED Defaults to true and Oracle recommends setting it to false only during database upgrade. If this parameter is set to false, then system triggers won't be executed.

_TRACE_FILES_PUBLIC Trace files (such as those created by a block dump are only readable by oracle, unless this parameter is set to true.

Setting this parameter to true causes a security risk as sensitive data might be written into the trace files!

_USE_ISM If a system features ISM (intimate shared memory), Oracle uses it by default. This can be disabled by setting _use_ism to false. As far as I can see, solaris is the only OS that has ISM.

_USE_ISM_FOR_PGA _WAIT_FOR_SYNC If set to false, a transaction that commits does not wait until the redo is flushed. However,

insert all [Oracle SQL] Also called: Multi-Table insert.

create table a (c number); create table b (c number); create table c (c number); insert into a values (1); insert into a values (42); insert into a values (100); commit; insert all into b values(c) into c values (c) select c from a; rollback; insert all into b into c select c from a; select * from b; rollback;

merge [Oracle SQL] merge [ hints ] into table-name-1 | view-name-1 [ alias-1 ] using table-name-2 | view-name-2 | subquery [ alias-2 ] on ( condition ) [ merge-update-clause ] [ merge-insert-clause ] [ error-logging-clause ]; create table table_dest ( id number primary key, txt varchar2(20) ); insert into table_dest values (1,'one'); insert into table_dest values (3,'three'); insert into table_dest values (5,'five'); commit; create table table_source ( id number primary key, txt varchar2(20) ); insert into table_source values (2,'TWO'); insert into table_source values (3,'THREE'); commit; merge into table_dest d using table_source s on (s.id = d.id) when matched then update set d.txt = s.txt when not matched then insert (id, txt) values (s.id, s.txt); select * from table_dest; ID ---------1 3 5 2

TXT -------------------one THREE five TWO

Note: It is not possible to update a field in the destination table that is used to merge with the source table.

recover [SQL*PLUS] recover database until cancel recover database until time '2004-03-21:22:59:04' recover database until change 123456 recover datafile 'filename' until cancel recover datafile 'filename' until time '2004-03-21:22:59:04' recover datafile 'filename' until change 123456 recover tablespace ts_name until cancel recover tablespace ts_name until time '2004-03-21:22:59:04' recover tablespace ts_name until change 123456 recover database using backup controlfile

Until time Performs a incomplete recovery (=Point in time recovery). The format of the time is 'YYYY-MM-DD:HH24:MI:SS'

Until change Performs a incomplete recovery. The number that follows until change is an SCN. In order to recover up to and including SCN 999, use recover until change 1000.

Standby database Recovers a standby database.

Using backup controlfile Don't use the

Ref cursors [PL/SQL] create table rc_tbl (a number, b varchar2(10)); insert into rc_tbl values (1, 'one'); insert into rc_tbl values (2, 'two'); insert into rc_tbl values (3, 'three'); create or replace procedure rc_proc(rc in out sys_refcursor) as begin open rc for select * from rc_tbl; end; / variable rc_var refcursor begin rc_proc(:rc_var); end; / print rc_var drop table rc_tbl; drop procedure rc_proc;

Renaming objects in Oracle Renaming Tables rename table_1 to table_2 This is equivalent to alter table .. rename to ...

Renaming Columns A column can only be renamed with Oracle 9i onwards. In 8i, the following solution is proposed:

rename some_table to some_table_; create view some_table as select col_1 col_1_new_name, col_2, col_3, col_n from some_table;

Renaming Tablespace Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace. For example, the following statement renames the users tablespace:

ALTER TABLESPACE users RENAME TO usersts;

replace [Oracle SQL] replace('orig-string', 'searched-string'); replace('orig-string', 'searched-string', 'replaced-string) drop table replace_table; create table replace_table ( str varchar2(30), find varchar2(10), subst varchar2(10) ); insert insert insert insert

into into into into

replace_table replace_table replace_table replace_table

values values values values

('ABC DEF GHI', ('I am $name' , ('Moo Foo Doo', ('Hello World',

'DEF' , '$name', 'oo' , 'World',

'bla' ); 'Rene'); 'XX' ); null );

column R format a30 select str, find, subst, replace(str, find, subst) R from replace_table; STR ----------------------------------------------------------ABC DEF GHI I am $name Moo Foo Doo Hello World

FIND SUBST R ---------- ---------DEF $name oo World

bla Rene XX

ABC bla GHI I am Rene MXX FXX DXX Hello

savepoint [Oracle SQL] -- DML statements savepoint savepoint_name_1; -- DML statements savepoint savepoint_name_2; -- further DML statements savepoint savepoint_name_n; -- further DML statements rollback to savepoint savepoint_name_n

A little demonstration create table savepoint_test ( n number ); Inserting some values:

insert into savepoint_test values (1); insert into savepoint_test values (2); insert into savepoint_test values (3); Establishing a savepoint named sp_one:

savepoint sp_one; Inserting three more rows:

insert into savepoint_test values (10); insert into savepoint_test values (20); insert into savepoint_test values (30); Establishing a 2nd savepoint_test, this time named sp_two:

savepoint sp_two; Again inserting some values:

insert into savepoint_test values (100); insert into savepoint_test values (200); insert into savepoint_test values (300); Let's check what we have. Not surprisingly, there are nine rows:

select * from savepoint_test; N ---------1 2 3 10 20 30 100 200 300 Rolling back to savepoint sp_two:

rollback to sp_two; Checking once more the table's content:

select * from savepoint_test; N ---------1 2

3 10 20 30 All DMLS (data manipulated) after savepoint sp_2 have been rolled back. Yet again, inserting some values ...

insert into savepoint_test values (111); insert into savepoint_test values (222); insert into savepoint_test values (333); ... and rolling back to sp_two;

rollback to sp_two; select * from savepoint_test; We're at savepoint sp_2 again, so we have the same result set again.

N ---------1 2 3 10 20 30 Rolling back to an even earlier time:

rollback to sp_one; What do we have in the table now?

select * from savepoint_test; Of course, we only find the values that have existed before savepoint sp_1:

N ---------1 2 3

set constraints [Oracle SQL] set constraint[s] all { immediate | deferred }; set constraint[s] constraint-name-1 [, constraint-name-n ...] { immediate | deferred };

select [Oracle SQL] [with...] select /*+ hint */ col_1, col_2, ... from table_1 join table_2 on q=r [where x=y and b=c] [hierarchical-clause] [group_by] [having e=f] [model-clause] [set-operator] [order by f]

Set Operators select select ... ... select

col_1, col_2, col_3, ... col_n from table_1 set operator col_1, col_2, col_3, ... col_n from table_2 set operator col_1, col_2, col_3, ... col_n from table_n;

The four set operators union, union all, intersect and minus allow to serially combine more than one select statements. Although more than one select statement will then be present, only one result set is then returned. If the select statements vary in their numbers of returned columns, Oracle report an ORA-01789: query block has incorrect number of result columns. For the demonstration of set operators, the following test tables are created:

create table table_1 ( col_1 number, col_2 varchar2(10), col_3 date ); create table table_2 ( col_1 number, col_2 varchar2(10), col_3 date ); Then, a few values are inserted:

alter session set nls_date_format='dd.mm.yyyy'; insert into table_1 values ( 3, 'hello' , to_date('28.08.1970')); insert into table_1 values ( 42, 'galaxy', to_date('01.01.2001')); insert into table_1 values (100, 'bye' , to_date('09.02.2004')); insert into table_2 values (

3, 'bye'

, to_date('28.08.1970'));

insert into table_2 values ( 42, 'galaxy', to_date('01.01.2001')); insert into table_2 values ( 60, 'bye' , to_date('09.02.2004')); insert into table_2 values ( 3, 'hello' , to_date('05.05.2002'));

union all union all selects all rows from all select statements: select col_1, col_2, col_3 from table_1 union all select col_1, col_2, col_3 from table_2; As can be seen, all records of both tables are returned:

COL_1 ---------3 42 100 3 42 60 3

COL_2 ---------hello galaxy bye bye galaxy bye hello

COL_3 ---------28.08.1970 01.01.2001 09.02.2004 28.08.1970 01.01.2001 09.02.2004 05.05.2002

union union all is very similar to union, however, it dismisses duplicate rows found across different select statements:

select col_1, col_2, col_3 from table_1 union select col_1, col_2, col_3 from table_2; The galaxy record is a duplicate. Hence, it is returned only once:

COL_1 ---------3 3 3 42 60 100

COL_2 ---------bye hello hello galaxy bye bye

COL_3 ---------28.08.1970 28.08.1970 05.05.2002 01.01.2001 09.02.2004 09.02.2004

intersect intersect only returns the rows that are found in all select statements: select col_1, col_2, col_3 from table_1 intersect select col_1, col_2, col_3 from table_2; Only the galaxy record is returned. It's the only record that is stored in both tables:

COL_1 COL_2 COL_3 ---------- ---------- ---------42 galaxy 01.01.2001

minus minus returns all rows from the first select statements except those who are duplicated in a following select statement:

select col_1, col_2, col_3 from table_1 minus select col_1, col_2, col_3 from table_2; As the galaxy record is found in both tables, it is removed from the first table's record set:

COL_1 COL_2 COL_3 ---------- ---------- ---------3 hello 28.08.1970

100 bye

09.02.2004

as of ... to be finished ... see Flashback query and Flashback query example 1.

versions between ... to be finished ... see Flashback version query

Order of select processing 1. where 2. group by 3.

having

4. analytic functions

Oracle Concepts - Starting your Oracle Database Oracle Tips by Burleson Consulting

Starting your Oracle Database One of the most common jobs of the database administrator is to startup or shutdown the Oracle database. Typically we hope that database shutdowns will be infrequent for a number of reasons: * Inconvenience to the user community. * Anytime you cycle the database, there is a risk that it will not restart. * It flushes the Oracle memory areas, such as the database buffer cache. Performance on a restarted database will generally be slow until the database memory areas are “warmed” up. Why would you shutdown your database? Some reasons include database maintenance: * Applying a patch or an upgrade. * Allow for certain types of application maintenance. * Performing a cold (offline) backup of your database. (We recommend hot backups that allow you to avoid shutting down your database) * An existing bug in your Oracle software requires you to restart the database on a regular basis. When the time comes to “bounce” the database (using the shutdown and startup commands), you will use SQL*Plus to issue these commands. Let’s look at each of these commands in more detail.

The Oracle Startup Command You start the Oracle database with the startup command. You must first be logged into an account that has sysdba or sysoper privileges such as the SYS account (we discussed connecting as SYSDBA earlier in this book). Here then is an example of a DBA connecting to his database and starting the instance: C:\Documents and Settings\Robert>set oracle_sid=booktst C:\Documents and Settings\Robert>sqlplus "sys as sysdba" SQL*Plus: Release 10.1.0.2.0 - Production on Mon Feb 21 12:35:48 Enter password: xxxx Connected to an idle instance. SQL> startup ORACLE instance started. Or

STARTUP PFILE=ORACLE_BASE\admin\db_name\pfile\init.ora

Total System Global Area 251658240 bytes Fixed Size 788368 bytes Variable Size 145750128 bytes Database Buffers 104857600 bytes Redo Buffers 262144 bytes Database mounted. Database opened. In this example from a Windows XP server, we set the ORACLE_SID to the name of the database and we log into SQL*Plus using the “sys as sysdba” login. This gives us the privileges we need to be able to startup the database. Finally, after we enter our password, we issue the startup command to startup the database.

Oracle displays its progress as it opens the database, and then returns us to the SQL*Plus prompt once the startup has been completed. When Oracle is trying to open your database, it goes through three distinct stages, and each of these is listed in the startup output listed previously. These stages are: * Startup (nomount) * Mount * Open Let’s look at these stages in a bit more detail.

The Startup (nomount) Stage When you issue the startup command, the first thing the database will do is enter the nomount stage. During the nomount stage, Oracle first opens and reads the initialization parameter file (init.ora) to see how the database is configured. For example, the sizes of all of the memory areas in Oracle are defined within the parameter file. After the parameter file is accessed, the memory areas associated with the database instance are allocated. Also, during the nomount stage, the Oracle background processes are started. Together, we call these processes and the associated allocated memory the Oracle instance. Once the instance has started successfully, the database is considered to be in the nomount stage. If you issue the startup command, then Oracle will automatically move onto the next stage of the startup, the mount stage.

Starting the Oracle Instance (Nomount Stage) There are some types of Oracle recovery operations that require the database to be in nomount stage. When this is the case, you need to issue a special startup command: startup nomount, as seen in this example: SQL> startup nomount

The Mount Stage When the startup command enters the mount stage, it opens and reads the control file. The control file is a binary file that tracks important database information, such as the location of the database datafiles. In the mount stage, Oracle determines the location of the datafiles, but does not yet open them. Once the datafile locations have been identified, the database is ready to be opened.

Mounting the Database Some forms of recovery require that the database be opened in mount stage. To put the database in mount stage, use the startup mount command as seen here: SQL> startup mount If you have already started the database instance with the startup nomount command, you might change it from the nomount to mount startup stage using the alter database command: SQL> alter database mount;

The Open Oracle startup Stage The last startup step for an Oracle database is the open stage. When Oracle opens the database, it accesses all of the datafiles associated with the database. Once it has accessed the database datafiles, Oracle makes sure that all of the database datafiles are consistent.

Opening the Oracle Database To open the database, you can just use the startup command as seen in this example SQL> startup If the database is mounted, you can open it with the alter database open command as seen in this example: SQL> alter database open;

Opening the Database in Restricted Mode You can also start the database in restricted mode. Restricted mode will only allow users with special privileges (we will discuss user privileges in a later chapter) to access the database (typically DBA’s), even though the database is technically open. We use the startup restrict command to open the database in restricted mode as seen in this example. SQL> startup restrict

You can take the database in and out of restricted mode with the alter database command as seen in this example: -- Put the database in restricted session mode. SQL> alter system enable restricted session; -- Take the database out of restricted session mode. SQL> alter system disable restricted session; Note: Any users connected to the Oracle instance when going into restricted mode will remain connected; they must be manually disconnected from the database by exiting gracefully or by the DBA with the “alter system kill session” command.

Problems during Oracle Startup The typical DBA life is like that of an airline pilot, “Long moments of boredom followed by small moments of sheer terror”, and one place for sheer terror is an error during a database startup. The most typical reason for a database not starting up is a prior database crash, a data corruption, disk failure or some other catastrophic event from which the database cannot recover. In these cases, you have to go into database recovery mode to start your instance. There is a chapter on recovery later in this book and we will discuss what to do when Oracle will not startup.

Oracle shutdown command Concepts You might take the database down to make configuration changes, for a backup or to upgrade Oracle software. When it’s time to bring your database down, we use the shutdown command. As you might guess, the shutdown command comes in many forms. Oracle has three shutdown modes: * Normal (default) - waits for in-flight work to complete * Immediate - terminates all sessions and does a rollback on all uncommitted transactions * Abort - aborts all sessions, leaving current DML in need of rollback, de-allocates the SGA and terminates the background processes. The "normal" and "immediate" modes can take a long time in you have in-flight transactions, and many Oracle DBA's ensure a swift clean shutdown this way, aborting the sessions, re-starting to allow warmstart rollback of the aborted transactions, and a shutdown immediate to close cleanly: SQL> shutdown abort SQL> startup SQL> shutdown immediate

Oracle Normal Shutdown A normal shutdown of an Oracle database is actually rarely used. This is because the normal shutdown waits for everyone to complete their work and then logoff in an orderly fashion. When a normal shutdown occurs, the database is closed in a normal manner, and all changes made in the database are flushed to the database datafiles. This is known as a “clean shutdown”. Most of the time this is not practical… there always seems to be someone who has left for vacation and who forgot to log out, or there are times that Oracle processes become “zombied” (this is where Oracle thinks someone is connected to the database but they really are not). In these cases, the database will never come down. It will simply wait forever until you manually kill those sessions. Because of this, we often recommend the shutdown immediate or shutdown abort commands, which we will discuss in the next sections. Here is an example of the use of the normal shutdown command. SQL> shutdown When you execute a shutdown, Oracle will flush all the changes in memory out to the database datafiles. This makes database startup quicker because the database is in a consistent state.

Think of it this way: if you jump into the air and land on your feet, you have landed in a way that prepares you to make another jump. If, instead, you jump and land on your back, you are in no position to make another jump; instead, you must perform a recovery by taking the actions required to stand again. A clean shutdown is one that is prepared to come back up without delay. A dirty shutdown is one that lands on its back; it can not come back up without first recovering itself.

Oracle Shutdown Immediate Perhaps the best way to initially shutdown the database is the shutdown immediate command. This command will prevent any new logins, then rollback any uncommitted transactions, and then bring down the database. In the process of bringing down the database, Oracle will flush all the changes in memory out to the database datafiles too, just like a regular shutdown does. This makes database startup quicker. Here is an example of shutting down a database with the shutdown immediate command: SQL> shutdown immediate The shutdown immediate command will work most of the time, but there are times when it can hang and fail to shutdown the database. In these cases, the shutdown abort command is called for.

Oracle Shutdown Abort Command The shutdown abort command is pretty much a guaranteed way to get your database to shutdown. It’s a “hard crash” of the database, and this can result in a longer time to start the database back up. Still, you can’t really hurt the database using the shutdown abort command, and during your DBA years you will find more than a few occasions to use the shutdown abort command. A shutdown abort should not be your first shutdown method of choice, there may be times when you must force the database down. Here is an example using the shutdown abort command: SQL> shutdown abort Next, let’s change topics and examine the Oracle data dictionary where information about our database resides.

shutdown [SQL*Plus] shutdown shutdown shutdown shutdown shutdown shutdown

abort immediate normal transactional transactional local

Shutting down an instance (and closing the database that is mounted on the instance) takes three steps: 1. Close the database. 2. Dismount the database. 3. Shut down the instance of the Oracle server. Oracle automatically performs all three steps when an instance is shut down. See also the states of an instance. In order to shutdown an instance, one needs to be connected as sysdba or as sysoper. An instance is started with startup.

Different types of shutdown shutdown abort When a database is started after a shutdown abort, Oracle peforms crash recovery. If there is a logoff trigger, it won't be executed.

shutdown immediate yet to be finished :) If there is a logoff trigger, it won't be executed.

shutdown normal shutdown normal is equivalent to shutdown

shutdown transactional yet to be finished :)

Controling the shutdown behaviour on Windows On Windows, it can be specified how the instance is shut down when the service is stopped with the registry value ORA_%ORACLE_SID%_SHUTDOWN.

Logging Oracle records shutdowns of the instance in the alert.log file.

Links See also startup.

startup [SQL*Plus] startup startup startup startup startup

nomount mount restrict open recover

startup pfile = /path/to/init.ora startup starts (creates) an Oracle instance. While the instance is created, Oracle reads init.ora or an spfile. In order to startup an instance, one needs to be connected as sysdba or as sysoper. An instance is terminated with shutdown.

Options startup nomount Only the instance is started. That is the SGA is allocated and the background processes are started. Neither controlfiles are mounted nor database opened.

startup mount Instance is started and controlfiles mounted, but database not opened.

startup Instance is started and controlfiles mounted and database opened.

startup restrict startup restrict is similar to startup, however, Oracle then allows only users with restricted privileges to access the database.

pfile The pfile=init.ora indicates the location and name of the init.ora file that the instance reads when it is started. This method is used when the init.ora file is to be taken from a non default location or has a non default name.

Logging Oracle records startups of the instance in the alert.log file.

Links See also startup. ORA-01031 when starting an instance

sys_context [Oracle] sys_context(namespace_string, parameter_string) sys_context(namespace_string, parameter_string, len)

Current session sys_context can for example be used to find out which row in v$session is the "current" session:

select sid from v$session where audsid=sys_context('userenv','SESSIONID');

Attributes for userenv • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • •

action audited_cursorid authenticated_identity authentication_data authentication_method authentication_type deprecated bg_job_id client_identifier client_info current_bind current_schema current_schema_id current_sql (also current_sqln for n 1 .. 7) current_sql_length current_user Deprecated in favor of session_user current_userid Deprecated in favor of session_userid db_domain db_name db_unique_name entryid enterprise_identity external_name deprecated fg_job_id global_context_memory global_uid host identification_type instance instance_name ip_address see also utl_inaddr.get_host_name isdba lang abbreviation for language language module network_protocol nls_calendar nls_currency

• • • • • • • • • • • • • • • • • •

nls_date_format nls_date_language nls_sort nls_territory os_user policy_invoker proxy_enterprise_identity proxy_global_uid proxy_user proxy_user_id server_host service_name session_user session_userid sessionid sid See also dbms_support.mysid statementid terminal

sys_refcursor sys_refcursor can be used to pass cursors from and to a stored precedure. The following procedure proc_ref_cursor accepts a sys_refcursor and loops on that cursor and prints out (dbms_output) what it fetches:

create or replace procedure proc_ref_cursor (rc in sys_refcursor) as v_a number; v_b varchar2(10); begin loop fetch rc into v_a, v_b; exit when rc%notfound; dbms_output.put_line(v_a || ' ' || v_b); end loop; end; / Here's a table that we will use to select from:

create table table_ref_cursor ( a number, b varchar2(10) ); insert insert insert insert

into into into into

table_ref_cursor table_ref_cursor table_ref_cursor table_ref_cursor

values(1, values(2, values(3, values(4,

'one'); 'two'); 'three'); 'four');

commit; Here's another stored procedure (call_proc_ref_cursor) that opens a cursor (select * from table_ref_cursor) and passes that cursor to proc_ref_cursor.

create or replace procedure call_proc_ref_cursor as v_rc sys_refcursor; begin open v_rc for select * from table_ref_cursor; proc_ref_cursor(v_rc); close v_rc; end; / See also the keyword cursor and ref cursors in PL/SQL.

truncate table|cluster [Oracle SQL] truncate table table_name; truncate cluster cluster_name; A statement like delete from tablename deletes all records in the table, but it does not free any space (see On table sizes). In order to free the space as well, use truncate. However, a truncate can not be rolled back. Basically, a truncate statement resets the high water mark to its initial position. A truncate statement cannot be used on a synonym. The truncate statement is not the same as the trunc statement.

Steps involved Oracle executes the following steps for a truncate statement:

1. Exclusive access to the segment header is requested. 2.

The segment header is locked

3. All blocks within the to-be-truncated segment are flushed from buffer cache to disk. 4. The free lists in the segment header block and in the free list groups blocks (if free list group blocks exist) are cleared. The changes (that is, the before image) hereof are recorded in a rollback segment.

5. The segment is truncated. That is, the high water mark is reset. This change is recorded in the rollback 6. 7.

segment as well. Extents beyond minextents are trimmed. In an OPS environment, the segment header blocks and the free list group blocks are flushed to disk