Oracle8iDBA必备卡

recover [automatic] [from '']

{ database [until { cancel | change

| time '' } ]

[using backup controlfile]

| [managed] standby database [timeout | cancel [immediate] ]

| [standby] tablespace '' [,...] [until [consistent with] controlfile]

| [standby] datafile {'' | } [,...]

[until [consistent with] controlfile]

| logfile | continue [default] | cancel}

[ { noparallel | parallel (degree { | default}

[instances <1> | default] ) } ]

dbv file= start= end= logfile= blocksize=<2048>

feedback=<0>

BLOCKS

v$type_size

Header:static(61B), row directory(2B*rec), transaction headers

(23B*TX) [Cluster: table directory]

ROWID

Logical:hex string of variable length

Extend(10B):DataObj#{32b} - RelFile#{10b} - Block#{22b} - Row#{16b}

Base64OOOOOO – FFF – BBBBBB – RRR

Restrict(6B):Block#{Xb} - Row#{Xb} - RelFile#{Xb}

Package DBMS_ROWID

(rowid_create, rowid_object, rowid_relative_fno, rowid_block_number,

rowid_row_number, rowid_to_absolute_fno, rowid_to_extended,

rowid_to_restricted)

LOGFILES

v$log, v$logfile, v$thread, v$loghist, v$log_history, v$database, v$archive,

v$archive_dest, v$archived_log, v$archive_processes, v$logmnr_dictionary,

v$logmnr_parameters, v$logmnr_logs, v$logmnr_contents, v$targetrba

'thread', 'log_buffer', 'log_archive_max_processes', 'log_archive_start', 'log_archive_dest',

'standby_archive_dest', 'log_archive_dest_[1-5]' = '{ location = | service = }

[optional | mandatory] [reopen [=<300>] ]', 'log_archive_dest_state_[1-5]' = {enable | defer},

'log_archive_duplex_dest', 'log_archive_min_succeed_dest', 'log_archive_format',

'log_file_name_convert', 'arch_io_slaves', 'utl_file_dir',

‘_allow_resetlogs_corruption’ (undocumented & unsupported)

[obsolete: 'log_archive_buffers', 'log_archive_buffer_size', 'log_block_checksum',

'log_simultaneous_copies', 'log_small_entry_max_size', 'lgwr_io_slaves']

Packages DBMS_LOGMNR_D (build), DBMS_LOGMNR (add_logfile, start_logmnr,

end_logmnr)

dbmslogmnrd.sql

Tuning/Contention

v$system_event, v$sysstat

Redo latch: "redo allocation", "redo copy" (v$latch) misses / gets > 1% or

immediate_misses / (immediate_gets + immediate_misses) > 1%

-> decrease 'log_small_entry_max_size'

-> increase 'log_simultaneous_copies' (max. CPU * 2)

archive log { list | stop | {start | next | all | } } [to ]

alter database [] {archivelog | noarchivelog};

alter system archive log [thread ]

{ start [to ''] | stop | current | next | all

| sequence | group | change | logfile '' };

alter system switch logfile;

alter database [] add logfile

[thread ] [group ] (‘’,...) size ;

alter database [] {enable [public] | disable} thread ;

alter database [] add logfile member ‘’ [reuse] to group ;

alter database [] rename file ‘’ [,...] to ‘’ [,...];

alter database [] drop logfile group ;

alter database [] drop logfile member ‘’;

alter database [] clear [unarchived] logfile { group | ‘’ }

[unrecoverable datafile];

TABLES & CONSTRAINTS & TRIGGERS

dba_tables, dba_all_tables, dba_object_tables, dba_tab_comments,

dba_tab_columns, col, dba_tab_col_statistics, dba_associations,

dba_ustats, dba_col_comments, dba_updatable_columns,

dba_unused_col_tabs, dba_tab_modifications, dba_nested_tables,

dba_part_tables, dba_tab_partitions, dba_tab_subpartitions,

dba_part_col_statistics, dba_part_key_columns, dba_partial_drop_tabs,

dba_subpart_col_statistics, dba_subpart_key_columns, dba_constraints,

dba_cons_columns, dba_triggers, dba_trigger_cols, dba_internal_triggers,

dba_tab_histograms, dba_part_histograms, dba_subpart_histograms,

hist_head$

[obsolete: dba_histograms, 'cache_size_threshhold' ]

Packages DBMS_UTILITY (analyze_database, analyze_schema, analyze_part_object)

DBMS_SPACE (unused_space, free_blocks) [dbmsutil.sql, utlvalid.sql]

DBMS_STATS (gather_{database | schema | table | index}_stats, {export |

import}_schema_stats)

utlexcpt.sql, utlexcpt1.sql, dbmsstdx.sql

Tuning/Contention

pctfree = UPD/AVG, pctused = 1 – pctfree – AVG/nBLK

scans: "table scans%" (v$sysstat)

-> adjust 'db_file_multiblock_read_count'

row migr.: table_name, head_rowid (chained_rows <- utlchain.sql, utlchain1.sql) or

"table fetch continued row" (v$sysstat)

-> increase pctfree

-> recreate table (create as, delete from, insert into select, commit, drop)

freelists: "segment header" (v$waitstat), "buffer busy waits" (v$system_event)

-> alter pctfree/pctused, inittrans, or

-> increase freelist/freelist groups

(v$session_wait -> dba_extents -> dba_segments -> recreate object)

full & partial partition-wise joins

create [global temporary] table (max. 1000 col)

[of ]

[object identifier is {primary key | system generated

[oidindex ( [tablespace ...] [storage (...) ] ) ] } ]

( [ {default | := } ]

[with rowid] [scope is ]

[constraint ]

[ { [not] null

| primary key [using index...]

| unique [using index...]

| check ()

| references [ () ] [on delete {cascade | set null} ] }

[ [not] deferrable [initially {immediate | deferred} ] ]

[ {disable | enable} [validate | novalidate] [exceptions into ] ]

[,... [constraint ]...] [,...]

[, constraint ...]

[ref () with rowid] [scope for () is ] )

[tablespace ] [organization {heap | index} ] [storage (...) ]

[pctfree <10>] [pctused <40>] [initrans <1>] [maxtrans ]

[logging | nologging] [recoverable | unrecoverable] <- obsolete

[cache | nocache] [monitoring | nomonitoring]

[noparallel | parallel [] ]

[ partition by range ( [,...] )

[ subpartition by hash ( [,...] )

[subpartitions [store in ( [,...] ) ] ]

( partition values less than ( { [,...] | maxvalue} )

[storage (...) ] [tablespace ]

[ ( subpartition [tablespace ]

[, subpartition...] ) ]

[, partition... [ ( subpartition...) ] ] ) ]

[ partition by hash ( [,...] )

{ ( partition [tablespace ] [, partition...] )

| partitions store in ( [,...] ) } ]

[ {disable | enable} row movement]

[ lob () store as

( [tablespace ] [storage (...) ]

[ {disable | enable} storage in row]

[pctversion <10>] [chunk ]

[cache | nocache {logging | nologging} ]

[ index ([tablespace ] [storage (...) ] ) ] ) ] << deprecated [ varray store as lob [] ([tablespace ] ) ] [nested table store as [ ( () [storage (...) ] ) ] [return as {locator | value} ]

[on commit {delete | preserve} rows];

create table [logging | nologging] ... as select...;

alter table modify (...);

alter table add (...);

alter table set unused { ( [,...] ) | column }

[cascade constraints] [invalidate];

alter table drop { ( [,...] ) | column }

[cascade constraints] [invalidate] [checkpoint <512>]; alter table drop {unused columns | columns continue}

[checkpoint <512>];

drop table [cascade constraints];

rename to ;

alter table move [tablespace ] [storage (...) ]

[logging | nologging] [noparallel | parallel [] ]; truncate table [ [preserve | purge] snapshot log]

[ {drop | reuse} storage];

alter table [storage (...) ] [noparallel | parallel [] ] ...

[ {nominimize | minimize} records_per_block];

alter table { allocate extent

( [size ] [datafile ''] [instance ] );

| deallocate unused [keep ] };

lock table in {share [row exclusive] | exclusive} mode [nowait]; alter table {enable | disable} table lock;

comment on {table | column .} is ‘’;

alter table add partition

values less than ( [,...] ) [tablespace ];

alter table add partition [ [tablespace ] ]; alter table drop partition [,...];

alter table coalesce partition;

alter table truncate {partition | subpartition}

[ {drop | reuse} storage];

alter table rename {partition | subpartition} to ; alter table modify partition

[storage (...) ] [allocate extent...] [logging | nologging] ... [ [rebuild] unusable local indexes]

[ add subpartition [ [tablespace ] ]

| coalesce subpartition];

alter table modify subpartition

[storage (...) ] [allocate extent...] [logging | nologging] ... [ [rebuild] unusable local indexes];

alter table modify default attributes

[for partition ] [storage (...) ] ...;

alter table move {partition | subpartition}

tablespace [parallel [] ] [logging | nologging];

alter table split partition at ()

into (partition , partition [,...] );

alter table merge partitions ,

[into partition ];

alter table exchange {partition | subpartition}

with table [including indexes] [ {with | without} validation]; alter table add

( [constraint ]

{ primary key ( [,...] ) [using index...]

| unique ( [,...] ) [using index...]

| foreign key ( [,...] ) references ( [,...] ) [on delete {cascade | set null} ]

| check () }

[ [not] deferrable [initially {immediate | deferred} ] ]

[ {disable | enable} [validate | novalidate] [exceptions into ] ] ); alter table {disable | enable} [validate | novalidate]

{ constraint | primary key | unique ( [,...] ) } [using index...] [exceptions into ] [cascade];

alter table modify constraint ... [rely | norely];

alter table drop

{ constraint | primary key | unique ( [,...] ) } [cascade]; set constraint[s] { [,...] | all} {immediate | deferred};

alter table {enable | disable} all triggers;

create [or replace] trigger { before | after | instead of }

{ {delete | insert | update [of [,...] ] } [or...]

on { | [nested table of] } | { {create | alter | drop} [or...]

| {shutdown | startup | servererror | logon | logoff} [or...] }

on {schema | database} }

[referencing

{old [as] | new [as] | parent [as] } [,...] ]

[for each row] [when () ]

{ begin ; end;

| call ... ;}

alter trigger { enable | disable | compile [debug] };

drop trigger ;

analyze table [partition() ]

{ compute statistics

| estimate statistics [sample <1064> {rows | percent} ] }

[for table] [for all [local] indexes]

[for all [indexed] columns [size <75>] ]

[for columns [size <75>] ];

analyze table delete statistics;

analyze table list chained rows [into ];

analyze table validate

{ structure [into ] [cascade]

| ref update [set dangling to null] };

associate statistics with

{ columns [.] [,...]

| functions [,...] | packages [,...] | types [,...]

| indexes [,...] | indextypes [,...] }

[using ] [default cost (, , ) ]

[default selectivity ];

disassociate statistics from

{ columns [.] [,...]

| functions [,...] | packages [,...] | types [,...]

| indexes [,...] | indextypes [,...] } [force];

VIEWS & SYNONYMS & SEQUENCES

dba_views, dba_synonyms, dba_sequences

create [or replace] [force | no force] view [ ( [,...] ) ]

[of with object oid [default | (,...) ] ]

as [with { read only | check option [constraint ] } ];

alter view compile;

drop view ;

create [public] synonym for ;

drop [public] synonym ;

create sequence [start with <1>] [increment by <1>]

[maxvalue <1027> | nomaxvalue] [minvalue <1> | nominvalue]

[cycle | nocycle] [nocache | cache <20>] [order | noorder];

alter sequence ...;

drop sequence ;

CLUSTERS

dba_clusters, dba_clu_columns, all_tab_columns,

dba_cluster_hash_expressions

create cluster ( [,...] )

[index | [single table] hashkeys [hash is ] ]

[size <1xBS>] [tablespace ] [storage (...) ]

[pctfree <10>] [pctused <40>] [initrans ] [maxtrans <255>];

create index on cluster

[storage (...) ] [tablespace ] [pctfree ]

[initrans ] [maxtrans ];

create table

(... [constraint ...] )

cluster ( [,...] );

alter cluster ...;

truncate cluster [ {drop | reuse} storage];

drop cluster [including tables [cascade constraints] ];

analyze cluster ...;

INDEX-ORGANIZED TABLES

all_tables (iot_type, iot_name), all_indexes

create table (... primary key...)

organization index

[tablespace ] [pctfree ] [initrans ] [maxtrans ]

[storage (...) ] [pctthreshold <50> [including ] ]

[compress [] | nocompress]

[ overflow [tablespace ] [pctfree <10>]

[initrans <1>] [maxtrans <255>] [storage (...) ]

[allocate...] [deallocate...] [logging | nologging] ]

[ partition by range ( [,...] )

( partition values less than ( [,...] )

[storage (...) ] [tablespace ] [overflow tablespace ...]

[, partition...] ) ];

alter table ... [overflow...];

alter table add overflow ... [ (partition ...) ];

alter table move [online] [compress [] | nocompress]

[tablespace ] [overflow...] ... [noparallel | parallel [] ];

alter table modify default attributes [for partition ]

[storage (...) ] [pctthreshold <50> [including ] ]

[compress [] | nocompress] [overflow tablespace ...];

analyze table compute statistics;

INDEXES

dba_indexes, dba_indextypes, dba_indextype_operators, dba_ind_columns,

dba_ind_expressions, index_stats, dba_part_indexes, dba_ind_partitions,

dba_ind_subpartitions, dba_part_col_statistics, dba_subpart_col_statistics,

index_histogram

'create_bitmap_area_size', 'bitmap_merge_area_size'

Package DBMS_PCLXUTIL (build_part_index)

Tuning

(index_stats) del_lf_rows_len / lf_rows_len > 20% -> rebuild index

create [unique | bitmap] index

on ( [] [asc | desc] [,...] )

[tablespace { | default } ] [storage (...) ]

[pctfree <10>] [initrans ] [maxtrans <255>]

[logging | nologging] [nosort] [reverse] [online]

[noparallel | parallel [] ] [nocompress | compress [] ]

[ local

[ { ( partition [] [storage (...) ] [tablespace ] on range p. tab

[logging | nologging] [, partition...] )

| { store in ( { [,...] | default} ) on hash p. tab

| ( partition [] [tablespace ] [, partition...] ) }

| store in ( { [,...] | default} ) on comp. p. tab

[ ( partition [] [storage (...) ] [tablespace ]

[logging | nologging]

[ { store in ( { [,...] | default} )

| ( subpartition [] [tablespace ]

[, subpartition...] ) } ]

[, partition...] ) ] } ] ]

[ global partition by range ()

( partition values less than ( { [,...] | maxvalue} )

[storage (...) ] [tablespace ] [logging | nologging]

[, partition...] ) ]

[indextype is [parameters ('') ] ];

drop index ;

alter index {enable | disable};

alter index unusable;

alter index rename to ;

alter index drop partition [,...];

alter index rename {partition | subpartition} to ;

alter index modify {partition | subpartition}

[storage (...) ] ... [logging | nologging] [unusable]

[rebuild unusable local indexes];

alter index modify default attributes [for partition ]

[storage (...) ] [pctfree ] ...;

alter index rebuild {partition | subpartition}

[tablespace ] [parallel [] ];

alter index split partition at values less than ()

into ( partition , partition [,...] );

alter index [storage (...) ] [initrans ] [maxtrans ]

[nocompress | compress [] ];

alter index allocate extent

( [size ] [datafile ''] [instance ] );

alter index [datafile ''] deallocate unused [keep ];

alter index rebuild

[ {partition | subpartition} ] [tablespace ] [storage (...) ]

[pctfree <10>] [initrans ] [maxtrans <255>]

[logging | nologging] [noparallel | parallel [] ]

[nocompress | compress ] [compute statistics] [online]

[noreverse | reverse] [parameters ('') ];

alter index coalesce;

analyze index ...;

analyze index validate structure;

ROLLBACK SEGMENTS

v$rollname, v$rollstat, v$transaction, v$transaction_enqueue,

v$global_transaction, dba_rollback_segs, dba_pending_transactions

'rollback_segments', 'transactions', 'transactions_per_rollback_segment'

Package DBMS_TRANSACTION (use_rollback_segment)

Tuning/Contention

RBS Header: "undo segment tx slot" (v$system_event) > 0 or

(v$rollstat) sum(waits) / sum(gets) > 5% or

-> add RBS

RBS Segment: "%undo%" (v$waitstat) / "consistent gets" (v$sysstat) (count/value) > 1%

-> add RBS

create [public] rollback segment [tablespace ]

[storage ( [initial <5xBS>] [next <5xBS>] [optimal ]

[minextents <1>] [maxextents { | unlimited} ] ) ];

drop rollback segment ;

alter rollback segment {online | offline};

alter rollback segment storage (...);

alter rollback segment shrink [to ];

set transaction use rollback segment ;

TEMPORARY SEGMENTS

v$sort_segment, v$sort_usage, dba_segments

'sort_area_size', 'sort_area_retained_size', 'sort_multiblock_read_count'

[obsolete: 'sort_direct_writes', 'sort_write_buffers', 'sort_write_buffer_size' ]

Tuning

"sorts (disk)", "sorts (memory)", sorts (rows)" (v$sysstat)

disk.value / mem.value > 5%

-> increase 'sort_area_size' (+ decrease 'sort_area_retained_size')

USERS & PRIVILEGES & RESOURCES & POLICIES

v$enabledprivs, v$resource, v$resource_limit, v$pwfile_users, v$context,

v$rsrc_plan, v$rsrc_plan_cpu_mth, v$rsrc_consumer_group,

v$rsrc_consumer_group_cpu_mth, v$parallel_degree_limit_mth,

v$max_active_sess_target_mth, dba_users, dba_roles, dba_profiles,

dba_ustats, dba_ts_quotas, dba_sys_privs, dba_tab_privs, dba_col_privs,

dba_role_privs, role_sys_privs, role_tab_privs, role_role_privs,

user_tab_privs_made, user_tab_privs_recd, user_col_privs_made,

user_col_privs_recd, user_password_limits, user_resource_limits,

session_privs, session_roles, dba_context, dba_policies, proxy_users,

resource_cost, dba_rsrc_plans, dba_rsrc_plan_directives,

dba_rsrc_consumer_groups, dba_rsrc_consumer_group_privs,

dba_rsrc_manager_system_privs

'o7_dictionary_accessibility', 'remote_os_authent', 'os_roles', 'remote_os_roles',

'max_enabled_roles', 'resource_limit', 'resource_manager_plan', 'ent_domain_name'

Environment: $ORA_ENCRYPT_LOGIN

Packages DBMS_RESOURCE_MANAGER (set_initial_consumer_group, {create |

submit | clear | validate}_pending_area, {create | update | delete}_{plan |

plan_directive | consumer_group}, delete_plan_cascade,

switch_consumer_group_for_{sess | user} ),

DBMS_RESOURCE_MANAGER_PRIVS ( {grant | revoke}_system_privilege,

{grant | revoke}_switch_consumer_group), DBMS_SESSION

(switch_current_consumer_group), DBMS_RLS ( {add | drop | enable |

refresh}_policy)

create user

identified { by | externally | globally as '' }

[default tablespace ] [temporary tablespace ]

[quota { | unlimited} on [quota...] ]

[password expire] [account {lock | unlock} ]

[profile { | default} ];

alter user ...;

drop user [cascade];

create role [ [not] identified {by | externally | globally} ];

alter role ...;

drop role ;

alter user default role { [,...] | all [except [,...] ] | none};

set role { [identified by ] [, [identified by ]...]

| all [except [,...] ] | none };

grant { [,...] | [,...] } to

{ [,...] | [,...] | public } [with admin option];

revoke { | } from { | | public };

grant { [ ( [,...] ) ] [,...] | all } on