oracle常用sql管理.txt - 墨天轮文档

oracle常用sql管理.txt

今天星期八

12页

7次

2023-12-21

5墨值下载

一.数据库基本状态和信息

-----1 数据库信息

select * from gv$version;

select dbid,name,created,log_mode from gv$database;

select version,instance_name,startup_time,status from gv$instance;

---数据库运行时间

select to_char(startup_time, 'YYYY-MM-DD HH24:MI:SS') 启动时间,

TRUNC(sysdate - (startup_time)) || '天 ' ||

TRUNC(24 *

((sysdate - startup_time) - TRUNC(sysdate - startup_time))) ||

'小时 ' || MOD(TRUNC(1440 * ((SYSDATE - startup_time) -

TRUNC(sysdate - startup_time))),

60) || '分 ' ||

MOD(TRUNC(86400 *

((SYSDATE - STARTUP_TIME) - TRUNC(SYSDATE - startup_time))),

60) || '秒' 运行时间

from gv$instance;

二．数据库参数信息

---常见参数：

---v$parameter ---session

---v$system_parameter ---system

select name, value, display_value, isdefault

from v$system_parameter

where name in ('audit_trail',

'audit_sys_operations',

'cluster_database_instances',

'cpu_count',

'cursor_sharing',

'db_name',

'db_recovery_file_dest_size',

'deferred_segment_creation',

'disk_asynch_io',

'event',

'enable_ddl_logging',

'filesystemio_options',

'instance_name',

'instance_number',

'job_queue_processes',

'log_archive_dest_1',

'log_archive_format',

'memory_max_target',

'memory_target',

'nls_language',

'optimizer_dynamic_sampling',

'optimizer_index_cost_adj',

'processes',

'parallel_force_local',

'parallel_max_servers',

'pga_aggregate_target',

'query_rewrite_enabled',

'sec_case_sensitive_logon',

'sessions',

'sga_max_size',

'sga_target',

'utl_file_dir',

'undo_management',

'undo_retention',

'undo_tablespace',

'large_pool_size',

'resource_limit',

'resource_manager_plan',

'max_dump_file_size',

'control_file_record_keep_time',

'result_cache_max_size',

'sec_case_sensitive_logon',

'local_listener')

order by 1;

---常见隐含参数

SELECT x.ksppinm as name,

y.ksppstvl as value,

y.ksppstdf as isdefault,

x.ksppdesc describ

FROM SYS.x$ksppi x, SYS.x$ksppcv y

WHERE x.inst_id = USERENV('Instance')

AND y.inst_id = USERENV('Instance')

AND x.indx = y.indx

AND x.ksppinm in ('_allow_resetlogs_corruption',

'_b_tree_bitmap_plans',

'_corrupted_rollback_segments',

'_datafile_write_errors_crash_instance',

'_gc_policy_time',

'_gc_undo_affinity',

'_gc_defer_time',

'_hash_join_enabled',

'_offline_rollback_segments',

'_px_use_large_pool',

'_memory_imm_mode_without_autosga',

'_partition_large_extents',

'_optimizer_null_aware_antijoin',

'_optim_peek_user_binds',

'_optimizer_mjc_enabled',

'_optimizer_use_feedback',

'_optimizer_join_elimination_enabled',

'_optimizer_ads_use_result_cache',

'_optimizer_adaptive_plans',

'_optimizer_adaptive_cursor_sharing',

'_optimizer_extended_cursor_sharing',

'_optimizer_extended_cursor_sharing_rel',

'_optimizer_aggr_groupby_elim',

'_optimizer_reduce_groupby_key',

'_optimizer_cost_based_transformation',

'_use_adaptive_log_file_sync',

'_undo_autotune')

order by 1;

三． .数据库对象和备份信息

---3 对象信息

---数据库大小

select owner, trunc(sum(bytes) / 1024 / 1024 / 1024,2) as db_GB

from dba_segments

where owner in ('TRFFPN_IMG','TRFFPN_APP')

of 12

5墨值下载

oralce

关注

评论