Olá,
Quando instalamos o Oracle e configuramos uma instância do banco de dados Oracle, podemos verificar posteriormente a sua versão, quais "options" e "features" do banco de dados foram instaladas e usadas. Segue abaixo 4 views do dicionário de dados que geralmente utilizo para verificar tais informações em um banco de dados Oracle.
- V$VERSION: Lista a versão e edição do banco de dados Oracle.
- V$OPTION: Lista as options e features instaladas no banco de dados. Geralmente as options são licenciadas separadamente e as features costumam já vir no produto que foi instalado (Standard Edition, Enterprise Edition).
- DBA_REGISTRY: Lista os componentes que foram instalados no banco de dados.
- DBA_FEATURE_USAGE_STATISTICS: Lista as features e as estatísticas de uso. Essas estatísticas são geradas aptravés de uma procedure não documentada do Oracle.
Segue abaixo um exemplo de saída das views acima executadas em um banco de dados Oracle Standard Edition e um Oracle Enterprise Edition.
-- --------------------------
-- Oracle Standard Edtion --
-- --------------------------
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> select * from v$option order by 2,1;
PARAMETER VALUE
---------------------------------------------------------------- ---------------
Active Data Guard FALSE
Advanced Compression FALSE
Advanced replication FALSE
Application Role FALSE
Automatic Storage Management FALSE
Backup Encryption FALSE
Basic Compression FALSE
Bit-mapped indexes FALSE
Block Change Tracking FALSE
Block Media Recovery FALSE
Change Data Capture FALSE
Data Mining FALSE
Data Redaction FALSE
Database resource manager FALSE
Deferred Segment Creation FALSE
Duplexed backups FALSE
Enterprise User Security FALSE
Export transportable tablespaces FALSE
Fast-Start Fault Recovery FALSE
File Mapping FALSE
Fine-grained access control FALSE
Fine-grained Auditing FALSE
Flashback Database FALSE
Flashback Table FALSE
Join index FALSE
Managed Standby FALSE
Materialized view rewrite FALSE
OLAP FALSE
Online Index Build FALSE
Online Redefinition FALSE
Oracle Data Guard FALSE
Oracle Database Vault FALSE
Oracle Label Security FALSE
Parallel backup and recovery FALSE
Parallel execution FALSE
Partitioning FALSE
Point-in-time tablespace recovery FALSE
Real Application Clusters FALSE
Real Application Testing FALSE
Result Cache FALSE
SecureFiles Encryption FALSE
Server Flash Cache FALSE
Spatial FALSE
SQL Plan Management FALSE
Streams Capture FALSE
Transparent Data Encryption FALSE
Trial Recovery FALSE
Unused Block Compression FALSE
Coalesce Index TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
DICOM TRUE
Flashback Data Archive TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Java TRUE
Objects TRUE
OLAP Window Functions TRUE
Parallel load TRUE
Plan Stability TRUE
Proxy authentication/authorization TRUE
Sample Scan TRUE
Transparent Application Failover TRUE
XStream TRUE
65 linhas selecionadas.
SQL> select name,version,detected_usages,description
2 from DBA_FEATURE_USAGE_STATISTICS
3 where rownum<=50 order by 1;
NAME VERSION DETECTED_USAGES DESCRIPTION
-------------------------------------------------- ----------------- --------------- ----------------------------------------------------------------------------------------------------------------------------------------
Automatic Memory Tuning 11.2.0.4.0 0 Automatic Memory Tuning is enabled.
Automatic SGA Tuning 11.2.0.4.0 25 Automatic SGA Tuning is enabled.
Backup ZLIB Compression 11.2.0.4.0 0 ZLIB compressed backups are being used.
Character Semantics 11.2.0.4.0 0 Character length semantics is used in Oracle Database
Character Set 11.2.0.4.0 25 Character set is used in Oracle Database
Client Identifier 11.2.0.4.0 0 Application User Proxy Authentication: Client Identifier is used at this specific time.
Clusterwide Global Transactions 11.2.0.4.0 0 Clusterwide Global Transactions is being used.
Crossedition Triggers 11.2.0.4.0 0 Crossedition triggers is being used.
CSSCAN 11.2.0.4.0 0 Oracle Database has been scanned at least once for character set:CSSCAN has been run at least once.
Data Guard 11.2.0.4.0 0 Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.
Data Mining 11.2.0.4.0 0 There exist Oracle Data Mining models in the database.
Database Migration Assistant for Unicode 11.2.0.4.0 0 Database Migration Assistant for Unicode has been used.
Deferred Segment Creation 11.2.0.4.0 25 Deferred Segment Creation is being used
Dynamic SGA 11.2.0.4.0 0 The Oracle SGA has been dynamically resized through an ALTER SYSTEM SET statement.
Editioning Views 11.2.0.4.0 0 Editioning views is being used.
Editions 11.2.0.4.0 0 Editions is being used.
EM Database Control 11.2.0.4.0 0 EM Database Control Home Page has been visited at least once.
EM Grid Control 11.2.0.4.0 0 EM Grid Control Database Home Page has been visited at least once.
EM Performance Page 11.2.0.4.0 0 EM Performance Page has been visited at least once.
Encrypted Tablespaces 11.2.0.4.0 0 Encrypted Tablespaces is enabled.
File Mapping 11.2.0.4.0 0 File Mapping, the mechanism that shows a complete mapping of a file to logical volumes and physical devices, is being used.
Flashback Data Archive 11.2.0.4.0 0 Flashback Data Archive, a historical repository of changes to data contained in a table, is used
Flashback Database 11.2.0.4.0 0 Flashback Database, a rewind button for the database, is enabled
Internode Parallel Execution 11.2.0.4.0 0 Internode Parallel Execution is being used.
Label Security 11.2.0.4.0 0 Oracle Label Security, that enables label-based access control Oracle applications, is being used.
Locally Managed Tablespaces (system) 11.2.0.4.0 25 There exists tablespaces that are locally managed in the database.
Locally Managed Tablespaces (user) 11.2.0.4.0 25 There exists user tablespaces that are locally managed in the database.
Messaging Gateway 11.2.0.4.0 0 Messaging Gateway, that enables communication between non-Oracle messaging systems and Advanced Queuing (AQ), link configured.
MTTR Advisor 11.2.0.4.0 0 Mean Time to Recover Advisor is enabled.
Multiple Block Sizes 11.2.0.4.0 0 Multiple Block Sizes are being used with this database.
OLAP - Analytic Workspaces 11.2.0.4.0 0 OLAP - the analytic workspaces stored in the database.
OLAP - Cubes 11.2.0.4.0 0 OLAP - number of cubes in the OLAP catalog that are fully mapped and accessible by the OLAP API.
Oracle Database Vault 11.2.0.4.0 0 Oracle Database Vault is being used
Oracle Managed Files 11.2.0.4.0 0 Database files are being managed by Oracle.
Oracle Secure Backup 11.2.0.4.0 0 Oracle Secure Backup is used for backups to tertiary storage.
Oracle Text 11.2.0.4.0 0 Oracle Text is being used - there is at least one oracle text index
Parallel SQL DDL Execution 11.2.0.4.0 0 Parallel SQL DDL Execution is being used.
Parallel SQL DML Execution 11.2.0.4.0 0 Parallel SQL DML Execution is being used.
Parallel SQL Query Execution 11.2.0.4.0 0 Parallel SQL Query Execution is being used.
Partitioning (system) 11.2.0.4.0 25 Oracle Partitioning option is being used - there is at least one partitioned object created.
Partitioning (user) 11.2.0.4.0 0 Oracle Partitioning option is being used - there is at least one user partitioned object created.
PL/SQL Native Compilation 11.2.0.4.0 0 PL/SQL Native Compilation is being used - there is at least one natively compiled PL/SQL library unit in the database.
Quality of Service Management 11.2.0.4.0 0 Quality of Service Management has been used.
Real Application Clusters (RAC) 11.2.0.4.0 0 Real Application Clusters (RAC) is configured.
Recovery Area 11.2.0.4.0 0 The recovery area is configured.
Recovery Manager (RMAN) 11.2.0.4.0 24 Recovery Manager (RMAN) is being used to backup the database.
RMAN - Disk Backup 11.2.0.4.0 24 Recovery Manager (RMAN) is being used to backup the database to disk.
RMAN - Tape Backup 11.2.0.4.0 0 Recovery Manager (RMAN) is being used to backup the database to tape.
SQL Monitoring and Tuning pages 11.2.0.4.0 0 EM SQL Monitoring and Tuning pages has been visited at least once.
Very Large Memory 11.2.0.4.0 0 Very Large Memory is enabled.
50 linhas selecionadas.
SQL> select comp_id,comp_name,version,status,modified,schema,procedure
2 from dba_registry
3 order by 1;
COMP_ID COMP_NAME VERSION STATUS MODIFIED SCHEMA PROCEDURE
------------------------------ -------------------------------------------------- ------------------------------ ----------- -------------------- ------------------------------ ----------------------------------
CATALOG Oracle Database Catalog Views 11.2.0.4.0 VALID 20-JAN-2017 19:29:19 SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC Oracle Database Packages and Types 11.2.0.4.0 VALID 20-JAN-2017 19:29:19 SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC
CONTEXT Oracle Text 11.2.0.4.0 VALID 20-JAN-2017 19:29:20 CTXSYS VALIDATE_CONTEXT
EM Oracle Enterprise Manager 11.2.0.4.0 VALID 12-JAN-2017 10:24:21 SYSMAN
OWM Oracle Workspace Manager 11.2.0.4.0 VALID 20-JAN-2017 19:29:20 WMSYS VALIDATE_OWM
XDB Oracle XML Database 11.2.0.4.0 VALID 20-JAN-2017 19:29:20 XDB DBMS_REGXDB.VALIDATEXDB
6 linhas selecionadas.
-- ----------------------------
-- Oracle Enterprise Edtion --
-- ----------------------------
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select * from v$option order by 2,1;
PARAMETER VALUE
---------------------------------------------------------------- ---------------
Automatic Storage Management FALSE
Data Mining FALSE
OLAP FALSE
Oracle Database Vault FALSE
Oracle Label Security FALSE
Real Application Clusters FALSE
Real Application Testing FALSE
Active Data Guard TRUE
Advanced Compression TRUE
Advanced replication TRUE
Application Role TRUE
Backup Encryption TRUE
Basic Compression TRUE
Bit-mapped indexes TRUE
Block Change Tracking TRUE
Block Media Recovery TRUE
Change Data Capture TRUE
Coalesce Index TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Database resource manager TRUE
Deferred Segment Creation TRUE
DICOM TRUE
Duplexed backups TRUE
Enterprise User Security TRUE
Export transportable tablespaces TRUE
Fast-Start Fault Recovery TRUE
File Mapping TRUE
Fine-grained access control TRUE
Fine-grained Auditing TRUE
Flashback Data Archive TRUE
Flashback Database TRUE
Flashback Table TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Java TRUE
Join index TRUE
Managed Standby TRUE
Materialized view rewrite TRUE
Objects TRUE
OLAP Window Functions TRUE
Online Index Build TRUE
Online Redefinition TRUE
Oracle Data Guard TRUE
Parallel backup and recovery TRUE
Parallel execution TRUE
Parallel load TRUE
Partitioning TRUE
Plan Stability TRUE
Point-in-time tablespace recovery TRUE
Proxy authentication/authorization TRUE
Result Cache TRUE
Sample Scan TRUE
SecureFiles Encryption TRUE
Server Flash Cache TRUE
Spatial TRUE
SQL Plan Management TRUE
Streams Capture TRUE
Transparent Application Failover TRUE
Transparent Data Encryption TRUE
Trial Recovery TRUE
Unused Block Compression TRUE
XStream TRUE
64 linhas selecionadas.
SQL> select name,version,detected_usages,description
2 from DBA_FEATURE_USAGE_STATISTICS
3 where rownum<=50 order by 1;
NAME VERSION DETECTED_USAGES DESCRIPTION
-------------------------------------------------------------------------------- ----------------- --------------- ----------------------------------------------------------------------------------------------------------------------
Active Data Guard - Real-Time Query on Physical Standby 11.2.0.3.0 0 Active Data Guard real-time query is enabled on a physical standby
ADDM 11.2.0.3.0 11 ADDM has been used.
Advanced Replication 11.2.0.3.0 0 Advanced Replication has been enabled.
ASO native encryption and checksumming 11.2.0.3.0 0 ASO network native encryption and checksumming is being used.
Audit Options 11.2.0.3.0 281 Audit options in use.
Automatic Maintenance - Optimizer Statistics Gathering 11.2.0.3.0 247 Automatic initiation of Optimizer Statistics Collection
Automatic Maintenance - Space Advisor 11.2.0.3.0 52 Automatic initiation of Space Advisor
Automatic Maintenance - SQL Tuning Advisor 11.2.0.3.0 210 Automatic initiation of SQL Tuning Advisor
Automatic Segment Space Management (system) 11.2.0.3.0 281 Extents of locally managed tablespaces are managed automatically by Oracle.
Automatic Segment Space Management (user) 11.2.0.3.0 281 Extents of locally managed user tablespaces are managed automatically by Oracle.
Automatic SQL Execution Memory 11.2.0.3.0 281 Sizing of work areas for all dedicated sessions (PGA) is automatic.
Automatic Storage Management 11.2.0.3.0 0 Automatic Storage Management has been enabled
Automatic Undo Management 11.2.0.3.0 281 Oracle automatically manages undo data using an UNDO tablespace.
Automatic Workload Repository 11.2.0.3.0 0 A manual Automatic Workload Repository (AWR) snapshot was taken in the last sample period.
AWR Baseline 11.2.0.3.0 0 At least one AWR Baseline has been created by the user
AWR Baseline Template 11.2.0.3.0 0 At least one AWR Baseline Template has been created by the user
AWR Report 11.2.0.3.0 9 At least one Workload Repository Report has been created by the user
Backup Encryption 11.2.0.3.0 0 Encrypted backups are being used.
Backup Rollforward 11.2.0.3.0 0 Backup Rollforward strategy is being used to backup the database.
Baseline Adaptive Thresholds 11.2.0.3.0 0 Adaptive Thresholds have been configured.
Baseline Static Computations 11.2.0.3.0 0 Static baseline statistics have been computed.
Change-Aware Incremental Backup 11.2.0.3.0 120 Track blocks that have changed in the database.
Character Semantics 11.2.0.3.0 0 Character length semantics is used in Oracle Database
Character Set 11.2.0.3.0 281 Character set is used in Oracle Database
Client Identifier 11.2.0.3.0 0 Application User Proxy Authentication: Client Identifier is used at this specific time.
Clusterwide Global Transactions 11.2.0.3.0 0 Clusterwide Global Transactions is being used.
Crossedition Triggers 11.2.0.3.0 0 Crossedition triggers is being used.
CSSCAN 11.2.0.3.0 0 Oracle Database has been scanned at least once for character set:CSSCAN has been run at least once.
Data Guard 11.2.0.3.0 278 Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.
Data Mining 11.2.0.3.0 0 There exist Oracle Data Mining models in the database.
Data Recovery Advisor 11.2.0.3.0 0 Data Recovery Advisor (DRA) is being used to repair the database.
Database Migration Assistant for Unicode 11.2.0.3.0 0 Database Migration Assistant for Unicode has been used.
Direct NFS 11.2.0.3.0 0 Direct NFS is being used to connect to an NFS server
Dynamic SGA 11.2.0.3.0 2 The Oracle SGA has been dynamically resized through an ALTER SYSTEM SET statement.
Editioning Views 11.2.0.3.0 0 Editioning views is being used.
Editions 11.2.0.3.0 0 Editions is being used.
EM Database Control 11.2.0.3.0 0 EM Database Control Home Page has been visited at least once.
Instance Caging 11.2.0.3.0 0 Instance Caging is being used to limit the CPU usage by the database instance.
Locator 11.2.0.3.0 0 There is at least one usage of the Oracle Locator index metadata table.
Resource Manager 11.2.0.3.0 0 Oracle Database Resource Manager is being used to manage database resources.
Server Flash Cache 11.2.0.3.0 0 Server Flash Cache is being used with this database.
Server Parameter File 11.2.0.3.0 281 The server parameter file (SPFILE) was used to startup the database.
Shared Server 11.2.0.3.0 0 The database is configured as Shared Server, where one server process can service multiple client programs.
Spatial 11.2.0.3.0 0 There is at least one usage of the Oracle Spatial index metadata table.
SQL Access Advisor 11.2.0.3.0 0 SQL Access Advisor has been used.
SQL Performance Analyzer 11.2.0.3.0 0 SQL Performance Analyzer has been used.
SQL Tuning Advisor 11.2.0.3.0 16 SQL Tuning Advisor has been used.
SQL Workload Manager 11.2.0.3.0 0 SQL Workload Manager has been used.
Tune MView 11.2.0.3.0 0 Tune MView has been used.
Undo Advisor 11.2.0.3.0 0 Undo Advisor has been used.
50 linhas selecionadas.
SQL> select comp_id,comp_name,version,status,modified,schema,procedure
2 from dba_registry
3 order by 1;
COMP_ID COMP_NAME VERSION STATUS MODIFIED SCHEMA PROCEDURE
------------------------------ -------------------------------------------------- ------------------------------ ----------- -------------------- ------------------------------ ----------------------------------
CATALOG Oracle Database Catalog Views 11.2.0.3.0 VALID 03-OUT-2012 22:47:46 SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC Oracle Database Packages and Types 11.2.0.3.0 VALID 03-OUT-2012 22:47:46 SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC
OWM Oracle Workspace Manager 11.2.0.3.0 VALID 03-OUT-2012 22:48:14 WMSYS VALIDATE_OWM
Nenhum comentário:
Postar um comentário