一、v$视图创建
由catalog.sql脚本创建。定义位于vfixed_view_definition、vfixed_table
由system用户访问的v视图实际上是指向v_视图的同义词，v_视图是原始v视图的视图，而原始v视图则建立在x基表基表之上。当其他dba需要访问v视图信息而并不需要sys或system用户密码时，就授予他们访问v_视图的权限，这样就可以访问v_的公共同义词v,也可以写成sys.v，从而避免因为简介引用公共同义词导致的性能问题。查询v视图需要付出性能代价，环境越大付出代价也越大 。
SQL> select count(*) from v$fixed_table where name like 'V%';
COUNT(*)
----------
778
SQL> select * from v$fixed_table where name like 'GV%' order by name fetch first 7 rows only;
NAME OBJECT_ID TYPE TABLE_NUM CON_ID
------------------------------ ---------- ----- ---------- ----------
GV$ACCESS 4294951284 VIEW 65537 0
GV$ACTIVE_INSTANCES 4294951353 VIEW 65537 0
GV$ACTIVE_SERVICES 4294952092 VIEW 65537 0
GV$ACTIVE_SESSION_HISTORY 4294952046 VIEW 65537 0
GV$ACTIVE_SESS_POOL_MTH 4294951659 VIEW 65537 0
GV$ADVISOR_CURRENT_SQLPLAN 4294953219 VIEW 65537 0
GV$ADVISOR_PROGRESS 4294952579 VIEW 65537 0
SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='V$DATAFILE';
VIEW_DEFINITION
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select FILE# , CREATION_CHANGE# , CREATION_TIME , TS# , RFILE# , STATUS , ENABLED , CHECKPOINT_CHANGE# , CHECKPOINT_TIME, UNRECOVERABLE_CHANGE#, UNRECOVERABLE_TIME, LAST_CHANGE# , LAST_TIME , OFFLINE_CHANGE# , ONLINE_CHANGE# , ONLINE_TIME , BYTES , BLOCKS , CREATE_BYTES , BLOCK_SIZE , NAME, PLUGGED_IN, BLOCK1_OFFSET , AUX_NAME , FIRST_NONLOGGED_SCN, FIRST_NONLOGGED_TIME, FOREIGN_DBID, FOREIGN_CREATION_CHANGE#, FOREIGN_CREATION_TIME, PLUGGED_READONLY, PLUGIN_CHANGE#, PLUGIN_RESETLOGS_CHANGE#, PLUGIN_RESETLOGS_TIME, CON_ID from GV$DATAFILE where inst_id = USERENV('Instance')
二、DBA_VIEWS
SQL> select text from dba_views where view_name='DBA_DATA_FILES';
TEXT
--------------------------------------------------------------------------------
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1,
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER')),
decode(bitand(f.spare2, 3), NULL, 'OFF', 0, 'OFF', 1, 'ENABLED', 2,
'SUSPEND')
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v,
(select * from x$kccfe
where (con_id is NULL or con_id = sys_context('USERENV', 'CON_ID'))) fe
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
and fe.fenum = f.file#
union all
select
v.name,f.file#, ts.name,
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#,
decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER')),
decode(bitand(f.spare2, 3), NULL, 'OFF', 0, 'OFF', 1, 'ENABLED', 2, 'SUSP
END')
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts,
(select * from x$kccfe
where (con_id is NULL or con_id = sys_context('USERENV', 'CON_ID'))) fe
where v.file# = f.file#
and f.spare1 is NOT NULL
and v.file# = hc.ktfbhcafno
and hc.ktfbhctsn = ts.ts#
and fe.fenum = f.file#
三、有用的v$脚本
–基本信息
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
–数据库信息
SQL> SQL> select name,created,log_mode,open_mode,database_role from v$database;
NAME CREATED LOG_MODE OPEN_MODE DATABASE_ROLE
------------------------------ --------- ------------ -------------------- ----------------
PROD4 12-SEP-22 ARCHIVELOG READ WRITE PRIMARY
–awr信息
SQL> select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like '%AWR%';
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES
---------------------------------------------------------------- ---------------------------------------------------------------- ------------------
SM/AWR Server Manageability - Automatic Workload Repository 90944
–最旧awr
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
02-NOV-22 09.26.38.880271000 PM +08:00
–awr信息保留多少天
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
15
–修改
SQL> exec dbms_stats.alter_stats_history_retention(15);
PL/SQL procedure successfully completed
3.2、基本许可信息
SQL> select sessions_max,sessions_warning,sessions_current,users_max from v$license;
SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT USERS_MAX
------------ ---------------- ---------------- ----------
0 0 1 0
3.3、已安装的数据库选项
SQL> select * from v$option order by parameter;
PARAMETER VALUE CON_ID
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
ASM Proxy Instance FALSE 0
Active Data Guard TRUE 0
Adaptive Execution Plans TRUE 0
Advanced Analytics TRUE 0
Advanced Compression TRUE 0
Advanced Index Compression TRUE 0
Advanced replication TRUE 0
Application Role TRUE 0
Automatic Data Optimization TRUE 0
Automatic Storage Management FALSE 0
Backup Encryption TRUE 0
Basic Compression TRUE 0
Bit-mapped indexes TRUE 0
Block Change Tracking TRUE 0
Block Media Recovery TRUE 0
Cache Fusion Lock Accelerator TRUE 0
Centrally Managed User TRUE 0
Change Data Capture TRUE 0
Coalesce Index TRUE 0
Connection multiplexing TRUE 0
Connection pooling TRUE 0
Cross Transportable Backups TRUE 0
DICOM TRUE 0
Data Mining TRUE 0
Data Redaction TRUE 0
Database queuing TRUE 0
Database resource manager TRUE 0
Deferred Segment Creation TRUE 0
Duplexed backups TRUE 0
Enterprise User Security TRUE 0
Exadata Discovery TRUE 0
Export transportable tablespaces TRUE 0
Fast-Start Fault Recovery TRUE 0
File Mapping TRUE 0
Fine-grained Auditing TRUE 0
Fine-grained access control TRUE 0
Flashback Data Archive TRUE 0
Flashback Database TRUE 0
Flashback Table TRUE 0
Global Data Services TRUE 0
Heat Map TRUE 0
I/O Server FALSE 0
In-Memory Aggregation TRUE 0
In-Memory Column Store TRUE 0
Incremental backup and recovery TRUE 0
Instead-of triggers TRUE 0
Java TRUE 0
Join index TRUE 0
Managed Standby TRUE 0
Management Database FALSE 0
Materialized view rewrite TRUE 0
OLAP TRUE 0
OLAP Window Functions TRUE 0
Objects TRUE 0
Online Index Build TRUE 0
Online Redefinition TRUE 0
Oracle Data Guard TRUE 0
Oracle Database Vault FALSE 0
Oracle Label Security FALSE 0
Parallel backup and recovery TRUE 0
Parallel execution TRUE 0
Parallel load TRUE 0
Partitioning TRUE 0
Plan Stability TRUE 0
Point-in-time tablespace recovery TRUE 0
Privilege Analysis TRUE 0
Proxy authentication/authorization TRUE 0
Real Application Clusters FALSE 0
Real Application Security TRUE 0
Real Application Testing TRUE 0
Result Cache TRUE 0
SQL Plan Management TRUE 0
Sample Scan TRUE 0
SecureFiles Encryption TRUE 0
Server Flash Cache TRUE 0
Snapshot time recovery TRUE 0
Spatial TRUE 0
Streams Capture TRUE 0
Table Clustering TRUE 0
Transparent Application Failover TRUE 0
Transparent Data Encryption TRUE 0
Transparent Sensitive Data Protection TRUE 0
Trial Recovery TRUE 0
Unified Auditing FALSE 0
Unused Block Compression TRUE 0
XStream TRUE 0
Zone Maps TRUE 0
87 rows selected.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
