select * from gv$version;
select dbid,name,created,log_mode from gv$database;
select version,instance_name,startup_time,status from gv$instance;
---数据库运行时间
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 ('CJC','CHEN')
group by owner
order by 1;
---表数量
select owner, count(*)
from dba_tables
where owner in ('CJC','CHEN')
group by owner
order by 1;
---临时表数量
select owner, count(*)
from dba_tables
where owner in ('CJC','CHEN')
and temporary = 'Y'
group by owner
order by 1;
---索引信息
select owner, count(*)
from dba_indexes
where owner in ('CJC','CHEN')
group by owner
order by 1;
---视图数量
select owner, count(*)
from dba_views
where owner in ('CJC','CHEN')
group by owner
order by 1;
---触发器信息
select owner, count(*)
from dba_triggers
where owner in ('CJC','CHEN')
group by owner
order by 1;
---存储过程
select owner, count(*)
from dba_procedures
where owner in ('CJC','CHEN')
group by owner
order by 1;
---无效的对象
select *
from dba_objects
where status = 'INVALID'
and owner in
('CJC','CHEN');
---JOB和定时任务
SELECT * from dba_jobs;
[oracle@cjcos01 ~]$ crontab -l
---RMAN备份信息
select to_char(start_time, 'yyyy-mm-dd') start_time,
to_char(start_time, 'day') day,
round(sum(OUTPUT_BYTES) / 1024 / 1024 / 1024, 2) SIZE_GB
from v$backup_set_details
group by to_char(start_time, 'yyyy-mm-dd'), to_char(start_time, 'day')
order by start_time desc;
---
select to_char(start_time, 'yyyy-mm-dd') start_time,
to_char(start_time, 'day') day,
round(sum(BYTES) / 1024 / 1024 / 1024, 2) SIZE_GB
from v$backup_piece
where handle is not null
group by to_char(start_time, 'yyyy-mm-dd'), to_char(start_time, 'day')
order by start_time desc;
---大表
select sum(bytes)/1024/1024/1024 from dba_segments where owner='CJC' and segment_name='T1';
select sum(bytes) / 1024 / 1024 / 1024
from dba_segments
where owner = 'CJC'
and segment_name in
(select segment_name
from dba_lobs
where owner = 'CJC'
and table_name = 'T1');
select 0.09375+221.677734375 from dual;
四 .数据库文件信息
select tablespace_name,
file_name,
file_id,
status,
trunc(bytes / 1024 / 1024 / 1024, 2) as FILE_GB,
autoextensible,
trunc(maxbytes / 1024 / 1024 / 1024, 2) as MAX_GB
from dba_data_files
order by file_id;
---4.2临时文件信息
select tablespace_name,
file_name,
file_id,
status,
trunc(bytes / 1024 / 1024 / 1024, 2) as FILE_GB,
autoextensible,
trunc(maxbytes / 1024 / 1024 / 1024, 2) as MAX_GB
from dba_temp_files
order by file_id;
---4.3控制文件信息
select * from v$controlfile;
---查看控制文件内容
alter database backup controlfile to trace as '/home/oracle/20200620.ctl';
---4.4日志文件信息
select a.group#,
THREAD#,
b.member,
a.members,
a.status,
a.sequence#,
bytes / 1024 / 1024 as file_mb
from v$log a, v$logfile b
where a.group# = b.group#
order by 1, 2;
---归档文件
select * from v$archived_log;
select * from v$log_history;
---4.5回滚段
---select * from v$rollname;
select owner, tablespace_name, segment_id, segment_name, status
from dba_rollback_segs
where status = 'ONLINE';
---4.6归档频率
[root@rac01 bin]# ./srvctl status nodeapps -n rac01
六．数据库连接信息
select inst_id, schemaname, count(*)
from gv$session
where schemaname in ('CJC', 'CHEN', 'SCOTT')
group by inst_id, schemaname
order by 1;
七 .数据库资源限制
select * from v$resource_limit;
八．数据库权限
---角色
select *
from dba_role_privs
where grantee in
('CJC','CHEN','SCOTT')
order by 1, 2;
---系统权限
select *
from dba_sys_privs
where grantee in
('CJC','CHEN','SCOTT')
order by 1, 2;
---表权限
select * from dba_tab_privs where OWNER IN ('CJC', 'CHEN', 'SCOTT');
---列权限
select * from dba_col_privs where OWNER IN ('CJC','CHEN','SCOTT');
九．数据库高水位
(1)导出表，删除表，再导入表
(2)
alter table t1 move; ---索引状态UNUSABLE
select * from t1;
create index i_t1_id on t1(id);
select * from t1 where id=1;
select \*+index(t1 i_t1_id)*\ * from t1 where id=1;---ORA-01502
alter index i_t1_id rebuild;
(3)整理表，不影响DML操作
alter table t3 enable ROW MOVEMENT;--启动行移动功能
alter table t3 shrink space compact; --只整理碎片 不回收空间
alter table t3 disable ROW MOVEMENT;
(4)重置高水位，此时不能有DML操作
alter table t3 enable ROW MOVEMENT;
alter table t3 shrink space; --整理碎片并回收空间，并调整水位线。业务少时执行
alter table t3 disable ROW MOVEMENT;--关闭行移动*/
十 .性能
10.1 TOP SQL信息
---elapsed_time
select a.*, elapsed_seconds / executions elapsed_per
from (select sql_text,
---sql_fulltext,
sql_id,
round(elapsed_time / 1000000, 2) elapsed_seconds,
executions,
buffer_gets,
disk_reads
from (select * from v$sql order by elapsed_time desc)
where rownum <= 100) a
where executions > 0
order by elapsed_per desc;
10.2 等待事件
---V$SYSTEM_EVENT
---v$session_wait
SELECT event,
total_waits waits,
total_timeouts timeouts,
time_waited total_time, ---in hundredths of a second
average_wait avg
FROM V$SYSTEM_EVENT
ORDER BY 2 DESC;
select * from v$session_wait;
10.3数据库各时间段负载查询
select s.snap_date,
decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
to_char(round(s.seconds / 60, 2)) "elapse(min)",
round(t.db_time / 1000000 / 60, 2) "DB time(min)",
s.redosize redo,
round(s.redosize / s.seconds, 2) "redo/s",
s.logicalreads logical,
round(s.logicalreads / s.seconds, 2) "logical/s",
physicalreads physical,
round(s.physicalreads / s.seconds, 2) "phy/s",
s.executes execs,
round(s.executes / s.seconds, 2) "execs/s",
s.parse,
round(s.parse / s.seconds, 2) "parse/s",
s.hardparse,
round(s.hardparse / s.seconds, 2) "hardparse/s",
s.transactions trans,
round(s.transactions / s.seconds, 2) "trans/s"
from (select curr_redo - last_redo redosize,
curr_logicalreads - last_logicalreads logicalreads,
curr_physicalreads - last_physicalreads physicalreads,
curr_executes - last_executes executes,
curr_parse - last_parse parse,
curr_hardparse - last_hardparse hardparse,
curr_transactions - last_transactions transactions,
round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
to_char(currtime, 'yy/mm/dd') snap_date,
to_char(currtime, 'hh24:mi') currtime,
currsnap_id endsnap_id,
to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
from (select a.redo last_redo,
a.logicalreads last_logicalreads,
a.physicalreads last_physicalreads,
a.executes last_executes,
a.parse last_parse,
a.hardparse last_hardparse,
a.transactions last_transactions,
lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
b.end_interval_time lasttime,
lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
b.startup_time
from (select snap_id,
dbid,
instance_number,
sum(decode(stat_name, 'redo size', value, 0)) redo,
sum(decode(stat_name,
'session logical reads',
value,
0)) logicalreads,
sum(decode(stat_name,
'physical reads',
value,
0)) physicalreads,
sum(decode(stat_name, 'execute count', value, 0)) executes,
sum(decode(stat_name,
'parse count (total)',
value,
0)) parse,
sum(decode(stat_name,
'parse count (hard)',
value,
0)) hardparse,
sum(decode(stat_name,
'user rollbacks',
value,
'user commits',
value,
0)) transactions
from dba_hist_sysstat
where stat_name in
('redo size',
'session logical reads',
'physical reads',
'execute count',
'user rollbacks',
'user commits',
'parse count (hard)',
'parse count (total)')
group by snap_id, dbid, instance_number) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
order by end_interval_time)) s,
(select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id
from dba_hist_sys_time_model a, dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.stat_name = 'DB time') t
where s.endsnap_id = t.endsnap_id
order by s.snap_date desc, time desc;
10.4 查看系统统计信息
select a.statistic#, a.value, a.name, b.name
from V$SYSSTAT a, V$STATNAME b
where a.statistic# = b.statistic#
order by 2 desc;
十一 .数据库告警日志信息