ARC<n>, CJQ0, J<nnn>, CKPT, DBW<n>, DIAG, DMON, EMN0, LCK<n>*, LGWR, LMD0*, LMON*, LMS<n>*, LNSV, LSP0, MRP0, NSV0, PMON, QMN<n>, RECO, RLAP, RSM0, SMON, RFS<n>BSP<n>*, SNP<n> « obsolete * RAC processes Failure of LGWR (Err 470), CKPT (470), DBW<n> (471), ARC<n> (473), SMON (474) or RECO (476) lead to termination of instance by PMON. Failure of PMON leads to termination of instance by DBW<n> (Err 472). Failed SNP<n> processes are restarted by PMON.

D<nnn>, S<nnn>, P<nnn>

v$fixed_table, v$fixed_view_definition, v$indexed_fixed_column, v$instance, v$sga, v$sgastat, v$pgastat, v$session, v$process, v$bgprocess, v$version, product_component_version, v$license, v$option, v$access, v$timer, v$parameter, v$parameter2, v$spparameter, v$system_parameter, v$system_parameter2, v$obsolete_parameter, v$sql, v$sqlarea, v$sqltext, v$sqltext_with_newlines, v$sql_cursor, v$sql_bind_data, v$sql_bind_metadata, v$sql_shared_memory, v$sql_plan, v$sql_workarea, v$sql_workarea_active, v$librarycache, v$rowcache, v$rowcache_parent, v$rowcache_subordinate, v$open_cursor, v$object_dependency, v$db_object_cache, v$shared_pool_reserved, v$bh, x$bh, v$cache, v$subcache, v$buffer_pool, v$buffer_pool_statistics, v$db_cache_advice, v$statistics_level, v$filestat, v$tempstat, v$sysstat, v$sesstat, v$mystat, v$statname, v$waitstat, v$latch, v$latchname, v$latchholder, v$latch_parent, v$latch_children, v$event_name, v$system_event, v$session_event, v$session_wait, v$sess_io, v$segment_statistics, v$segstat, v$segstat_name, v$circuit, v$queue, v$shared_server, v$shared_server_monitor, v$dispatcher, v$dispatcher_rate, v$reqdist, v$queue, v$lock, v$enqueue_lock, v$enqueue_stat, v$locked_object, v$global_blocked_locks, dba_locks, dba_lock, dba_lock_internal, v$session_connect_info, v$session_longops, v$system_cursor_cache, v$session_cursor_cache, v$session_object_cache, v$bsp, v$px_session, v$px_sesstat, v$px_process, v$px_process_sysstat, v$pq_sesstat, v$pq_slave, v$pq_sysstat, v$pq_tqstat, v$execution, v$mls_parameters, deptree, session_context

spfile, ifile, instance_name, service_names, db_block_size, sga_max_size, db_cache_size, db_keep_cache_size, db_recycle_cache_size, db_<n>k_cache_size, db_cache_advice, shared_pool_size, log_buffer, large_pool_size, java_pool_size, shared_pool_reserved_size, pre_page_sga, sessions, processes, user_dump_dest, background_dump_dest, max_dump_file_size, local_listener, remote_listener, mts_service, circuits, dispatchers, max_dispatchers, shared_servers, max_shared_servers, shared_server_sessions, dbwr_io_slaves, remote_os_authent, os_authent_prefix, dml_locks, enqueue_resources, parallel_automatic_tuning, parallel_min_servers, parallel_max_servers, parallel_min_percent, parallel_adaptive_multi_user, parallel_threads_per_cpu, parallel_execution_message_size, parallel_broadcast_enabled, oracle_trace_enable, oracle_trace_collection_{name | path | size}, oracle_trace_facility_{name | path}, java_soft_sessionspace_limit, java_max_sessionspace_size, lock_sga, shared_memory_address, hi_shared_memory_address, object_cache_optimal_size, object_cache_max_size_percent, serial_reuse, session_max_open_files, timed_os_statistics, cursor_sharing, drs_start

DBMS_SYSTEM : set_sql_trace_in_session
DBMS_SUPPORT : mysid, {start | stop}_trace, {start | stop}_trace_in_session
DBMS_SESSION : set_sql_trace, {set | clear}_identifier, {set | list | clear}_context, set_role, set_nls, is_role_enabled, is_session_alive, unique_session_id, close_database_link, reset_package, modify_package_state, switch_current_consumer_group, free_unused_user_memory, set_close_cached_open_cursors DBMS_SHARED_POOL : keep, unkeep, sizes
DBMS_APPLICATION_INFO : set_module, set_action, set_client_info, read_module, read_client_info

dbmspool.sql, dbmssupp.sql, catparr.sql, utldtree.sql

Statistics classes :1 User, 2 Redo, 4 Enqueue, 8 Cache, 16 OS, 32 RAC, 64 SQL, 128 Debug
Buffer cache : «Cache Hit Ratio» (v$sysstat) or per pool (v$buffer_pool_statistics)1 – («physical reads» / («db block gets» + «consistent gets»)) < 90–95% → increase «db_block_buffers» or «buffer_pool_keep», «buffer_pool_recycle»
Shared pool : «Shar. Cursors» (v$librarycache) gethitratio for SQL AREA < 99%
Library cache : sum(reloads) / sum(pins) > 1% (v$librarycache)
Dict. cache : sum(getmisses) / sum(gets) > 15% (v$rowcache) → increase «shared_pool_size»
LRU latch : «cache buffers lru chain» (v$latch) misses / gets > 1% → increase «db_block_lru_latches» (max. CPU * 2 or BUFFERS / 50)
Redo buffer : «redo%retries» (v$sysstat)
PGA : «%ga memory%» (v$sysstat), «sorts%» (v$sysstat), sorts (v$sqlarea), «workarea%» (v$sysstat, v$sesstat), v$pgastat, v$sql_workarea, v$sql_workarea_active, pga_%_mem (v$process)

v$mts : db_block_buffers, buffer_pool_keep, buffer_pool_recycle, mts_circuits, mts_dispatchers, mts_max_dispatchers, mts_servers, mts_sessions utlbstat.sql, utlestat.sql

v$recent_bucket, v$current_bucket, db_block_lru_latches, use_indirect_data_buffers, db_block_lru_extended_statistics, db_block_lru_statistics, lock_sga_areas, shared_pool_reserved_min_alloc, parallel_server_idle_time, parallel_transaction_resource_timeout, parallel_min_message_pool, mts_rate_log_size, mts_rate_scale, mts_max_serversInstance

show parameter[s] <string> alter system set <param> [=] <value>[comment ‘<text>’] [deferred] [scope = {memory | spfile | both} ][sid = { ‘<sid>’ | ‘*’ } ]; alter system reset <param>[scope = {memory | spfile | both} ][sid = { ‘<sid>’ | ‘*’ } ];

active_instance_count = <n>, audit_file_dest = <dir>, audit_trail = {none | false | db | true | os}, background_core_dump = {full | partial}, bitmap_merge_area_size = <1m>, blank_trimming = {true | false}, buffer_pool_{keep | recycle} = {<n> | (buffers: <n>, lru_latches: <n>} << deprecated, circuits = <n>, cluster_database = {true | false}, cluster_database_instances = <n>, cluster_interconnects = <ip>[:<ip>…], commit_point_strength = <n>, compatible = <x.x.x>, control_files = (“<file>” [, …]), cpu_count = <n>, create_bitmap_area_size = <8m>, cursor_space_for_time = {true | false}, db_block_buffers = <n> << deprecated, db_block_size = <2048>, db_domain = <str>, {db | log}_file_name_convert = (‘prim’, ‘stdby’ [, …]), db_files = <200>, db_name = <str>, db_writer_processes = <1>, dblink_encrypt_login = {true | false}, dbwr_io_slaves = <0>, disk_asynch_io = {true | false}, distributed_transactions = <n>, gc_files_to_locks = ‘<f#>[-<f#>]=<n>[!<B>][r][each][: …]’ << disables Cache Fusion, hi_shared_memory_address = <0>, ifile = <file>, instance_groups = <gr> [, …], instance_name = <sid>, instance_number = <n>, java_max_sessionspace_size = <0>, java_pool_size = <20k>, java_soft_sessionspace_limit = <0>, large_pool_size = <n>, local_listener = <serv>, lock_name_space = <name>, lock_sga = {true | false}, log_archive_format = <fmt>, log_archive_start = {true | false}, log_buffer = <n>, logmnr_max_persistent_sessions = <1>, max_commit_propagation_delay = <700>, max_dispatchers = <5>, max_enabled_roles = <20>, max_shared_servers = <n>, o7_dictionary_accessibility = {true | false}, open_cursors = <50>, open_links = <4>, open_links_per_instance = <4>, optimizer_features_enable = <9.0.0>, oracle_trace_collection_name = <name>, oracle_trace_collection_path = <dir>, oracle_trace_collection_size = <n>, oracle_trace_facility_name = {oracled, oraclee, oraclesm, oraclec}, oracle_trace_facility_path = <dir>, os_authent_prefix = <OPS$>, os_roles = {true, false}, parallel_automatic_tuning = {true | false}, parallel_execution_message_size = <n>, parallel_max_servers = <n>, parallel_min_servers = <0>, pre_page_sga = {true | false}, processes = <n>, rdbms_server_dn = <x.500>, read_only_open_delayed = {true | false}, recovery_parallelism = <n>, remote_archive_enable = {true | false}, remote_listener = <serv>, remote_login_passwordfile = {none | shared | exclusive}, remote_os_authent = {true | false}, remote_os_roles = {true | false}, replication_dependency_tracking = {true | false}, rollback_segments = (<rbs> [, …]), row_locking = {always | default | intent}, serial_reuse = {disable | select | sml | plsql | all}, session_max_open_files = <10>, sessions = <(1.1*proc)+5>, sga_max_size = <n>, shadow_core_dump = {partial | full}, shared_memory_address = <0>, shared_pool_reserved_size = <5%SP>, shared_server_sessions = <n>, spfile = <file>, sql92_security = {true | false}, sql_trace = {true | false}, tape_asynch_io = {true | false}, thread = <n>, transactions_per_rollback_segment = <5>, undo_management = {manual | auto}, util_file_dir = <dir>

aq_tm_processes = <n>, archive_lag_target = <n>, background_dump_dest = ‘<dir>’, backup_tape_io_slaves = {true | false}, control_file_record_keep_time = <7>, core_dump_dest = ‘<dir>’, db_{2|4|8|16|32}k_cache_size = <0>, db_block_checking = {true | false}, db_block_checksum = {true | false}, db_cache_advice = {on | ready | off}, db_cache_size = <48m>, db_{keep | recycle}_cache_size = <0m>, dispatchers = ‘{ (protocol = <prot>) | (description = (address =…) ) | (address = (protocol = <prot>) (host = <node>) (port = <port>) )} (connections = <n>) (dispatchers = <1>) (index = <n>) (listener = <list>) ( {pool | multiplex} = {1 | on | yes | true | both | ({in | out} = <n>) | 0 | off | no | false | <n>}) (ticks = <15>) (service = <serv>) (presentation = {ttc | oracle.aurora.server.{SGiopServer | GiopServer} })’, drs_start = {true | false}, fal_client = <serv>, fal_server = <serv>, fast_start_io_target = <n> « deprecated, fast_start_mttr_target = <0>, fast_start_parallel_rollback = {hi | lo | false}, fixed_date = <date>, global_context_pool_size = <1m>, hs_autoregister = {true | false}, job_queue_processes = <0>, license_max_sessions = <0>, license_max_users = <0>, license_sessions_warning = <0>, log_archive_dest = <dir>, log_archive_duplex_dest = <dir>, log_archive_max_processes = <1>, log_archive_trace = <0>, log_checkpoint_interval = <bl>, log_checkpoint_timeout = <sec>, log_checkpoints_to_alert = {true | false}, parallel_adaptive_multi_user = {true | false}, parallel_threads_per_cpu = <n>, pga_aggregate_target = <0>, plsql_native_c_compiler = <path>, plsql_native_library_dir = <dir>, plsql_native_library_subdir_count = <0>, plsql_native_linker = <path>, plsql_native_make_file_name = <path>, plsql_native_make_utility = <path>, resource_limit = {true | false}, resource_manager_plan = <plan>, service_names = <serv> [, …], shared_pool_size = <16/64m>, shared_servers = <0/1>, standby_archive_dest = <path>, standby_file_management = {manual | auto}, trace_enabled = {true | false}, transaction_auditing = {true | false}, undo_retention = <900>, undo_tablespace = <ts>, user_dump_dest = <dir>

alter session set <param> [=] <value>; cursor_sharing = {similar | exact | force}, db_block_checking, db_create_file_dest = ‘<dir>’, db_create_online_log_dest_<1-5> = ‘<dir>’, db_file_multiblock_read_count = <8>, global_names = {true | false}, hash_area_size = <n>, hash_join_enabled = {true | false}, log_archive_dest_<1-10> = {location = <path> | service = <serv>} [optional | mandatory] [[no]reopen [=<300>]] [arch | lgwr] [synch | async = <n>] [[no]affirm] [[no]delay [= <30>]] [[no]dependency] [[no]alternate [= <dest>]] [[no]max_failure [= <n>] [[no]quota_size [= <n>] [[no]quota_used] [[no]register [= <loc>]], log_archive_dest_state_<1-10> = {enable | defer | alternate}, log_archive_min_succeed_dest = <1>, max_dump_file_size = {<n> | unlimited}, nls_calendar = ‘<cal>’, nls_comp = {binary | ansi}, nls_currency = <curr>, nls_date_format = ‘<fmt>’, nls_date_language = <lang>, nls_dual_currency = <curr>, nls_iso_currency = <terr>, nls_language = <lang>, nls_length_semantics = {byte | char}, nls_nchar_conv_excp = {true | false}, nls_numeric_characters = <sep>, nls_sort = {binary | <ling>}, nls_territory = <terr>, nls_time_format = ‘<fmt>’, nls_timestamp_format = ‘<fmt>’, nls_timestamp_tz_format = ‘<fmt>’, nls_time_tz_format = ‘<fmt>’, object_cache_max_size_percent = <10>, object_cache_optimal_size = <n>, optimizer_index_caching = <0>, optimizer_index_cost_adj = <100>, optimizer_max_permutations = <80000>, optimizer_mode = {first_rows_{1|10|100|1000} | first_rows | all_rows | choose | rule}, oracle_trace_enable = {true | false}, parallel_broadcast_enabled = {true | false}, parallel_instance_group = <gr>, parallel_min_percent = <0>, partition_view_enabled = {true | false}, plsql_compiler_flags = {[debug | non_debug], [interpreted | normal]}, plsql_v2_compatibility = {true | false}, query_rewrite_enabled = {true | false}, query_rewrite_integrity = {stale_tolerated | trusted | enforced}, remote_dependencies_mode = {timestamp | signature}, session_cached_cursors = <0>, sort_area_retained_size = <n>, sort_area_size = <65536>, star_transformation_enabled = {temp_disable | true | false}, statistics_level = {typical | basic | all}, timed_os_statistics = <0>, timed_statistics = {true | false}, tracefile_identifier = ‘<id>’, undo_suppress_errors = {true | false}, workarea_size_policy = {auto | manual}

constraint[s] = {immediate | deferred | default}, create_stored_outlines = {true | false | ‘<cat>’} [nooverride], current_schema = <schema>, error_on_overlap_time = {true | false}, flagger = {entry | immediate | full | off}, instance = <n>, isolation_level = {serializable | read committed}, plsql_debug = {true | false}, skip_unusable_indexes = {true | false}, sql_trace = {true | false}, time_zone = {‘<{+|-}hh:mi>’ | local | dbtimezone | ‘<tz_region>’}, use_{private | stored}_outlines = {true | false | ‘<cat>’}

_system_trig_enabled, _log_simultaneous_copies, _log_io_size

mts_dispatchers, mts_servers%_area%_size «for dedicated server cofigurations

job_queue_interval, db_block_max_dirty_target, hash_multiblock_io_count = <n>

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

startup : [force] [restrict] [pfile=<par>] [ nomount | [exclusive | parallel [retry] | shared [retry]]{ mount [<db>] | open [read {only | write [recover]} | recover] [<db>] } ]
shutdown : [ normal | transactional [local] | immediate | abort ]
alter database : [<db>]{ mount [ {standby | clone} database] [exclusive | parallel] « obsolete| dismount| open [read only | [read write] [resetlogs | noresetlogs] ]| close [normal | immediate] };

alter system {enable | disable} restricted session;
alter system {quiesce restricted | unquiesce};
alter system {suspend | resume};
alter system kill session ‘<SID>,<Serial#>’ [immediate];
alter system disconnect session ‘<SID>,<Serial#>’[post_transaction | immediate];
alter system shutdown [immediate] ‘D<nnn>’;
alter system register;
alter system flush shared_pool;

orapwd : file=<file> password=<pwd> entries=<n>
oradim : –{new | edit | delete | startup | shutdown}–{sid <SID> | srvc <serv>} –newsid <SID>–usrpwd <pwd> –intpwd <pwd> –maxusers <n> –startmode {a | m} –shutmode {a | i | n} –{starttype | shuttype} {srvc | inst | srvc, inst}–pfile <par> –timeout <n> tkprof : <trc> <out> [explain=<user>/<pwd>@<netserv>] [table=<tab>] [print=<n>] [sys=no] [insert=<file>] [record=<file>] [aggregate=<n>] [sort=<opt>] otrcfmt oemctl : { {start | stop | status | ping} oms [<user>/<pwd>] | {start | stop} paging [<host> <name>] | {enable | disable | dump | export | import} eventhandler [<file>]| {import | export} registry [<file>] <user>/<pwd>@<repalias> | configure rws } oemapp {console | dataguard}
vppcntl –start
vtm

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

¹⁾)))))))))))))))))))))

v$database, v$controlfile, v$controlfile_record_section, v$deleted_object, v$compatibility, v$compatseg, v$timezone_names, dictionary, dict_columns, dba_catalog, dba_objects, dba_object_size, dba_keepsizes, dba_analyze_objects, props$, database_properties, database_compatible_level

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

catalog.sql, catproc.sql, utlrp.sql, utlip.sql, utlirp.sql, utlconst.sql, utlincmpt.sql, utldst.sql, timezone.dat, timezlrg.dat, catlg803.sql, u0703040.sql, r0703040.sql, u080<n>0<n>0.sql, r08000<n>0.sql, d080<n>0<n>.sql

phyrds, phywrts (v$filestat)

create database [<db>] [datafile ‘<file>’ [, …] size <n> [reuse][autoextend {on | off} [next <1xBS> maxsize {<n> | unlimited}] ]][logfile [group <n>] (‘<log>’ [, …] ) size <n> [reuse] [, [group <n>] (‘<log>’ [, …] ) size <n> [reuse] ] … ][default temporary tablespace <ts> [tempfile ‘<file>’] [extent management local] [uniform [size <1> [k | m]] ]][undo tablespace <SYS_UNDOTBS> [datafile ‘<file>’ [autoextend…] [, …] ]][controlfile reuse] [maxdatafiles <n>] [maxinstances <n>] [maxlogfiles <n>] [maxlogmembers <n>] [maxloghistory <n>] [character set {<charset> | <UTF8> | <UTFE> | <AL32UTF8>} ] [national character set {<UTF8> | <AL16UTF16>} ] [set time_zone = { ‘<{+|-}hh:mi>’ | ‘<time_zone_region>’ } ][set standby database {protected | unprotected} ] [archivelog | noarchivelog] [exclusive];

alter database [<db>] rename global_name to <db>;
alter database [<db>] default temporary tablespace <ts>;
alter system set undo_tablespace = <new_ts>;
alter database [<db>] convert;
alter database [<db>] reset compatibility;
alter database [<db>] [national] character set <new_char>;
alter database [<db>] set {dblow = <str> | dbhigh = <str> | dbmac {on | off} };
create controlfile [‘<ctrl>‘] [reuse] set database <db> [datafile…] [logfile…] … trace [resetlogs | noresetlogs] };\\ alter database [<db>] create standby controlfile as ‘<file>’ [reuse];\\ alter database [<db>] set standby database {protected | unprotected};\\ alter database [<db>] commit to switchover to [physical] {primary | standby} [wait | nowait];\\ alter database [<db>] activate [physical] standby database [skip [standby logfile;

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

dbassist
dbca

cast ( { <expr> | (<subquery>) | multiset (<subquery>) } as <type> )