oracle 9i cheat sheet

Copyright © 2000-2004 Dr. Lars Ditzel Database Management

Oracle Server 9i
Quick Reference Guide
Disclaimer 2
Oracle Architecture 3
Instance 4
Database 8
Database Utilities 9
Tablespaces, Datafiles & Segments 10
Logfiles 11
Tables, Constraints & Triggers 12
Views, Synonyms & Sequences 14
Clusters 14
Index-organized Tables 15
Indexes 15
Undo Management 16
Temporary Segments 16
Users, Privileges, Resources & Policies 17
Auditing 18
Net Services 18
Recovery Manager 20
Distributed DB, Replication, Heterogenous Services,
Advanced Queuing & Data Warehousing 22
Real Application Clusters 24
Globalization Support 26
SQL*Plus 27
Data Types (PL/SQL & Database) 28
SQL, PL/SQL & Java 30
Embedded SQL 34
SQLJ 36
Label Security 37
Performance 37

www.larsditzel.de

2 Oracle Server 9i Quick Reference Guide

Disclaimer
This document is nothing official by Oracle online documentation. Optional parts are
Corporation. Oracle holds all rights on its enclosed in square brackets [], a list of items is
documentation. Certain terms are registered enclosed in curly braces {}, alternatives within
trademarks. brackets or braces are separated by a vertical
This quick reference guide is some kind bar |. Keywords appear in regular style and
of outgrown cheat-sheet for all of us with are not case-sensitive in most OS. Placehold-
limited supply of system memory. It enables ers for input data are enclosed in brackets <>
quick lookup of syntax for statements which with corresponding default values underlined.
one might need less frequently in daily A comma followed by an ellipsis inclosed in
work. So the major goal of this document is square brackets [, …] indicates that the pre-
compaction, not clarity. Some syntax options ceding syntactic element may be repeated. An
only work under certain circumstances, some ellipsis without preceding comma … indicates
options exclude each other. This behaviour that the corresponding syntactic elements have
is intentionally not reflected to avoid prolif- been specified beforehand.
eration of similar statements. Be aware of your Each section commonly starts with dynamic
actions! The author disclaims liability for errors performance views and static data dictionary
within this document and subsequent mistakes views (only DBA variants listed) and – oc-
that might harm your database. In case of casionally – tables. Initialization parameters
uncertainties please refer to Oracle’s excellent follow as well as database packages and im-
original documentation, which can be found portant files or processes. Then comes a list
online at the Oracle Technology Network of performance tuning measures and desup-
(otn.oracle.com) – comprising several hundred ported or deprecated features. The main part
megabytes meanwhile – or contact Oracle Cus- of each section is comprised of SQL statements
tomer Support. In any quick reference guide grouped by functionality. The final section may
there is also no room for discussing concepts state utilities if any exist.
and techniques. If you do not know where to Changes from Releases 8i to 9i are colored
start just read the Oracle Database Concepts blue for new features or red for obsolete
Guide which is very concise. Advanced discus- features. Some features of Server9i Release 2
sions can be found elsewhere, my favourite have been added without another special color
resource being asktom.oracle.com. Oracle highlighting than blue but I did not scan the
Guru Thomas Kyte has also written excellent whole Release 2 documentation yet. And prob-
books on techniques and best practices. Other ably it will not take too long until Server10i
invaluable repositories are the sites of Jonathan shows up…
Lewis (www.jlcomp.demon.co.uk) and of Steve Outdated syntax has not been removed most
Adams (www.ixora.com.au). Not to forget that of the time as one eventually comes across da-
lots of technical whitepapers can be found at tabases running older releases of Oracle Server.
Oracle’s Technet. – One of my customers still uses Server7.
This reference uses a modified Backus-Naur Any suggestions and corrections to improve
Form syntax which is adapted from the Oracle this guide are welcome.

Dr. Lars Ditzel Database Management • Ringstraße 23a • 65824 Schwalbach • Germany
Phone/Fax +49 (6196) 889739-0/5 • Mobile +49 (160) 94651446 • info@larsditzel.de

www.larsditzel.de Copyright © 2000-2004 Dr. Lars Ditzel Database Management

Oracle Server 9i Quick Reference Guide 3

��
��
��
��
��
��
��
��
��
��
��
��
��
��
��
��
��
��
��
��
��
��
� ��
��

��
��

��
��
��
��
��
��
��
� ��
��
��
��
� ��
��
��
� ��
��
��
��
��
��
��
��
��
��
��
��
��
��
��
��
� ��
��
��

��
��
��
��
��
��

��
��
��
��
��
��
��
��
��
� ��
��
� ��
��
� ��
��
��
��
��

www.larsditzel.de


Instance

Background Processes (v$bgprocess) Parameters (init<sid>.ora) Tuning/Contention
ARC<n>, CJQ0, J<nnn>, CKPT, DBW<n>, spfile, ifile, instance_name, service_names, Statistics classes:
DIAG, DMON, EMN0, LCK<n>*, LGWR, db_block_size, sga_max_size, db_cache_size, 1 User, 2 Redo, 4 Enqueue, 8 Cache, 16
LMD0*, LMON*, LMS<n>*, LNSV, LSP0, db_keep_cache_size, db_recycle_cache_size, OS, 32 RAC, 64 SQL, 128 Debug
MRP0, NSV0, PMON, QMN<n>, RECO, db_<n>k_cache_size, db_cache_advice, Buffer cache: «Cache Hit Ratio» (v$sysstat)
RLAP, RSM0, SMON, RFS<n> shared_pool_size, log_buffer, large_pool_size, or per pool (v$buffer_pool_statistics)
BSP<n>*, SNP<n> << obsolete java_pool_size, shared_pool_reserved_size, 1 – («physical reads» / («db block gets» +
* RAC processes pre_page_sga, sessions, processes, user_dump_ «consistent gets»)) < 90–95% -> increase
Failure of LGWR (Err 470), CKPT (470), DBW<n> dest, background_dump_dest, max_dump_
(471), ARC<n> (473), SMON (474) or RECO (476) «db_block_buffers» or «buffer_pool_keep»,
lead to termination of instance by PMON. Failure of file_size, local_listener, remote_listener, «buffer_pool_recycle»
PMON leads to termination of instance by DBW<n> mts_service, circuits, dispatchers, max_dis-
(Err 472). Shared pool: «Shar. Cursors»
patchers, shared_servers, max_shared_servers, (v$librarycache) gethitratio for SQL AREA
Failed SNP<n> processes are restarted by PMON.
shared_server_sessions, dbwr_io_slaves, < 99%
remote_os_authent, os_authent_prefix, Library cache: sum(reloads) / sum(pins) > 1%
Foreground Processes dml_locks, enqueue_resources, parallel_auto- (v$librarycache)
D<nnn>, S<nnn>, P<nnn> matic_tuning, parallel_min_servers, paral-
Dict. cache: sum(getmisses) / sum(gets) >
lel_max_servers, parallel_min_percent, paral-
15% (v$rowcache) -> increase «shared_
Views & Tables lel_adaptive_multi_user, parallel_threads_
pool_size»
v$fixed_table, v$fixed_view_definition, per_cpu, parallel_execution_message_size, par-
allel_broadcast_enabled, oracle_trace_enable, LRU latch: «cache buffers lru chain»
v$indexed_fixed_column, v$instance, v$sga, (v$latch) misses / gets > 1% -> increase
v$sgastat, v$pgastat, v$session, v$process, oracle_trace_collection_{name | path | size},
oracle_trace_facility_{name | path}, java_ «db_block_lru_latches» (max. CPU * 2 or
v$bgprocess, v$version, product_component_ BUFFERS / 50)
version, v$license, v$option, v$access, v$timer, soft_sessionspace_limit, java_max_sessions-
pace_size, lock_sga, shared_memory_address, Redo buffer: «redo%retries» (v$sysstat)
v$parameter, v$parameter2, v$spparameter,
hi_shared_memory_address, object_cache_op- PGA: «%ga memory%» (v$sysstat), «sorts%»
v$system_parameter, v$system_parameter2,
timal_size, object_cache_max_size_percent, (v$sysstat), sorts (v$sqlarea), «work-
v$obsolete_parameter, v$sql, v$sqlarea,
serial_reuse, session_max_open_files, timed_ area%» (v$sysstat, v$sesstat), v$pgastat,
v$sqltext, v$sqltext_with_newlines, v$sql_cur-
os_statistics, cursor_sharing, drs_start v$sql_workarea, v$sql_workarea_active,
sor, v$sql_bind_data, v$sql_bind_meta-
pga_%_mem (v$process)
data, v$sql_shared_memory, v$sql_plan,
v$sql_workarea, v$sql_workarea_active, Packages
v$librarycache, v$rowcache, v$rowcache_par- Deprecated Features
DBMS_SYSTEM
ent, v$rowcache_subordinate, v$open_cursor, set_sql_trace_in_session v$mts
v$object_dependency, v$db_object_cache, DBMS_SUPPORT db_block_buffers, buffer_pool_keep, buf-
v$shared_pool_reserved, v$bh, x$bh, v$cache, mysid, {start | stop}_trace, {start | stop}_ fer_pool_recycle, mts_circuits, mts_dispatch-
v$subcache, v$buffer_pool, v$buffer_pool_ trace_in_session ers, mts_max_dispatchers, mts_servers,
statistics, v$db_cache_advice, v$statistics_lev- DBMS_SESSION mts_sessions
el, v$filestat, v$tempstat, v$sysstat, v$sesstat, set_sql_trace, {set | clear}_identi- utlbstat.sql, utlestat.sql
v$mystat, v$statname, v$waitstat, v$latch, fier, {set | list | clear}_context, set_role,
v$latchname, v$latchholder, v$latch_parent, set_nls, is_role_enabled, is_session_alive, Desupported Features
v$latch_children, v$event_name, v$system_ unique_session_id, close_database_link,
event, v$session_event, v$session_wait, v$recent_bucket, v$current_bucket, db_
reset_package, modify_package_state, block_lru_latches, use_indirect_data_buffers,
v$sess_io, v$segment_statistics, v$segstat, switch_current_consumer_group, free_un-
v$segstat_name, v$circuit, v$queue, v$shared_ db_block_lru_extended_statistics, db_block_
used_user_memory, set_close_cached_ lru_statistics, lock_sga_areas, shared_pool_re-
server, v$shared_server_monitor, v$dispatcher, open_cursors
v$dispatcher_rate, v$reqdist, v$queue, v$lock, served_min_alloc, parallel_server_idle_time,
DBMS_SHARED_POOL parallel_transaction_resource_timeout, paral-
v$enqueue_lock, v$enqueue_stat, v$locked_
keep, unkeep, sizes lel_min_message_pool, mts_rate_log_size,
object, v$global_blocked_locks, dba_locks,
DBMS_APPLICATION_INFO mts_rate_scale, mts_max_servers
dba_lock, dba_lock_internal, v$session_con-
set_module, set_action, set_client_info,
nect_info, v$session_longops, v$system_cur-
read_module, read_client_info
sor_cache, v$session_cursor_cache,
v$session_object_cache, v$bsp, v$px_session,
v$px_sesstat, v$px_process, v$px_process_sys- Files
stat, v$pq_sesstat, v$pq_slave, v$pq_sysstat, dbmspool.sql, dbmssupp.sql, catparr.sql,
v$pq_tqstat, v$execution, v$mls_parameters, utldtree.sql
deptree, session_context

www.larsditzel.de


Instance (cont.)

Parameters | false}, remote_os_roles = {true | false}, repli- Session Scope Dynamic Init. Parameters
show parameter[s] <string> cation_dependency_tracking = {true | false}, alter session set <param> [=] <value>;
alter system set <param> [=] <value> rollback_segments = (<rbs> [, …]), row_lock- cursor_sharing = {similar | exact | force},
[comment ‘<text>’] [deferred] ing = {always | default | intent}, serial_reuse = db_block_checking, db_create_file_dest =
[scope = {memory | spfile | both} ] {disable | select | sml | plsql | all}, session_max_ ‘<dir>’, db_create_online_log_dest_<1-5> =
[sid = { ‘<sid>’ | ‘*’ } ]; open_files = <10>, sessions = <(1.1*proc)+5>, ‘<dir>’, db_file_multiblock_read_count = <8>,
sga_max_size = <n>, shadow_core_dump global_names = {true | false}, hash_area_size
alter system reset <param>
= {partial | full}, shared_memory_address = = <n>, hash_join_enabled = {true | false},
[scope = {memory | spfile | both} ]
<0>, shared_pool_reserved_size = <5%SP>, log_archive_dest_<1-10> = {location = <path>
[sid = { ‘<sid>’ | ‘*’ } ];
shared_server_sessions = <n>, spfile = <file>, | service = <serv>} [optional | mandatory]
sql92_security = {true | false}, sql_trace = {true [[no]reopen [=<300>]] [arch | lgwr] [synch |
Static Initialization Parameters | false}, tape_asynch_io = {true | false}, thread async = <n>] [[no]affirm] [[no]delay [= <30>]]
active_instance_count = <n>, audit_file_dest = <n>, transactions_per_rollback_segment = [[no]dependency] [[no]alternate [= <dest>]]
= <dir>, audit_trail = {none | false | db | true | <5>, undo_management = {manual | auto}, [[no]max_failure [= <n>] [[no]quota_size
os}, background_core_dump = {full | partial}, util_file_dir = <dir> [= <n>] [[no]quota_used] [[no]register [=
bitmap_merge_area_size = <1m>, blank_trim-
<loc>]], log_archive_dest_state_<1-10> = {en-
ming = {true | false}, buffer_pool_{keep | Dynamic Initialization Parameters able | defer | alternate}, log_archive_min_suc-
recycle} = {<n> | (buffers: <n>, lru_latches:
aq_tm_processes = <n>, archive_lag_target = ceed_dest = <1>, max_dump_file_size = {<n>
<n>} << deprecated, circuits = <n>, cluster_data-
<n>, background_dump_dest = ‘<dir>’, back- | unlimited}, nls_calendar = ‘<cal>’, nls_comp
base = {true | false}, cluster_database_instances
up_tape_io_slaves = {true | false}, control_file_ = {binary | ansi}, nls_currency = <curr>,
= <n>, cluster_interconnects = <ip>[:<ip>…],
record_keep_time = <7>, core_dump_dest = nls_date_format = ‘<fmt>’, nls_date_lan-
commit_point_strength = <n>, compatible
‘<dir>’, db_{2|4|8|16|32}k_cache_size = <0>, guage = <lang>, nls_dual_currency = <curr>,
= <x.x.x>, control_files = (“<file>” [, …]),
db_block_checking = {true | false}, db_block_ nls_iso_currency = <terr>, nls_language =
cpu_count = <n>, create_bitmap_area_size
checksum = {true | false}, db_cache_advice <lang>, nls_length_semantics = {byte | char},
= <8m>, cursor_space_for_time = {true |
= {on | ready | off }, db_cache_size = <48m>, nls_nchar_conv_excp = {true | false}, nls_nu-
false}, db_block_buffers = <n> << deprecated,
db_{keep | recycle}_cache_size = <0m>, meric_characters = <sep>, nls_sort = {binary |
db_block_size = <2048>, db_domain =
dispatchers = ‘{ (protocol = <prot>) | (descrip- <ling>}, nls_territory = <terr>, nls_time_for-
<str>, {db | log}_file_name_convert = (‘prim’,
tion = (address =…) ) | (address = (protocol mat = ‘<fmt>’, nls_timestamp_format =
‘stdby’ [, …]), db_files = <200>, db_name =
= <prot>) (host = <node>) (port = <port>) ‘<fmt>’, nls_timestamp_tz_format = ‘<fmt>’,
<str>, db_writer_processes = <1>, dblink_en-
)} (connections = <n>) (dispatchers = <1>) nls_time_tz_format = ‘<fmt>’, object_cache_
crypt_login = {true | false}, dbwr_io_slaves =
(index = <n>) (listener = <list>) ( {pool | max_size_percent = <10>, object_cache_op-
<0>, disk_asynch_io = {true | false}, distrib-
multiplex} = {1 | on | yes | true | both | ({in | timal_size = <n>, optimizer_index_caching
uted_transactions = <n>, gc_files_to_locks
out} = <n>) | 0 | off | no | false | <n>}) (ticks = = <0>, optimizer_index_cost_adj = <100>,
= ‘<f#>[-<f#>]=<n>[!<B>][r][each][: …]’ <<
<15>) (service = <serv>) (presentation = {ttc | optimizer_max_permutations = <80000>, op-
disables Cache Fusion, hi_shared_memory_ad-
oracle.aurora.server.{SGiopServer | GiopServ- timizer_mode = {first_rows_{1|10|100|1000}
dress = <0>, ifile = <file>, instance_groups
er} })’, drs_start = {true | false}, fal_client = | first_rows | all_rows | choose | rule},
= <gr> [, …], instance_name = <sid>,
<serv>, fal_server = <serv>, fast_start_io_target oracle_trace_enable = {true | false}, parallel_
instance_number = <n>, java_max_ses-
= <n> << deprecated, fast_start_mttr_target broadcast_enabled = {true | false}, parallel_in-
sionspace_size = <0>, java_pool_size =
= <0>, fast_start_parallel_rollback = {hi | stance_group = <gr>, parallel_min_percent =
<20k>, java_soft_sessionspace_limit = <0>,
lo | false}, fixed_date = <date>, global_con- <0>, partition_view_enabled = {true | false},
large_pool_size = <n>, local_listener = <serv>,
text_pool_size = <1m>, hs_autoregister = plsql_compiler_flags = {[debug | non_debug],
lock_name_space = <name>, lock_sga =
{true | false}, job_queue_processes = <0>, [interpreted | normal]}, plsql_v2_compat-
{true | false}, log_archive_format = <fmt>,
license_max_sessions = <0>, license_max_us- ibility = {true | false}, query_rewrite_enabled
log_archive_start = {true | false}, log_buffer =
ers = <0>, license_sessions_warning = <0>, = {true | false}, query_rewrite_integrity =
<n>, logmnr_max_persistent_sessions = <1>,
log_archive_dest = <dir>, log_archive_du- {stale_tolerated | trusted | enforced}, remote_
max_commit_propagation_delay = <700>,
plex_dest = <dir>, log_archive_max_processes dependencies_mode = {timestamp | signature},
max_dispatchers = <5>, max_enabled_roles =
= <1>, log_archive_trace = <0>, log_check- session_cached_cursors = <0>, sort_area_re-
<20>, max_shared_servers = <n>, o7_diction-
point_interval = <bl>, log_checkpoint_time- tained_size = <n>, sort_area_size = <65536>,
ary_accessibility = {true | false}, open_cursors
out = <sec>, log_checkpoints_to_alert = {true star_transformation_enabled = {temp_disable
= <50>, open_links = <4>, open_links_per_in-
| false}, parallel_adaptive_multi_user = {true | true | false}, statistics_level = {typical | basic |
stance = <4>, optimizer_features_enable
| false}, parallel_threads_per_cpu = <n>, all}, timed_os_statistics = <0>, timed_statistics
= <9.0.0>, oracle_trace_collection_name
pga_aggregate_target = <0>, plsql_native_c_ = {true | false}, tracefile_identifier = ‘<id>’,
= <name>, oracle_trace_collection_path =
compiler = <path>, plsql_native_library_dir undo_suppress_errors = {true | false}, work-
<dir>, oracle_trace_collection_size = <n>,
= <dir>, plsql_native_library_subdir_count = area_size_policy = {auto | manual}
oracle_trace_facility_name = {oracled, oraclee,
<0>, plsql_native_linker = <path>, plsql_na-
oraclesm, oraclec}, oracle_trace_facility_path =
tive_make_file_name = <path>, plsql_native_ Session Parameters Only
<dir>, os_authent_prefix = <OPS$>, os_roles
make_utility = <path>, resource_limit = {true constraint[s] = {immediate | deferred | default},
= {true, false}, parallel_automatic_tuning =
| false}, resource_manager_plan = <plan>, ser- create_stored_outlines = {true | false | ‘<cat>’}
{true | false}, parallel_execution_message_size
vice_names = <serv> [, …], shared_pool_size = [nooverride], current_schema = <schema>, er-
= <n>, parallel_max_servers = <n>, paral-
<16/64m>, shared_servers = <0/1>, standby_ ror_on_overlap_time = {true | false}, flagger =
lel_min_servers = <0>, pre_page_sga = {true
archive_dest = <path>, standby_file_manage- {entry | immediate | full | off }, instance = <n>,
| false}, processes = <n>, rdbms_server_dn =
ment = {manual | auto}, trace_enabled = {true isolation_level = {serializable | read commit-
<x.500>, read_only_open_delayed = {true |
| false}, transaction_auditing = {true | false}, ted}, plsql_debug = {true | false}, skip_unus-
false}, recovery_parallelism = <n>, remote_ar-
undo_retention = <900>, undo_tablespace = able_indexes = {true | false}, sql_trace = {true
chive_enable = {true | false}, remote_listener =
<ts>, user_dump_dest = <dir> | false}, time_zone = {‘<{+|-}hh:mi>’ | local |
<serv>, remote_login_passwordfile = {none |
shared | exclusive}, remote_os_authent = {true dbtimezone | ‘<tz_region>’}, use_{private |

www.larsditzel.de


Instance (cont.)

stored}_outlines = {true | false | ‘<cat>’} Debug events | tracefile_name
10015 (rollback), 10046 (process), 10049, | lkdebug
Hidden Initialization Parameters 10051, 10053, 10210, 10211, 10212, 10231, | nsdbx
10232, 10235, 10248 (dispatcher), 10249 | -G {<inst> | def | all}
_system_trig_enabled, _log_simultane-
(shared server + dispatcher), 10257 (pmon), 10262, | -R {<inst> | def | all}
ous_copies, _log_io_size
10289 (hex dump), 10297 (oid caching), 10325 | setinst {“<inst> [, …]” | all}
(control), 10408 (block keywords), 10520 (avoid | sgatofile <”path”>
Deprecated Initialization Parameters invalidations), 10619 (compatibility), 19027 (ctxx- | dmpcowsga <”path”>
mts_dispatchers, mts_servers path), 29700 (v$ges_convert% views), 30441 | mapcowsga <”path”>
%_area%_size <<for dedicated server cofigurations | hanganalyze [level]
oradebug | ffbegin
Obsolete Initialization Parameters { help [cmd] | ffderegister
job_queue_interval, db_block_max_dirty_tar- | setmypid | ffterminst
get, hash_multiblock_io_count = <n> | setospid <ospid> | ffresumeinst
| setorapid <orapid> [‘force’] | ffstatus
| dump <dump> <lev> [addr] | core
Events
| dumpsga [bytes] | ipc
{ alter system set event = | dumplist | unlimit
| alter session set events [=] } | event <evt> | procstat
‘<dbg_evt> trace name context | session_event <evt> | call <func> [<arg> , …] }
{forever, level <n> | off }’ | dumpvar {p | s | uga} <var> [lev]
alter session set events [=] | setvar {p | s | uga} <var> <val>
{ ‘immediate trace name | peek <addr> <len> [lev]
{ heapdump | blockdump | treedump | poke <addr> <len> <val>
| controlf | systemstate | buffers } | wakeup <orapid>
level <n>’ | suspend
| ‘<oerr> trace name errorstack level <n> | resume
[; name processstate level <n>]’ } | flush
| close_trace

Instance Startup/Shutdown Utilities
startup orapwd
[force] [restrict] [pfile=<par>] [ nomount | file=<file> password=<pwd> entries=<n>
[exclusive | parallel [retry] | shared [retry]] oradim
{ mount [<db>] | open –{new | edit | delete | startup | shutdown}
[read {only | write [recover]} | recover] –{sid <SID> | srvc <serv>} –newsid <SID>
[<db>] } ] –usrpwd <pwd> –intpwd <pwd>
shutdown –maxusers <n> –startmode {a | m}
[ normal | transactional [local] –shutmode {a | i | n}
| immediate | abort ] –{starttype | shuttype}
alter database [<db>] {srvc | inst | srvc, inst}
{ mount [ {standby | clone} database] –pfile <par> –timeout <n>
[exclusive | parallel] << obsolete tkprof <trc> <out>
| dismount [explain=<user>/<pwd>@<netserv>]
| open [read only | [read write] [table=<tab>] [print=<n>] [sys=no]
[resetlogs | noresetlogs] ] [insert=<file>] [record=<file>]
| close [normal | immediate] }; [aggregate=<n>] [sort=<opt>]
otrcfmt
Instance Modification oemctl
alter system {enable | disable} restricted { {start | stop | status | ping}
session; oms [<user>/<pwd>] | {start | stop}
alter system {quiesce restricted | unquiesce}; paging [<host> <name>] | {enable | dis-
able | dump | export | import}
alter system {suspend | resume};
eventhandler [<file>]
alter system kill session ‘<SID>,<Serial#>’
| {import | export} registry [<file>]
[immediate];
<user>/<pwd>@<repalias>
alter system disconnect session | configure rws }
‘<SID>,<Serial#>’
oemapp {console | dataguard}
[post_transaction | immediate];
vppcntl –start
alter system shutdown [immediate] ‘D<nnn>’;
vtm
alter system register;
alter system flush shared_pool;

www.larsditzel.de


Instance (cont.)

Database Locks (v$lock) CU cursor bind, DF data file, DL direct cache, RT redo thread, SC system commit
modes loader parallel index creation, DM data- number, SM smon, SN sequence number,
0 - none, 1 - null (NULL), 2 - row share base mount, DR distributed recovery, DX SQ sequence number enqueue, SS sort seg-
(SS), 3 - row exclusive (SX), 4 - share (S), distributed transaction, FS file set, HW ment, ST space transaction, SV sequence
5 - share row exclusive (SSX), 6 - exclu- space management operation, IN instance number value, TA generic enqueue, TS
sive (X) number, IR instance recovery, IS instance temporary segment (ID2=0) or new block
user types and names state, IV library cache invalidation, JQ allocation (ID2=1), TT temporary table,
TM dml enqueue, TX transaction job queue, KK redo thread kick, L[A-P] UN user name, US undo segment ddl,
enqueue, UL user-defined lock library cache lock, MM mount defini- WL being-written redo log, XA instance
tion, MR media recovery, N[A-Z] library registration attribute lock, XI instance
system types and names
cache pin, PF password file, PI/PS parallel registration lock
BL buffer hash table, CF control file trans-
operation, PR process startup, Q[A-Z] row
action, CI cross-instance call invocation,

Table Locks (TM)

SQL Statement Mode Acquired Additional Mode Allowed? Row Locks?
RS RX S SRX X
select none Y Y Y Y Y
select … for update RS Y* Y* Y* Y* N X
lock table … in row share mode RS Y Y Y Y N
insert RX Y Y N N N X
update RX Y* Y* N N N X
delete RX Y* Y* N N N X
lock table … in row exclusive mode RX Y Y N N N
lock table … in share mode S Y N Y N N
lock table … in share row exclusive mode SRX Y N N N N
lock table … in exclusive mode X N N N N N

RS = SS (subshare), RX = SX (subexclusive), SRX = SSX (share-subexclusive)
* waits occur for conflicting row locks of concurrent transactions

www.larsditzel.de


Database

Views & Tables Files
v$database, v$controlfile, v$controlfile_re- catalog.sql, catproc.sql, utlrp.sql, utlip.sql,
cord_section, v$deleted_object, utlirp.sql, utlconst.sql, utlincmpt.sql,
v$compatibility, v$compatseg, v$timezone_ utldst.sql, timezone.dat, timezlrg.dat,
names, dictionary, dict_columns, dba_catalog, catlg803.sql, u0703040.sql, r0703040.sql,
dba_objects, dba_object_size, dba_keepsizes, u080<n>0<n>0.sql, r08000<n>0.sql,
dba_analyze_objects, props$, database_prop- d080<n>0<n>.sql
erties, database_compatible_level
Tuning/Contention
Parameters phyrds, phywrts (v$filestat)
db_create_file_dest, db_create_online_log_
dest_<n>, undo_tablespace, cluster_database,
control_files, db_name, db_domain, db_files,
compatible, read_only_open_delayed

DB Creation [national character set create controlfile [‘<ctrl>‘] [reuse]
create database [<db>] {<UTF8> | <AL16UTF16>} ] set database <db> [datafile…] [logfile…]
[datafile ‘<file>’ [, …] size <n> [reuse] [set time_zone = … [[no]resetlogs];
[autoextend {on | off } [next <1xBS> { ‘<{+|-}hh:mi>’ create spfile [= ‘<spfile>’] from
maxsize {<n> | unlimited}] ]] | ‘<time_zone_region>’ } ] pfile [= ‘<pfile>’];
[logfile [group <n>] [set standby database create pfile [= ‘<pfile>’] from
(‘<log>’ [, …] ) size <n> [reuse] {protected | unprotected} ] spfile [= ‘<spfile>’];
[, [group <n>] [archivelog | noarchivelog] [exclusive]; alter database [<db>] backup controlfile to
(‘<log>’ [, …] ) size <n> [reuse] ] … ] { ‘<file>’ [reuse]
[default temporary tablespace <ts> DB Modification | trace [resetlogs | noresetlogs] };
[tempfile ‘<file>’] alter database [<db>] rename global_name alter database [<db>] create standby controlfile
[extent management local] to <db>; as ‘<file>’ [reuse];
[uniform [size <1> [k | m]] ]] alter database [<db>] default temporary alter database [<db>] set standby database
[undo tablespace <SYS_UNDOTBS> tablespace <ts>; {protected | unprotected};
[datafile ‘<file>’ alter system set undo_tablespace = <new_ts>; alter database [<db>]
[autoextend…] [, …] ]]
alter database [<db>] convert; commit to switchover to [physical]
[controlfile reuse]
alter database [<db>] reset compatibility; {primary | standby} [wait | nowait];
[maxdatafiles <n>] [maxinstances <n>]
alter database [<db>] [national] character set alter database [<db>] activate [physical]
[maxlogfiles <n>] [maxlogmembers <n>]
<new_char>; standby database [skip [standby logfile]];
[maxloghistory <n>]
[character set {<charset> alter database [<db>] set {dblow = <str> |
| <UTF8> | <UTFE> | <AL32UTF8>} ] dbhigh = <str> | dbmac {on | off } };

Data Guard CLI alter Other Utilities
dgmgrl [-silent] [-xml] [-debug] [-echo] { configuration set state = dbassist
connect <user>/<pwd>@<service> ‘[online | offline]’ dbca
| site ‘<site>’ set { state = ‘[online | offline]’
startup [restrict] [force] [pfile=<file>]
| auto pfile = ‘<pfile>’ [off ] }
[nomount | mount [<db>]
| resource ‘<res>’ [on site ‘<site>’] set
| open [read {only | write} ] ]
{ state = ‘<state>’
shutdown {normal | immediate | abort} | property ‘<prop>’ = ‘<val>’ };
show { configuration [verbose] [‘<prop>’] create
| site [verbose] ‘<site>’ [‘<prop>’] { configuration ‘<conf>’
| resource [verbose] ‘<res>’ [‘<prop>’] as primary site is ‘<prim>’
[on site ‘<site>’] | site ‘<site>’ }
| dependency tree resource is ‘<res>’ hostname is ‘<host>’
| log [alert] [latest] on site ‘<site>’ }; instance name is ‘<inst>’
enable { configuration | site ‘<site>’ | resource service name is ‘<serv>’
‘<res>’ [on site ‘<site>’] }; site is maintained as physical;
disable { configuration | site ‘<site>’ | resource remove { configuration ‘<conf>’ | site ‘<site>’ };
‘<res>’ [on site ‘<site>’] };

www.larsditzel.de


Database Utilities

Views & Tables Loads [[x]’<str>’] [and [x]’<str>’]
v$loadcstat, v$loadistat, v$loadpstat, sqlldr | terminated [by]
v$loadtstat, dba_exp_files, dba_exp_objects, userid = <user>/<pwd> data = <data> {whitespace | [x]’<str>’ | eof }
dba_exp_version, sys.incexp, sys.incfil, control = <ctrl> parfile = <par> log = [[optionally] enclosed…] }
sys.incvid <log> bad = <bad> discard = <discard> [trailing [nullcols] ]
discardmax = <n> skip = <n> load = [sorted indexes]
<n> errors = <n> rows = <n> bindsize [singlerow]
Files
= <65536> readsize = <65536> silent ( { <col> { <sqlfunc> | sysdate | recnum
catexp.sql, catexp7.sql, migrate.bsq | sequence [( { <n>[.<x>]
= ( {header | feedback | errors | discards
| partitions | all} [, …] ) direct = <n> | max | count } )] }
Export multithreading = <n> streamsize = <n> | <col> [filler]
exp columnarrayrows = <n> parallel = <n> [ position ( { <x> [ {: | -} <y>]
help = <n> userid = <user>/<pwd> parfile file = <file> skip_unusable_indexes = <n> | * [+<z>] } ) ]
= <par> file = <expdat.dmp> filesize skip_index_maintenance = <n> com- { char [(<n>)]
= <n> volsize = <n> log = <log> buf- mit_discontinued = <n> external_table | varchar [(<n>)]
fer = <n> silent = <n> recordlength = = {not_used | generate_only | execute} | varcharc
<n> direct = <n> rows = <y> indexes resumable = <n> resumable_name = <str> | date [“<fmt>”]
= <y> grants = <y> constraints = <y> resumable_timeout = <7200> datecache | time
triggers = <y> feedback = <0> statistics = <n> | timestamp
= {estimate | compute | none} record = bind array size = | time with time zone
(n rows) * ( SUM (fixed field lengths) + SUM(max. | timestamp with time zone
<y> compress = <y> consistent = <n> varying field lengths) + ( (number of varying length
object_consistent = <n> flashback_scn = fields) * (size of length indicator [2 or 3, system | interval year to month
<scn> flashback_time = <time> resumable dependent]) ) ) | interval day to second
= <n> resumable_name = <str> resum- | integer [external] [(<n>)]
able_timeout = <7200> template = <x> Controlfile | smallint
tablespaces = (<ts> [, …]) transport_ta- [ options ( | float [external]
blespace = <n> tts_full_check = <x> [, …] [bindsize = <n>] [columnsarrayrows = | double
point_in_time_recover = <n> <n>] [direct = {true | false}] [errors = <n>] | byteint
recovery_tablespaces = <ts> [, …] [load = <n>] [multithreading = {true | | zoned [external] (p [,<s>])
{ full = <n> | owner = <schema> false}] [parallel = {true | false}] [readsize | decimal [external] (p [,<s>])
| tables = (<tab>[:<part>] [, …] = <n>] [resumable] [resumable_name] | raw [(<n>)]
[query = <expr>] ) } [resumable_timeout] [rows = <n>] [silent | varraw
inctype = {complete | cumulative | incre- = ({feedback | errors | discards | all} [, …])] | long varraw
mental} << deprecated [skip = <n>] [skip_index_maintenance] | varrawc
Perform full exports as user System. [skip_unusable_indexes] [streamsize = | graphic [(<n>)]
buffer size =
<n>] ) ] | graphic external [(<n>)]
(n rows) * SUM(max. field length + size of length
indicator [2 or 3, system dependent]) | vargraphic [(<n>)] }
[recoverable | unrecoverable]
[terminated by
{load | continue_load} data
{“<str>” | whitespace} ]
Import [{infile | inddn} [‘<load.dat>’ | * ]
[ {nullif | defaultif } ({<col> | <pos>})
imp [“str [x]’<char>’”]
<op> { [x]‘<str>’ | blanks } [and…] ]
help = <n> userid = <user>/<pwd> parfile [“recsize <n> buffers <n>”]
[enclosed by ‘<chr>’ and ‘<chr>’]
= <par> file = <expdat.dmp> filesize = [badfile ‘<load.bad>’ | baddn]
[“<sql_stmt>(:<col>)”]
<n> volsize = <n> log = <log> buffer [{discardfile | discarddn} ‘<load.dsc>’]
[, <col> …] )
= <n> recordlength = <n> rows = <y> [{discards | discardmax} <n>]
[into table <tab> …]
grants = <y> indexes = <y> indexfile = [characterset <char>]
[begindata…]
<file> constraints = <y> commit = <n> [byteorder {big | little} [endian] ]
compile = <y> ignore = <n> inctype = [byteordermark {check | nocheck} ]
[length [semantics] Migration
{system | restore} feedback = <0> show
= <n> statistics = {always | none | safe | {byte | char | character} ] mig
recalculate} analyze = <y> recalculate_sta- [concatenate <n>] dbname = <db> new_dbname = <new>
tistics = <n> destroy = <n> skip_unus- [continueif pfile = <initfile> spool = <logfile> check_
able_indexes = <n> toid_novalidate = { [this | next] [preserve] only = <false> no_space_check = <false>
(<type> [, …] ) resumable = <n> resum- [(] <pos> multiplier = <15> nls_nchar = <char >
able_name = <str> resumable_timeout | last [preserve] [(] }
= <7200> streams_configuration = <y> <op> [x]’<str>’ [)] ]
streams_instatiation = <n> { full = <n> | into table <tab>
tables = (<tab>[:<part>] [, …])} fromuser [ ({partition | subpartition} <part>) ]
= <schema> [, …] touser = <schema> [, [skip <n>]
…] transport_tablespace = <n> datafiles = {insert | replace | truncate | append}
‘(<file> [, …] )‘ tablespaces = (<ts> [, …]) [options (file = <db_file>) ]
tts_owners = <owner> [, …] point_in_ [when ({<col> | <pos>})
time_recover = <false> <op> { [x]‘<str>’ | blanks } [and…] ]
Order: type defs – table defs – table data – indexes [fields
– constraints, views, procedures, triggers { enclosed [by]
– bitmap, functional, domain indexes

www.larsditzel.de


Tablespaces, Datafiles & Segments

Views & Tables fet$, seg$ relocate | fix}_bitmaps, tablespace_rebuild_
v$tablespace, v$datafile, v$datafile_copy, quotas, tablespace_fix_segment_states,
v$datafile_header, v$dbfile, v$offline_ Parameters tablespace_migrate_{from | to}_local,
range, v$tempfile, v$temp_extent_map, segment_{verify | corrupt | dump |
db_block_checking, db_block_checksum,
v$temp_extent_pool, v$temp_space_header, moveblocks}, segment_drop_corrupt, seg-
recovery_parallelism, fast_start_parallel_roll-
v$temp_ping, v$backup, v$recover_file, ment_number_{blocks | extents},
back, db_file_name_convert, log_check-
v$recovery_file_status, v$recovery_log, point_timeout, log_checkpoints_to_alert, DBMS_TTS
v$recovery_progress, v$recovery_status, db_writer_processes, db_file_simultane- transport_set_check, downgrade
v$recovery_transactions, v$instance_recovery, ous_waits, standby_file_management,
v$fast_start_servers, v$fast_start_trans- read_only_open_delayed Deprecated Features
actions, v$managed_standby, dba_ta- fast_start_io_target, log_checkpoint_interval
blespaces, dba_ts_quotas, dba_data_files, Packages
dba_temp_files, dba_segments, dba_extents,
DBMS_REPAIR Desupported Features
dba_free_space, dba_free_space_coalesced,
dba_free_space_coalesced_tmp[1-3], check_object, {skip | fix}_corrupt_blocks, db_block_max_dirty_target, db_file_simul-
ts_pitr_objects_to_be_dropped, ts_pitr_check, dump_orphan_keys, rebuild_freelists, taneous_writes, db_block_checkpoint_batch,
transport_set_violations, dba_dmt_free_space, admin_tables, segment_fix_status parallel_transaction_recovery
dba_dmt_used_extents, dba_lmt_free_space, DBMS_SPACE
dba_lmt_used_extents, pluggable_set_check, unused_space, free_blocks, space_usage
uni_pluggable_set_check, straddling_ts_ob- DBMS_SPACE_ADMIN
jects, ext_to_obj_view, ts$, file$, filext$, uet$, tablespace_verify, tablespace_{rebuild |

TS Creation alter tablespace <ts> { online | offline [ next <n> | timeout <n> | delay <n>
create tablespace <ts> [ normal | temporary | immediate | nodelay | expire <n> | cancel
[datafile ‘<file>’] << only optional if | for recover ] }; << deprecated [immediate] [nowait] | disconnect
DB_CREATE_FILE_DEST is set alter tablespace <ts> { read {write | only} [from session] [finish [nowait]] ]
[size <n>] [reuse] | permanent | temporary }; | [standby] tablespace ‘<ts>’ [, …]
[autoextend {off | on [next <n>] alter tablespace <ts> [minimum extent <n>] [until [consistent with] controlfile]
[maxsize {<n> | unlimited} ] } ] default storage (…); | [standby] datafile {‘<file>’ | <n>} [, …]
[,’<file>’… [autoextend…] ] alter tablespace <ts> coalesce; [until [consistent with] controlfile]
[minimum extent <n>] | logfile ‘<log>’ }
alter tablespace <ts> {begin | end} backup;
[blocksize <n> [k]] [test [allow <x> corruption] ]
alter database [<db>] datafile <n> [, …] [noparallel | parallel [<n>] ]
[default storage ( [initial <5xBS>] end backup;
[next <5xBS>] [pctincrease <50>] | continue [default] | cancel };
[minextents <1>] recover [automatic] [from ‘<log_path>’]
Datafiles { database [until { cancel | change <scn>
[maxextents {<n> | unlimited} ]
[freelists <1>] [freelist groups <1>] alter system checkpoint [global | local]; | time ‘<YYYY-MM-DD:HH24:MI:SS>’ }]
[buffer_pool {default | keep | recycle} ] )] alter system check datafiles [global | local]; [using backup controlfile]
[logging | nologging] alter database [<db>] datafile ‘<file>’ [, …] | [managed] standby database
[permanent | temporary] [online | offline] { resize <n> | autoextend… | online [timeout <n> | cancel [immediate] ]
[extent management | offline [drop] | end backup }; | [standby] tablespace ‘<ts>’ [, …]
{ dictionary | local alter database [<db>] tempfile ‘<file>’ [, …] [until [consistent with] controlfile]
[autoallocate | uniform [size <1m>]] }] { resize <n> | autoextend… | online | [standby] datafile {‘<file>’ | <n>} [, …]
[segment space management | offline | drop [including datafiles] }; [until [consistent with] controlfile]
{manual | auto} ]; alter database [<db>] rename file | logfile <log>
create undo tablespace <ts> ‘<file>’ [, …] to ‘<new_file>’ [, …]; | continue [default]
[datafile ‘<file>’… [autoextend…] ] << s.a. | cancel }
alter database [<db>] create datafile
[extent management local] [noparallel | parallel (degree {<n> | default}
‘<file>‘ [, …] [as {new | ‘<file>‘ [, …]} ];
[uniform [size <1m>]]; [instances <1> | default] )]
alter system dump datafile ‘<file>’
create temporary tablespace <ts> block min <x> block max <y>;
[tempfile ‘<file>’… [autoextend…] ] << s.a.
[extent management local] Recovery
[uniform [size <1m>]];
set autorecovery {on | off }
drop tablespace <ts>
[including contents [and datafiles] set logsource <dir>
[cascade constraints] ]; alter database [<db>] recover
[automatic] [from ‘<log_path>‘]
TS Modification { { [standby] database
[until { cancel | change <scn>
alter tablespace <ts> add {datafile | tempfile} | time ‘<YYYY-MM-DD:HH24:MI:SS>’ }]
‘<file>’ size <n> [reuse] [autoextend…]; [using backup controlfile]
alter tablespace <ts> rename datafile | managed standby database
‘<file>’ [, …] to ‘<new>’ [, …];

www.larsditzel.de


Tablespaces, Datafiles & Segments (cont.)

Utilities Row piece: Base64
dbv file=<file> overhead, no. of columns, cluster key ID, OOOOOO – FFF – BBBBBB – RRR
start=<n> end=<n> logfile=<log> rowids of chained row pieces, Restrict(6B):
blocksize=<2048> feedback=<0> col data (col length, col value, …) Block#{Xb} - Row#{Xb} - File#{Xb}
parfile=<fil> segment_id=<ts.fil.blck>
RowID Packages
Blocks Logical: DBMS_ROWID
v$type_size hex string of variable length (rowid_create, rowid_object, rowid_rela-
Block header: Extend(10B): tive_fno, rowid_block_number, ro-
static (61B), table directory, row direc- DataObj#{32b} - RelFile#{10b} - wid_row_number, rowid_to_absolute_fno,
tory (2B*rec), interested transaction list Block#{22b} - Row#{16b} rowid_to_extended, rowid_to_restricted)
(23B*tx)

Logfiles

Views & Tables (_allow_resetlogs_corruption Files
<< undocumented & unsupported)
v$log, v$logfile, v$thread, v$loghist, v$log_ dbmslm.sql, dbmslmd.sql, dbmslms.sql,
history, v$database, v$archive, v$archive_dest, dbmscdcp.sql, dbmscdcs.sql catcdc.sql,
Packages
v$archive_dest_status, v$archive_gap, initcdc.sql
v$standby_log, v$archived_log, v$archive_ DBMS_LOGMNR_D
processes, v$logmnr_dictionary, v$logmnr_pa- build
Tuning/Contention
rameters, v$logmnr_logs, v$logmnr_contents, DBMS_LOGMNR
v$system_event, v$sysstat
dba_source_tables, dba_source_tab_columns, add_logfile, start_logmnr, end_logmnr,
mine_value, column_present Redo latch:
dba_subscriptions, dba_subscribed_tables,
«redo allocation», «redo copy» (v$latch)
dba_subscribed_columns, change_sources, DBMS_LOGMNR_SESSION
«misses» / «gets» > 1% or
change_sets, change_tables {add | remove}_logfile, {create | attach |
«immediate_misses» / («immediate_gets» +
detach | destroy}_session, column_present,
«immediate_misses») > 1%
Parameters include_src_tbl, mine_value, {prepare |
-> decrease «log_small_entry_max_size»
release}_scn_range, set_dict_attr, set_ses-
db_create_online_log_dest_<1-5>, thread, -> increase «log_simultaneous_copies»
sion_params
log_buffer, log_archive_max_processes, (max. CPU * 2)
log_archive_start, log_archive_dest, log_ar- DBMS_[LOGMNR_]CDC_PUBLISH
chive_format, standby_archive_dest, log_ar- {create | alter | drop}_change_table, drop_
{subscription | subscriber_view}, purge Desupported Features
chive_duplex_dest, log_archive_dest_
DBMS_[LOGMNR_]CDC_SUBSCRIBE v$targetrba, log_archive_buffers, log_archive_
<1-10>, log_archive_dest_state_<1-10>,
get_subcription_handle, subscribe, buffer_size, log_block_checksum, log_simul-
remote_archive_enable, fal_client, fal_server,
{activate | drop}_subscription, {extend | taneous_copies, log_small_entry_max_size,
log_archive_trace, archive_lag_target,
purge}_window, {prepare | drop}_ sub- lgwr_io_slaves
log_archive_min_succeed_dest, log_file_
name_convert, arch_io_slaves, utl_file_dir, scriber_view
logmnr_max_persistent_sessions, _log_simul-
taneous_copies, _log_io_size

Archive Mode Logfiles alter database [<db>] drop
archive log { list | stop | {start | next | all | alter database [<db>] add [standby] logfile [standby] logfile member ‘<log>’;
<n>}} [thread <n>] [group <n>] (‘<log>’, …) alter database [<db>] clear
[to <dest>] << always applies to current instance size <n>; [unarchived] logfile {group <n> | ‘<log>’}
alter database [<db>] alter database [<db>] [unrecoverable datafile];
{archivelog | noarchivelog}; {enable [public] | disable} thread <n>; alter database add supplemental log data
alter system archive log [thread <n>] alter database [<db>] add [standby] logfile ( {primary key | unique index} [, …] )
{ start [to ‘<log_path>’] | stop member ‘<log>’ [reuse] to group <n>; columns;
| current << global log switch alter database [<db>] register [or replace] alter database drop supplemental log data;
| next | all | sequence <n> | group <n> [physical] logfile ‘<log>’ [, …]; alter table add supplemental log group <grp>
| change <n> | logfile ‘<file>’ }; alter database [<db>] rename file (<col> [, …] ) [always];
alter system switch logfile; ‘<log>’ [, …] to ‘<new_log>’ [, …]; alter table drop supplemental log group <grp>;
<< applies only to current instance alter database [<db>] drop
[standby] logfile group <n>;

www.larsditzel.de

oracle 9i cheat sheet

Recommended

Recommended

More Related Content

What's hot

What's hot (20)

Viewers also liked

Viewers also liked (14)

Similar to oracle 9i cheat sheet

Similar to oracle 9i cheat sheet (20)

More from Piyush Mittal

More from Piyush Mittal (20)

Recently uploaded

Recently uploaded (20)

oracle 9i cheat sheet