SVRMGRL COMMANDS
archive log list|stop|start|next|all
connect user/pwd@host:instance
connect internal
connect user/pwd as sysoper | as sysdba
describe table|view|procedure
disconnect
execute pl/sqlblockname
exit
host o/scommand
monitor monitorname (see previous section)
print variablename
recover database| tablespace| datafile (see manual)
remark text
set arraysize| autorecovery| charwidth| compatibility| cycle| datewidth| echo| fetchrows| flagger| history| instance| labwidth| lines| logsource| longwidth| maxdata| numwidth| retries| serveroutput| spool| stoponerror| term| termout| timing (see manual)
show arraysize| autorecovery| charwidth| compatibility| cycle| datewidth| echo| fetchrows| flagger| history| instance| labwidth| lines| logsource| longwidth| maxdata| numwidth| retries| serveroutput| spool| stoponerror| term| termout| timing| errors| logsource| parameters(see below)| sga| var| variables
shutdown abort| immediate| normal
spool filename
startup (see manual for more options)
startup nomount| mount| open
startup force| restrict| pfile=pfname
variable name type [variable city varchar2
V$TABLES
v$access, v$archive, v$backup, v$bgprocess, v$bh, v$cache, v$cache_lock, v$circuit, v$compatibility, v$compatseg, v$controlfile, v$database, v$datafile, v$db_object_cache, v$db_pipes, v$dbfile, v$dblink, v$dispatcher, v$enabledprivs, v$event_name, v$false_ping, v$filestat, v$fixed_table, V$fixed_view_definition, v$indexed_fixed_column, v$instance, v$latch, v$latchholder, v$latchname, v$librarycache, v$license, v$loadcstat, v$loadtstat, v$lock_actiity, v$lock_element, v$locks_with_collisions, v$log, v$log_history, v$logfile, v$loghist, v$mls_parameters, v$mts, v$mystat, v$nls_parameters, v$nls_valid_values, v$object_dependency, v$open_cursor, v$option, v$parameter, v$ping, v$process, v$pwfile_users, v$queue, v$recover_file, v$recovery_log, v$reqdist, v$resource, v$rollname, v$rollstat, v$rollcache, v$secondary, v$sess_io, v$session, v$session_cursor_cache, v$session_event, v$session_wait, v$sesstat, v$sga, v$sgastat, v$shared_server, v$sql, v$sqlarea, v$sqltext, v$statname, v$syslabel, v$sysstat, v$system_cursor_cache, v$system_event, v$thread, v$timer, v$transaction, v$type_size, v$version, v$waitstat
POPULAR DBA VIEWS
Storage Information Views --------
dba_free_space tablespace_name, file_id, block_id, bytes, blocks
dba_segments owner, tablespace_name, bytes, blocks, extents, initial_extent, next_extent, min_extents, max_extents, pct_increase, segment_name, segment_type
dba_tablespaces tablespace_name, initial_extent, next_extent, min_extents, max_extents, pct_increase, status
dba_rollback_segs segment_name, owner, tablespace_name, segment_id, file_id, block_id, next_extent, min_extents, max_extents, pct_increase, status
dba_objects owner, object_name, object_id, created, last_ddl_time, timestamp, status
dba_object_size name, type, source_size, parsed_size, code_size, error_size
dba_extents owner, segment_name, segment_type, tablespace_name, extent_id, file_id, block_id, bytes, blocks
Operating System Views --------
dba_data_files file_name, file_id, tablespace_name, bytes, blocks, status
dba_exp_files exp_version, exp_type, file_name, user_name, timestamp
Privileges Views
dba_col_privs grantee, owner, table_name, column_name, grantor, privilege, grantable
dba_profiles profile, resource_name, limit
dba_roles role, password_required
dba_role_privs grantee, granted_role, admin_option, default_role
dba_sys_privs grantee, privilege, admin_option
dba_tab_privs grantee, owner, table_name, grantor, privilege, grantable
Index Views ---------
dba_indexes owner, status, index_name, table_owner, table_name, table_type, uniqueness, tablespace_name, ini_trans, max_trans, initial_extent, next_extent, min_extents, max_extents, pct_increase, pct_free .......
dba_ind_columns index_owner, index_name, table_owner, table_name, column_name, column_position, column_length
Table and Column Views --------
dba_tables owner, table_name, tablespace_name, cluster_name, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extents, max_extents, pct_increase, pct_free, backed_up, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
dba_catalog owner, table_name, table_type
dba_tab_columns owner, table_name, column_name, data_type, data_length, data_precision, data_scale, nullable, column_id, default_length, data_default, num_distinct, low_value, high_value
dba_views owner, view_name, text_length, text
Lock Views ----------
dba_blockers session_id
dba_ddl_locks session_id, owner, name, type, mode_held, mode_requested
dba_dml_locks session_id, owner, name, mode_held, mode_requested.
dba_waiters waiting_session, holding_session, type, mode_held, mode_requested, lock_id1, lock_id2
dba_locks session_id, type, mode_held, mode_requested, lock_id1, lock_id2
Constraint Views ---------
dba_constraints owner, constraint_name, constraint_type, table_name, search_condition, r_owner, r_constraint_name, delete_rule, status
dba_cons_columns owner, constraint_name, table_name, column_name, position
MISC Views ---------
dba_triggers owner, trigger_name, trigger_type, triggering_event, table_owner, table_name, referencing_names, when_clause, status, description, trigger_body
dba_trigger_cols trigger_owner, trigger_name, table_owner, table_name, column_name, column_list, column_usage
dba_db_links owner, db_link, username, password, host, created
dba_sequences sequence_owner, sequence_name, min_value, max_value, increment_by, cycle_flag, order_flag, cache_size, last_number
dba_synonyms owner, synonym_name, table_owner, table_name, db_link
dba_users username, user_id, password, default_tablespace, temporary_tablespace, created, profile
ORACLE, SQL*DBA, SQL*LOADER and SQL*PLUS are registered trademarks of ORACLE Corporation.
INITIALIZATION PARAMETERS LIST
background_dump_dest=/dsk01/home/oracle/admin/prod/bdump
checkpoint_process=true|false
control_files=(/dsk02/ORACLE/prod/control01.dbf)
db_block_buffers=99999
db_block_size=1024|2048|4096|8192
db_domain=csa.com
db_files=999
db_file_multiblock_read_count=32
db_name=PROD
enqueue_resources=9999
log_archive_buffer_size=9999 (o/s blocks)
log_archive_buffers=9999 (o/s blocks)
log_archive_dest=/dsk01/ORACLE/prod/arch
log_archive_format='log%s_%t.arc'
log_archive_start=true|false
log_buffer=65536
log_checkpoint_interval=9999
log_checkpoint_timeout=0
log_files=255
max_dump_file_size=500 (blocks)
max_enabled_roles=20
max_rollback_segments=30
mts_dispatchers="tcp,1"
mts_listener_address="(address=(protocol=tcp) (host=achilles) (port=1525))"
mts_max_dispatchers=5
mts_max_servers=20
mts_servers=99
open_cursors=50
processes=200
rollback_segments=(rollback_seg_1, rollback_seg_2. ....)
row_cache_cursors=10
row_locking=always|intent
sequence_cache_entries=10
shared_pool_size=3670016 (3.5 M)
small_table_threshold=4
sort_area_size=1048576
time_statistics=fales|true
user_dump_dest=/dsk01/home/oracle/admin/prod/udump
POPULAR INSTANCE STATISTICS
0 cumulative logons - since last warmstart
1 current logons - current users
2 cumulative opened cursors - since warmstart
3 current open cursors - current sql cursors
7 recursive calls - high value indicates too small dict cache
9 session logical reads - total # of reads (logical/physical)
22 enqueue timeouts - lock timedout, s/b small #
23 enqueue waits - # times waited for a lock
25 enqueue requests - # of locks requested
26 enqueue conversions - # times lock type changed
27 enqueue releases - # of locks released
37 db block gets - # requests for current copy of block
38 consistent gets - this + db block gets = # logical reads
39 physical reads - reads that read from disk
41 write requests - # multiblock writes performed
49 free buffer requested - # of free buffers needed
52 DBWR timeouts - # of times dbwr was idle. Big is good.
53 DBWR make free requests - times needed clean buffers
54 DBWR free buffers found - # clean buffers found in scan
55 DBWR lru scans - times lru scanned
57 DBWR buffers scanned - # lru scanned for dirty buffers
67 redo entries - # redo entries created
68 redo size - bytes of generated redo entries
70 redo buffer allocation retries - indicates redo problem
72 redo wastage - filler added to redos, high value is ok
77 redo log space requests - requests to write a redo buffer
88 table scans (short tables) - interesting....
89 table scans (long tables) - minimize in application
90 table scan rows gotten - minimize in application
92 table fetch by rowid - indexed fetches
99 sorts(disk) - # sorts done using disk, try to minimize
100 sorts (memory) - # sorts done in memory
102 sorts (rows) - total # of rows sorted cumulative
IMPORT/EXPORT/SQL*LOADER
IMP username/password parfile=parfile.imp
EXP username/password log=expdat.log parfile=parfile.exp
username/password - can put in parfile
userid=sys - name of user whose object are to be moved
file=expdat.dmp - name of import/export data file
show=N - on imp allows pre-listing of contents in expdat file
Ignore=N - on imp reports if object already exists
grants=Y - import/export grants?
Indexes=Y - import/export indexes?
rows=Y - import/export rows or only dictionary objects
full=N - full database export?
fromuser=rob - on import, indicate which user's objects
touser=john - on import, indicate new owner's user name
tables=(x,y) - import/export this list of tables only
commit=N - on import commit after each array inserted?
log=expdat.log - name of log file to record actions
destroy=N - on import, allow removes data files if 'Y'
compress=Y - compress data on exports?
consistent=Y - on export, maintain read-consistent view?
constraints=Y - on export, export constraints too?
owner=rob - on export, whose objects to export
record=Y - on export record incr or cumul export in db tables?
statistics=estimate - on export specify import optimizer stats
SQLLDR userid=scott/tiger, control=xxx.ctl, log=xxx.log, data=xxx.dat, errors=999, load=2000, discard=xxx.dis, discardmax=5, direct=true, parallel=true parfile=xxx.par
archive log list|stop|start|next|all
connect user/pwd@host:instance
connect internal
connect user/pwd as sysoper | as sysdba
describe table|view|procedure
disconnect
execute pl/sqlblockname
exit
host o/scommand
monitor monitorname (see previous section)
print variablename
recover database| tablespace| datafile (see manual)
remark text
set arraysize| autorecovery| charwidth| compatibility| cycle| datewidth| echo| fetchrows| flagger| history| instance| labwidth| lines| logsource| longwidth| maxdata| numwidth| retries| serveroutput| spool| stoponerror| term| termout| timing (see manual)
show arraysize| autorecovery| charwidth| compatibility| cycle| datewidth| echo| fetchrows| flagger| history| instance| labwidth| lines| logsource| longwidth| maxdata| numwidth| retries| serveroutput| spool| stoponerror| term| termout| timing| errors| logsource| parameters(see below)| sga| var| variables
shutdown abort| immediate| normal
spool filename
startup (see manual for more options)
startup nomount| mount| open
startup force| restrict| pfile=pfname
variable name type [variable city varchar2
V$TABLES
v$access, v$archive, v$backup, v$bgprocess, v$bh, v$cache, v$cache_lock, v$circuit, v$compatibility, v$compatseg, v$controlfile, v$database, v$datafile, v$db_object_cache, v$db_pipes, v$dbfile, v$dblink, v$dispatcher, v$enabledprivs, v$event_name, v$false_ping, v$filestat, v$fixed_table, V$fixed_view_definition, v$indexed_fixed_column, v$instance, v$latch, v$latchholder, v$latchname, v$librarycache, v$license, v$loadcstat, v$loadtstat, v$lock_actiity, v$lock_element, v$locks_with_collisions, v$log, v$log_history, v$logfile, v$loghist, v$mls_parameters, v$mts, v$mystat, v$nls_parameters, v$nls_valid_values, v$object_dependency, v$open_cursor, v$option, v$parameter, v$ping, v$process, v$pwfile_users, v$queue, v$recover_file, v$recovery_log, v$reqdist, v$resource, v$rollname, v$rollstat, v$rollcache, v$secondary, v$sess_io, v$session, v$session_cursor_cache, v$session_event, v$session_wait, v$sesstat, v$sga, v$sgastat, v$shared_server, v$sql, v$sqlarea, v$sqltext, v$statname, v$syslabel, v$sysstat, v$system_cursor_cache, v$system_event, v$thread, v$timer, v$transaction, v$type_size, v$version, v$waitstat
POPULAR DBA VIEWS
Storage Information Views --------
dba_free_space tablespace_name, file_id, block_id, bytes, blocks
dba_segments owner, tablespace_name, bytes, blocks, extents, initial_extent, next_extent, min_extents, max_extents, pct_increase, segment_name, segment_type
dba_tablespaces tablespace_name, initial_extent, next_extent, min_extents, max_extents, pct_increase, status
dba_rollback_segs segment_name, owner, tablespace_name, segment_id, file_id, block_id, next_extent, min_extents, max_extents, pct_increase, status
dba_objects owner, object_name, object_id, created, last_ddl_time, timestamp, status
dba_object_size name, type, source_size, parsed_size, code_size, error_size
dba_extents owner, segment_name, segment_type, tablespace_name, extent_id, file_id, block_id, bytes, blocks
Operating System Views --------
dba_data_files file_name, file_id, tablespace_name, bytes, blocks, status
dba_exp_files exp_version, exp_type, file_name, user_name, timestamp
Privileges Views
dba_col_privs grantee, owner, table_name, column_name, grantor, privilege, grantable
dba_profiles profile, resource_name, limit
dba_roles role, password_required
dba_role_privs grantee, granted_role, admin_option, default_role
dba_sys_privs grantee, privilege, admin_option
dba_tab_privs grantee, owner, table_name, grantor, privilege, grantable
Index Views ---------
dba_indexes owner, status, index_name, table_owner, table_name, table_type, uniqueness, tablespace_name, ini_trans, max_trans, initial_extent, next_extent, min_extents, max_extents, pct_increase, pct_free .......
dba_ind_columns index_owner, index_name, table_owner, table_name, column_name, column_position, column_length
Table and Column Views --------
dba_tables owner, table_name, tablespace_name, cluster_name, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extents, max_extents, pct_increase, pct_free, backed_up, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
dba_catalog owner, table_name, table_type
dba_tab_columns owner, table_name, column_name, data_type, data_length, data_precision, data_scale, nullable, column_id, default_length, data_default, num_distinct, low_value, high_value
dba_views owner, view_name, text_length, text
Lock Views ----------
dba_blockers session_id
dba_ddl_locks session_id, owner, name, type, mode_held, mode_requested
dba_dml_locks session_id, owner, name, mode_held, mode_requested.
dba_waiters waiting_session, holding_session, type, mode_held, mode_requested, lock_id1, lock_id2
dba_locks session_id, type, mode_held, mode_requested, lock_id1, lock_id2
Constraint Views ---------
dba_constraints owner, constraint_name, constraint_type, table_name, search_condition, r_owner, r_constraint_name, delete_rule, status
dba_cons_columns owner, constraint_name, table_name, column_name, position
MISC Views ---------
dba_triggers owner, trigger_name, trigger_type, triggering_event, table_owner, table_name, referencing_names, when_clause, status, description, trigger_body
dba_trigger_cols trigger_owner, trigger_name, table_owner, table_name, column_name, column_list, column_usage
dba_db_links owner, db_link, username, password, host, created
dba_sequences sequence_owner, sequence_name, min_value, max_value, increment_by, cycle_flag, order_flag, cache_size, last_number
dba_synonyms owner, synonym_name, table_owner, table_name, db_link
dba_users username, user_id, password, default_tablespace, temporary_tablespace, created, profile
ORACLE, SQL*DBA, SQL*LOADER and SQL*PLUS are registered trademarks of ORACLE Corporation.
INITIALIZATION PARAMETERS LIST
background_dump_dest=/dsk01/home/oracle/admin/prod/bdump
checkpoint_process=true|false
control_files=(/dsk02/ORACLE/prod/control01.dbf)
db_block_buffers=99999
db_block_size=1024|2048|4096|8192
db_domain=csa.com
db_files=999
db_file_multiblock_read_count=32
db_name=PROD
enqueue_resources=9999
log_archive_buffer_size=9999 (o/s blocks)
log_archive_buffers=9999 (o/s blocks)
log_archive_dest=/dsk01/ORACLE/prod/arch
log_archive_format='log%s_%t.arc'
log_archive_start=true|false
log_buffer=65536
log_checkpoint_interval=9999
log_checkpoint_timeout=0
log_files=255
max_dump_file_size=500 (blocks)
max_enabled_roles=20
max_rollback_segments=30
mts_dispatchers="tcp,1"
mts_listener_address="(address=(protocol=tcp) (host=achilles) (port=1525))"
mts_max_dispatchers=5
mts_max_servers=20
mts_servers=99
open_cursors=50
processes=200
rollback_segments=(rollback_seg_1, rollback_seg_2. ....)
row_cache_cursors=10
row_locking=always|intent
sequence_cache_entries=10
shared_pool_size=3670016 (3.5 M)
small_table_threshold=4
sort_area_size=1048576
time_statistics=fales|true
user_dump_dest=/dsk01/home/oracle/admin/prod/udump
POPULAR INSTANCE STATISTICS
0 cumulative logons - since last warmstart
1 current logons - current users
2 cumulative opened cursors - since warmstart
3 current open cursors - current sql cursors
7 recursive calls - high value indicates too small dict cache
9 session logical reads - total # of reads (logical/physical)
22 enqueue timeouts - lock timedout, s/b small #
23 enqueue waits - # times waited for a lock
25 enqueue requests - # of locks requested
26 enqueue conversions - # times lock type changed
27 enqueue releases - # of locks released
37 db block gets - # requests for current copy of block
38 consistent gets - this + db block gets = # logical reads
39 physical reads - reads that read from disk
41 write requests - # multiblock writes performed
49 free buffer requested - # of free buffers needed
52 DBWR timeouts - # of times dbwr was idle. Big is good.
53 DBWR make free requests - times needed clean buffers
54 DBWR free buffers found - # clean buffers found in scan
55 DBWR lru scans - times lru scanned
57 DBWR buffers scanned - # lru scanned for dirty buffers
67 redo entries - # redo entries created
68 redo size - bytes of generated redo entries
70 redo buffer allocation retries - indicates redo problem
72 redo wastage - filler added to redos, high value is ok
77 redo log space requests - requests to write a redo buffer
88 table scans (short tables) - interesting....
89 table scans (long tables) - minimize in application
90 table scan rows gotten - minimize in application
92 table fetch by rowid - indexed fetches
99 sorts(disk) - # sorts done using disk, try to minimize
100 sorts (memory) - # sorts done in memory
102 sorts (rows) - total # of rows sorted cumulative
IMPORT/EXPORT/SQL*LOADER
IMP username/password parfile=parfile.imp
EXP username/password log=expdat.log parfile=parfile.exp
username/password - can put in parfile
userid=sys - name of user whose object are to be moved
file=expdat.dmp - name of import/export data file
show=N - on imp allows pre-listing of contents in expdat file
Ignore=N - on imp reports if object already exists
grants=Y - import/export grants?
Indexes=Y - import/export indexes?
rows=Y - import/export rows or only dictionary objects
full=N - full database export?
fromuser=rob - on import, indicate which user's objects
touser=john - on import, indicate new owner's user name
tables=(x,y) - import/export this list of tables only
commit=N - on import commit after each array inserted?
log=expdat.log - name of log file to record actions
destroy=N - on import, allow removes data files if 'Y'
compress=Y - compress data on exports?
consistent=Y - on export, maintain read-consistent view?
constraints=Y - on export, export constraints too?
owner=rob - on export, whose objects to export
record=Y - on export record incr or cumul export in db tables?
statistics=estimate - on export specify import optimizer stats
SQLLDR userid=scott/tiger, control=xxx.ctl, log=xxx.log, data=xxx.dat, errors=999, load=2000, discard=xxx.dis, discardmax=5, direct=true, parallel=true parfile=xxx.par
No comments:
Post a Comment