There are a lot of options which can be used in database. Not all are available in your database. It depends which version of database you are using (standard/enterprise or else) or if a given option is active/tuned off/on. You can find details in V$OPTION.
select * from v$option; PARAMETER VALUE ----------------------------------------- ----- Active Data Guard FALSE Advanced Compression TRUE Backup Encryption TRUE Basic Compression TRUE ... Spatial TRUE Streams Capture TRUE Unused Block Compression TRUE XStream TRUE 64 rows selected
If you want to check which from above options were used in your database check DBA_FEATURE_USAGE_STATISTICS. Remember some of options are extra paid if you don’t use them turn them off and save money.
select * from dba_feature_usage_statistics;
View DBA_HIGH_WATER_MARK_STATISTICS displays information about database high-watermark statistics like:
-
Number of User Tables
-
Size of Largest Segment (Bytes)
-
Maximum Number of Partitions belonging to an User Table
-
Maximum Number of Partitions belonging to an User Index
-
Number of User Indexes
-
Maximum Number of Concurrent Sessions seen in the database
-
Maximum Size of the Database (Bytes)
-
Maximum Number of Datafiles
-
Maximum Number of Tablespaces
-
Maximum Number of CPUs
-
Maximum Query Length
-
Maximum Number of Services
select * from dba_high_water_mark_statistics;
DBA_CPU_USAGE_STATISTICS displays database CPU usage statistics
select * from dba_cpu_usage_statistics;
You can produce very nice and detailed reports about options, statistics and cpu usage using package DBMS_FEATURE_USAGE_REPORT in html
select output from table(dbms_feature_usage_report.display_html);
or text form
select output from table(dbms_feature_usage_report.display_text);
Information about database properties you can find in view DATABASE_PROPERTIES. It’s very useful source of standard settings for your database.
select * from database_properties; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------------- DICT.BASE 2 DEFAULT_TEMP_TABLESPACE TEMP DEFAULT_PERMANENT_TABLESPACE USERS DEFAULT_EDITION ORA$BASE Flashback Timestamp TimeZone GMT TDE_MASTER_KEY_ID DST_UPGRADE_STATE NONE DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 0 DEFAULT_TBS_TYPE SMALLFILE NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET AL32UTF8 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 11.2.0.3.0 GLOBAL_DB_NAME ORA11G.DBAORA.COM EXPORT_VIEWS_VERSION 8 WORKLOAD_CAPTURE_MODE WORKLOAD_REPLAY_MODE NO_USERID_VERIFIER_SALT 4EAE690CC98FBD2E2472E90C9A9D4A92 DBTIMEZONE 00:00
Have a fun 🙂
Tomasz