oracle视图

V$PROCESS  Contains information about the currently active processes 

V$SESSION      Lists session information for each current session 
V$SESS_IO      Contains I/O statistics for each user session 
V$SESSION_LONGOPS Displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle Database release. 
V$SESSION_WAIT    Displays the current or last wait for each session 
V$SESSION_WAIT_HISTORY    Lists the last ten wait events for each active session 
V$WAIT_CHAINS    Displays information about blocked sessions 
V$SYSSTAT Contains session statistics 
V$RESOURCE_LIMIT    Provides information about current and maximum global resource utilization for some system resources 
V$SQLAREA    Contains statistics about shared SQL areas. Contains one row for each SQL string. Provides statistics about SQL statements that are in memory, parsed, and ready for execution

 

---V$MEMORY_TARGET_ADVICE    provides tuning advice for the MEMORY_TARGET initialization parameter
SQL>  select * from v$memory_target_advice order by memory_size;

---V$SGA_TARGET_ADVICE    view provides information that helps you decide on a value for SGA_TARGET
SQL> select * from v$sga_target_advice order by sga_size;

 

V$SGA Displays summary information about the system global area (SGA). 
V$SGAINFO Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory. 
V$SGASTAT Displays detailed information about how memory is allocated within the shared pool, large pool, Java pool, and Streams pool. 
V$PGASTAT Displays PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup. 
V$MEMORY_DYNAMIC_COMPONENTS Displays information on the current size of all automatically tuned and static memory components, with the last operation (for example, grow or shrink) that occurred on each. 
V$SGA_DYNAMIC_COMPONENTS Displays the current sizes of all SGA components, and the last operation for each component. 
V$SGA_DYNAMIC_FREE_MEMORY Displays information about the amount of SGA memory available for future dynamic SGA resize operations. 
V$MEMORY_CURRENT_RESIZE_OPS Displays information about resize operations that are currently in progress. A resize operation is an enlargement or reduction of the SGA, the instance PGA, or a dynamic SGA component. 
V$SGA_CURRENT_RESIZE_OPS Displays information about dynamic SGA component resize operations that are currently in progress. 
V$MEMORY_RESIZE_OPS Displays information about the last 800 completed memory component resize operations, including automatic grow and shrink operations for SGA_TARGET and PGA_AGGREGATE_TARGET. 
V$SGA_RESIZE_OPS Displays information about the last 800 completed SGA component resize operations. 
V$MEMORY_TARGET_ADVICE Displays information that helps you tune MEMORY_TARGET if you enabled automatic memory management. 
V$SGA_TARGET_ADVICE Displays information that helps you tune SGA_TARGET. 
V$PGA_TARGET_ADVICE Displays information that helps you tune PGA_AGGREGATE_TARGET.

 

V$LOCK Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch 
DBA_BLOCKERS Displays a session if it is holding a lock on an object for which another session is waiting 
DBA_WAITERS Displays a session if it is waiting for a locked object 
DBA_DDL_LOCKS Lists all DDL locks held in the database and all outstanding requests for a DDL lock 
DBA_DML_LOCKS Lists all DML locks held in the database and all outstanding requests for a DML lock 
DBA_LOCK Lists all locks or latches held in the database and all outstanding requests for a lock or latch 
DBA_LOCK_INTERNAL Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch 
V$LOCKED_OBJECT Lists all locks acquired by every transaction on the system 
V$SESSION_WAIT Lists the resources or events for which active sessions are waiting 
V$SYSSTAT Contains session statistics

V$RESOURCE_LIMIT Provides information about current and maximum global resource utilization for some system resources 
V$SQLAREA Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution 
V$LATCH Contains statistics for nonparent latches and summary statistics for parent latches 

SELECT * FROM V$DIAG_CRITICAL_ERROR;

---To find the trace file for your current session:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
---To find all trace files for the current instance:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
---To determine the trace file for each Oracle Database process:
SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;

tab$, clu$, fet$, uet$, seg$, undo$, ts$, file$, obj$, ind$, icol$, col$, user$, con$, cdef$, ccol$, bootstrap$, objauth$, ugroup$, tsq$, syn$, view$, typed_view$, superobj$, seq$, lob$, coltype$, subcoltype$, ntab$, refcon$, opqtype$, dependency$, access$, viewcon$, icoldep$, dual$, sysauth$, objpriv$, defrole$, and ecol$

BEGIN
  DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'my_run');
END;
/

---To obtain a list of health check names, run the following query:
SELECT name FROM v$hm_check WHERE internal_check='N';

SELECT c.name check_name, p.name parameter_name, p.type,
p.default_value, p.description
FROM v$hm_check_param p, v$hm_check c
WHERE p.check_id = c.id and c.internal_check = 'N'
ORDER BY c.name;

BEGIN
  DBMS_HM.RUN_CHECK (
    check_name    => 'Transaction Integrity Check',
    run_name        => 'my_run',
    input_params => 'TXN_ID=7.33.2');
END;
/

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.GET_RUN_REPORT('HM_RUN_1061') FROM DUAL;

adrci>> show hm_run
adrci>> create report hm_run run_name
adrci>> show report hm_run run_name

---V$HM_RUN view to determine a history of checker runs
SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run;

V$DATABASE Displays database information from the control file 
V$CONTROLFILE Lists the names of control files 
V$CONTROLFILE_RECORD_SECTION Displays information about control file record sections 
V$PARAMETER Displays the names of control files as specified in the CONTROL_FILES initialization parameter

SELECT BLOCKSIZE FROM V$LOG;

ALTER DATABASE ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 100M;
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

V$DATABASE Shows if the database is in ARCHIVELOG or NOARCHIVELOG mode and if MANUAL (archiving mode) has been specified. 
V$ARCHIVED_LOG Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information. 
V$ARCHIVE_DEST Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations. 
V$ARCHIVE_PROCESSES Displays information about the state of the various archive processes for an instance. 
V$BACKUP_REDOLOG Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information. 
V$LOG Displays all redo log groups for the database and indicates which need to be archived. 
V$LOG_HISTORY Contains log history information such as which logs have been archived and the SCN range for each archived log.

SQL> COLUMN PLATFORM_NAME FORMAT A36
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM. ORDER BY PLATFORM_NAME;

---查询平台
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
        FROM V$TRANSPORTABLE_PLATFORM. tp, V$DATABASE d
        WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);
SQL> ALTER TABLESPACE sales_1 READ ONLY;
SQL> ALTER TABLESPACE sales_2 READ ONLY;
expdp system dumpfile=expdat.dmp directory=data_pump_dir transport_tablespaces=sales_1,sales_2 logfile=tts_export.log;
expdp system dumpfile=expdat.dmp directory=data_pump_dir transport_tablespaces=sales_1,sales_2 transport_full_check=y logfile=tts_export.log;

C:\>RMAN TARGET /
Recovery Manager: Release 11.2.0.0.1 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: ORAWIN (DBID=3462152886)
RMAN> CONVERT DATAFILE 
'C:\Temp\sales_101.dbf',
'C:\Temp\sales_201.dbf'
TO PLATFORM="Microsoft Windows IA (32-bit)"
FROM PLATFORM="Solaris[tm] OE (32-bit)"
DB_FILE_NAME_CONVERT=
'C:\Temp\', 'C:\app\orauser\oradata\orawin\'
PARALLELISM=4;

impdp system dumpfile=expdat.dmp directory=data_pump_dir transport_datafiles=c:\app\orauser\oradata\orawin\sales_101.dbf,c:\app\orauser\oradata\orawin\sales_201.db remap_schema=sales1:crm1  remap_schema=sales2:crm2 logfile=tts_import.log;

•If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit without first shutting down the database.
•If the value of DB_FILES is too high, memory is unnecessarily consumed.

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
Note:
To use this form. of the ALTER DATABASE statement, the database must be in ARCHIVELOG mode. This requirement prevents you from accidentally losing the datafile, since taking the datafile offline while in NOARCHIVELOG mode is likely to result in losing the file.

•The OFFLINE keyword causes the database to mark the datafile OFFLINE, whether or not it is corrupted, so that you can open the database.
•The FOR DROP keywords mark the datafile for subsequent dropping. Such a datafile can no longer be brought back online.
Note:
This operation does not actually drop the datafile. It remains in the data dictionary, and you must drop it yourself using one of the following methods: 
◦An ALTER TABLESPACE ... DROP DATAFILE statement.
After an OFFLINE FOR DROP, this method works for dictionary managed tablespaces only.
◦A DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES statement
◦If the preceding methods fail, an operating system command to delete the datafile. This is the least desirable method, as it leaves references to the datafile in the data dictionary and control files.

CREATE DIRECTORY SOURCE_DIR AS '/usr/admin/source';
CREATE DIRECTORY DEST_DIR AS '/usr/admin/destination';
GRANT EXECUTE ON DBMS_FILE_TRANSFER TO strmadmin;
GRANT READ ON DIRECTORY source_dir TO strmadmin;
GRANT WRITE ON DIRECTORY dest_dir TO strmadmin;
CONNECT strmadmin
BEGIN
  DBMS_FILE_TRANSFER.COPY_FILE(
              source_directory_object            =>  'SOURCE_DIR',
              source_file_name                          =>  'db1.dat',
              destination_directory_object  =>  'DEST_DIR',
              destination_file_name                =>  'db1_copy.dat');
END;
/
Caution:
Do not use the DBMS_FILE_TRANSFER package to copy or transfer a file that is being modified by a database because doing so may result in an inconsistent file.

DBA_DATA_FILES Provides descriptive information about each datafile, including the tablespace to which it belongs and the file ID. The file ID can be used to join with other views for detail information. 
DBA_EXTENTS USER_EXTENTS
  DBA view describes the extents comprising all segments in the database. Contains the file ID of the datafile containing the extent. USER view describes extents of the segments belonging to objects owned by the current user. 
DBA_FREE_SPACE USER_FREE_SPACE
  DBA view lists the free extents in all tablespaces. Includes the file ID of the datafile containing the extent. USER view lists the free extents in the tablespaces accessible to the current user. 
V$DATAFILE Contains datafile information from the control file 
V$DATAFILE_HEADER Contains information from datafile headers

If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.
WARNING:
Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

V$UNDOSTAT Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode. 
V$ROLLSTAT For automatic undo management mode, information reflects behavior. of the undo segments in the undo tablespace 
V$TRANSACTION Contains undo segment information 
DBA_UNDO_EXTENTS Shows the status and size of each extent in the undo tablespace. 
DBA_HIST_UNDOSTAT Contains statistical snapshots of V$UNDOSTAT information. See Oracle Database 2 Day DBA for more information.

---shows how undo space is consumed in the system for the previous 24 hours from the time 14:35:12 on 10/27/2004
SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
                TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
                UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"
                FROM v$UNDOSTAT WHERE rownum <= 144;

DBA_CONSTRAINTS ALL_CONSTRAINTS
USER_CONSTRAINTS
  DBA view describes all constraint definitions in the database. ALL view describes constraint definitions accessible to current user. USER view describes constraint definitions owned by the current user. 
DBA_CONS_COLUMNS ALL_CONS_COLUMNS
USER_CONS_COLUMNS
  DBA view describes all columns in the database that are specified in constraints. ALL view describes only those columns accessible to current user that are specified in constraints. USER view describes only those columns owned by the current user that are specified in constraints.

DBA_OBJECTS ALL_OBJECTS
USER_OBJECTS
  DBA view describes all schema objects in the database. ALL view describes objects accessible to current user. USER view describes objects owned by the current user. 
DBA_CATALOG ALL_CATALOG
USER_CATALOG
  List the name, type, and owner (USER view does not display owner) for all tables, views, synonyms, and sequences in the database. 
DBA_DEPENDENCIES ALL_DEPENDENCIES
USER_DEPENDENCIES
  List all dependencies between procedures, packages, functions, package bodies, and triggers, including dependencies on views without any database links.

---Shrink a table and all of its dependent segments (including BASICFILE LOB segments):
ALTER TABLE employees SHRINK SPACE CASCADE;
---Shrink a BASICFILE LOB segment only:
ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE);
---Shrink a single partition of a partitioned table:
ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;
---Shrink an IOT index segment and the overflow segment:
ALTER TABLE cities SHRINK SPACE CASCADE;
---Shrink an IOT overflow segment only:
ALTER TABLE cities OVERFLOW SHRINK SPACE;

SELECT table_name, segment_created, partition_name FROM user_tab_partitions;

SELECT * FROM RECYCLEBIN;
ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE;
ALTER SESSION SET recyclebin = ON;
ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE;
SELECT object_name, original_name FROM dba_recyclebin WHERE wner = 'HR';
PURGE RECYCLEBIN;
PURGE TABLE BIN$jsleilx392mk2=293$0;
PURGE TABLE int_admin_emp;
FLASHBACK TABLE int_admin_emp TO BEFORE DROP RENAME TO int2_admin_emp;
SELECT object_name, original_name, createtime FROM recyclebin;
ALTER INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;

DBA_TABLES ALL_TABLES
USER_TABLES
  DBA view describes all relational tables in the database. ALL view describes all tables accessible to the user. USER view is restricted to tables owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement. 
DBA_TAB_COLUMNS ALL_TAB_COLUMNS
USER_TAB_COLUMNS
  These views describe the columns of tables, views, and clusters in the database. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement. 
DBA_ALL_TABLES ALL_ALL_TABLES
USER_ALL_TABLES
  These views describe all relational and object tables in the database. Object tables are not specifically discussed in this book. 
DBA_TAB_COMMENTS ALL_TAB_COMMENTS
USER_TAB_COMMENTS
  These views display comments for tables and views. Comments are entered using the COMMENT statement. 
DBA_COL_COMMENTS ALL_COL_COMMENTS
USER_COL_COMMENTS
  These views display comments for table and view columns. Comments are entered using the COMMENT statement. 
DBA_EXTERNAL_TABLES ALL_EXTERNAL_TABLES
USER_EXTERNAL_TABLES
  These views list the specific attributes of external tables in the database. 
DBA_EXTERNAL_LOCATIONS ALL_EXTERNAL_LOCATIONS
USER_EXTERNAL_LOCATIONS
  These views list the data sources for external tables. 
DBA_TAB_HISTOGRAMS ALL_TAB_HISTOGRAMS
USER_TAB_HISTOGRAMS
  These views describe histograms on tables and views. 
DBA_TAB_STATISTICS ALL_TAB_STATISTICS
USER_TAB_STATISTICS
  These views contain optimizer statistics for tables. 
DBA_TAB_COL_STATISTICS ALL_TAB_COL_STATISTICS
USER_TAB_COL_STATISTICS
  These views provide column statistics and histogram information extracted from the related TAB_COLUMNS views. 
DBA_TAB_MODIFICATIONS ALL_TAB_MODIFICATIONS
USER_TAB_MODIFICATIONS
  These views describe tables that have been modified since the last time table statistics were gathered on them. They are not populated immediately, but after a time lapse (usually 3 hours). 
DBA_ENCRYPTED_COLUMNS USER_ENCRYPTED_COLUMNS
ALL_ENCRYPTED_COLUMNS
  These views list table columns that are encrypted, and for each column, lists the encryption algorithm in use. 
DBA_UNUSED_COL_TABS ALL_UNUSED_COL_TABS
USER_UNUSED_COL_TABS
  These views list tables with unused columns, as marked by the ALTER TABLE ... SET UNUSED statement. 
DBA_PARTIAL_DROP_TABS ALL_PARTIAL_DROP_TABS
USER_PARTIAL_DROP_TABS
  These views list tables that have partially completed DROP COLUMN operations. These operations could be incomplete because the operation was interrupted by the user or a system failure.

ALTER INDEX index_name RENAME TO new_name;
ALTER INDEX index MONITORING USAGE;
ALTER INDEX index NOMONITORING USAGE;
---using the ANALYZE INDEX...VALIDATE STRUCTURE statement, and then querying the INDEX_STATS view:
SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'index';

DBA_INDEXES ALL_INDEXES
USER_INDEXES
  DBA view describes indexes on all tables in the database. ALL view describes indexes on all tables accessible to the user. USER view is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement. 
DBA_IND_COLUMNS ALL_IND_COLUMNS
USER_IND_COLUMNS
  These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement. 
DBA_IND_EXPRESSIONS ALL_IND_EXPRESSIONS
USER_IND_EXPRESSIONS
  These views describe the expressions of function-based indexes on tables. 
DBA_IND_STATISTICS ALL_IND_STATISTICS
USER_IND_STATISTICS
  These views contain optimizer statistics for indexes. 
INDEX_STATS Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement. 
INDEX_HISTOGRAM Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement. 
V$OBJECT_USAGE Contains index usage information produced by the ALTER INDEX...MONITORING USAGE functionality.

DBA_CLUSTERS ALL_CLUSTERS
USER_CLUSTERS
  DBA view describes all clusters in the database. ALL view describes all clusters accessible to the user. USER view is restricted to clusters owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement. 
DBA_CLU_COLUMNS USER_CLU_COLUMNS
  These views map table columns to cluster columns

DBA_VIEWS ALL_VIEWS
USER_VIEWS
  DBA view describes all views in the database. ALL view is restricted to views accessible to the current user. USER view is restricted to views owned by the current user. 
DBA_SYNONYMS ALL_SYNONYMS
USER_SYNONYMS
  These views describe synonyms. 
DBA_SEQUENCES ALL_SEQUENCES
USER_SEQUENCES
  These views describe sequences. 
DBA_UPDATABLE_COLUMNS ALL_UPDATABLE_COLUMNS
USER_UPDATABLE_COLUMNS
  These views describe all columns in join views that are updatable.

ADMIN_TABLES Provides administrative functions (create, drop, purge) for repair or orphan key tables. Note: These tables are always created in the SYS schema.
 
CHECK_OBJECT Detects and reports corruptions in a table or index 
DUMP_ORPHAN_KEYS Reports on index entries that point to rows in corrupt data blocks 
FIX_CORRUPT_BLOCKS Marks blocks as software corrupt that have been previously identified as corrupt by the CHECK_OBJECT procedure 
REBUILD_FREELISTS Rebuilds the free lists of the object 
SEGMENT_FIX_STATUS Provides the capability to fix the corrupted state of a bitmap entry when segment space management is AUTO 
SKIP_CORRUPT_BLOCKS When used, ignores blocks marked corrupt during table and index scans. If not used, you get error ORA-01578 when encountering blocks marked corrupt.

---Creating a Job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name                    =>  'update_sales',
    job_type                    =>  'STORED_PROCEDURE',
    job_action                =>  'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
    start_date                =>  '28-APR-08 07.00.00 PM Australia/Sydney',
    repeat_interval      =>  'FREQ=DAILY;INTERVAL=2',
    end_date                    =>  '20-NOV-08 07.00.00 PM Australia/Sydney',
    auto_drop                  =>    FALSE,
    job_class                  =>  'batch_update_jobs',
    comments                    =>  'My new job');
END;
/

V$DATABASE_BLOCK_CORRUPTION

When you specify WITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates

To avoid the overhead of this validation activity, issue the following statement for each constraint before doing the exchange partition operation:
ALTER TABLE table_name
        DISABLE CONSTRAINT constraint_name KEEP INDEX
Then, enable the constraints after the exchange.
If you specify WITHOUT VALIDATION, then you must ensure that the data to be exchanged belongs in the partition you exchange.

ALTER TABLE stocks
      EXCHANGE PARTITION p3 WITH TABLE stock_table_3;

CREATE TABLE t1 (i NUMBER, j NUMBER)
        PARTITION BY HASH(i)
            (PARTITION p1, PARTITION p2);
CREATE TABLE t2 (i NUMBER, j NUMBER)
        PARTITION BY RANGE(j)
        SUBPARTITION BY HASH(i)
              (PARTITION p1 VALUES LESS THAN (10)
                      SUBPARTITION t2_pls1
                      SUBPARTITION t2_pls2,
                PARTITION p2 VALUES LESS THAN (20)
                      SUBPARTITION t2_p2s1
                      SUBPARTITION t2_p2s2));
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1
        WITH VALIDATION;

SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLE_NAME='QUARTERLY_REGIONAL_SALES'
  ORDER BY PARTITION_NAME;

ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;

•USER_TABLES, ALL_TABLES, DBA_TABLES
•USER_PART_TABLES, ALL_PART_TABLES, DBA_PART_TABLES
•USER_OBJECT_TABLES, ALL_OBJECT_TABLES, DBA_OBJECT_TABLES

DBA_PART_TABLES
ALL_PART_TABLES
USER_PART_TABLES
  DBA view displays partitioning information for all partitioned tables in the database. ALL view displays partitioning information for all partitioned tables accessible to the user. USER view is restricted to partitioning information for partitioned tables owned by the user.
 
DBA_TAB_PARTITIONS
ALL_TAB_PARTITIONS
USER_TAB_PARTITIONS
  Display partition-level partitioning information, partition storage parameters, and partition statistics generated by the DBMS_STATS package or the ANALYZE statement.
 
DBA_TAB_SUBPARTITIONS
ALL_TAB_SUBPARTITIONS
USER_TAB_SUBPARTITIONS
  Display subpartition-level partitioning information, subpartition storage parameters, and subpartition statistics generated by the DBMS_STATS package or the ANALYZE statement.
 
DBA_PART_KEY_COLUMNS
ALL_PART_KEY_COLUMNS
USER_PART_KEY_COLUMNS
  Display the partitioning key columns for partitioned tables.
 
DBA_SUBPART_KEY_COLUMNS
ALL_SUBPART_KEY_COLUMNS
USER_SUBPART_KEY_COLUMNS
  Display the subpartitioning key columns for composite-partitioned tables (and local indexes on composite-partitioned tables).
 
DBA_PART_COL_STATISTICS
ALL_PART_COL_STATISTICS
USER_PART_COL_STATISTICS
  Display column statistics and histogram information for the partitions of tables.
 
DBA_SUBPART_COL_STATISTICS
ALL_SUBPART_COL_STATISTICS
USER_SUBPART_COL_STATISTICS
  Display column statistics and histogram information for subpartitions of tables.
 
DBA_PART_HISTOGRAMS
ALL_PART_HISTOGRAMS
USER_PART_HISTOGRAMS
  Display the histogram data (end-points for each histogram) for histograms on table partitions.
 
DBA_SUBPART_HISTOGRAMS
ALL_SUBPART_HISTOGRAMS
USER_SUBPART_HISTOGRAMS
  Display the histogram data (end-points for each histogram) for histograms on table subpartitions.
 
DBA_PART_INDEXES
ALL_PART_INDEXES
USER_PART_INDEXES
  Display partitioning information for partitioned indexes.
 
DBA_IND_PARTITIONS
ALL_IND_PARTITIONS
USER_IND_PARTITIONS
  Display the following for index partitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the DBMS_STATS package or the ANALYZE statement.
 
DBA_IND_SUBPARTITIONS
ALL_IND_SUBPARTITIONS
USER_IND_SUBPARTITIONS
  Display the following information for index subpartitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the DBMS_STATS package or the ANALYZE statement.
 
DBA_SUBPARTITION_TEMPLATES
ALL_SUBPARTITION_TEMPLATES
USER_SUBPARTITION_TEMPLATES
  Display information about existing subpartition templates

•DBA_TABLES and DBA_OBJECT_TABLES
•DBA_TAB_STATISTICS and DBA_TAB_COL_STATISTICS
•DBA_TAB_HISTOGRAMS
•DBA_TAB_COLS
•DBA_COL_GROUP_COLUMNS
•DBA_INDEXES and DBA_IND_STATISTICS
•DBA_CLUSTERS
•DBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONS
•DBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONS
•DBA_PART_COL_STATISTICS
•DBA_PART_HISTOGRAMS
•DBA_SUBPART_COL_STATISTICS
•DBA_SUBPART_HISTOGRAMS

Example 13-1 Viewing Height-Balanced Histogram Statistics
BEGIN
  DBMS_STATS.GATHER_table_STATS ( 
      OWNNAME      => 'OE', 
      TABNAME      => 'INVENTORIES', 
      METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand' );
END;
/

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM 
FROM    USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND';

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE 
FROM    USER_TAB_HISTOGRAMS
WHERE  TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND'
ORDER BY ENDPOINT_NUMBER;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
The database automatically creates frequency histograms instead of height-balanced histograms under the following conditions:
•The number of distinct values is less than or equal to the number of histogram buckets specified (up to 254).
•It is not true that each column value repeats only once.

Example 13-2 Viewing Frequency Histogram Statistics
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS ( 
      OWNNAME      => 'OE', 
      TABNAME      => 'INVENTORIES', 
      METHOD_OPT => 'FOR COLUMNS SIZE 20 warehouse_id' );
END;
/

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM 
FROM    USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'WAREHOUSE_ID';

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER INDEX MONITORING USAGE;

ALL_AUDIT_POLICIES
ALL_AUDIT_POLICY_COLUMNS 
ALL_CLUSTERS
ALL_COLL_TYPES
ALL_COL_COMMENTS
ALL_COL_PENDING_STATS
ALL_COL_PRIVS   
ALL_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD 
ALL_CONSTRAINTS
ALL_CONS_COLUMNS
ALL_CONS_OBJ_COLUMNS
ALL_CONTEXT

ALL_DB_LINKS
ALL_DIRECTORIES
ALL_ERRORS
ALL_ERRORS_AE
ALL_EXTERNAL_LOCATIONS
ALL_EXTERNAL_TABLES
ALL_HISTOGRAMS
ALL_IDENTIFIERS
ALL_INDEXES   
ALL_INDEXTYPES
ALL_IND_COLUMNS
ALL_IND_EXPRESSIONS
ALL_IND_PARTITIONS   
ALL_IND_PENDING_STATS
ALL_IND_STATISTICS   
ALL_IND_SUBPARTITIONS
ALL_INTERNAL_TRIGGERS
ALL_LOG_GROUPS
ALL_LOG_GROUP_COLUMNS 
ALL_MVIEWS

DBA_BASE_TABLE_MVIEWS
ALL_MVIEW_AGGREGATES 
ALL_MVIEW_ANALYSIS
ALL_MVIEW_COMMENTS
ALL_MVIEW_DETAIL_PARTITION
ALL_MVIEW_DETAIL_RELATIONS
ALL_MVIEW_DETAIL_SUBPARTITION
ALL_MVIEW_JOINS
ALL_MVIEW_KEYS
ALL_MVIEW_LOGS
ALL_MVIEW_REFRESH_TIMES
 
ALL_SYNONYMS: Syntax   
ALL_TABLES: Syntax   
ALL_TAB_COLS: Syntax   
ALL_TAB_COLUMNS: Syntax   
ALL_TAB_COL_STATISTICS: Syntax   
ALL_TAB_COMMENTS: Syntax   
ALL_TAB_HISTGRM_PENDING_STATS: Syntax   
ALL_TAB_HISTOGRAMS: Syntax   
ALL_TAB_MODIFICATIONS: Syntax   
ALL_TAB_PARTITIONS: Syntax   
ALL_TAB_PENDING_STATS: Syntax   
ALL_TAB_PRIVS: Syntax   
ALL_TAB_PRIVS_MADE: Syntax   
ALL_TAB_PRIVS_RECD: Syntax   
ALL_TAB_STATISTICS: Syntax   
ALL_TAB_STATS_HISTORY: Syntax   
ALL_TAB_STAT_PREFS: Syntax   
ALL_TAB_SUBPARTITIONS: Syntax 

ALL_TRIGGERS: Syntax   
ALL_TRIGGER_COLS: Syntax   
ALL_TRIGGER_ORDERING: Syntax 

DBA_HIST_ENQUEUE_STAT: Syntax   
DBA_HIST_EVENT_HISTOGRAM: Syntax   
DBA_HIST_EVENT_NAME: Syntax   
DBA_HIST_FILEMETRIC_HISTORY: Syntax   
DBA_HIST_FILESTATXS: Syntax   
DBA_HIST_IC_CLIENT_STATS: Syntax   
DBA_HIST_IC_DEVICE_STATS: Syntax   
DBA_HIST_INSTANCE_RECOVERY: Syntax   
DBA_HIST_INST_CACHE_TRANSFER: Syntax   
DBA_HIST_INTERCONNECT_PINGS: Syntax   
DBA_HIST_IOSTAT_DETAIL: Syntax   
DBA_HIST_IOSTAT_FILETYPE: Syntax   
DBA_HIST_IOSTAT_FILETYPE_NAME: Syntax   
DBA_HIST_IOSTAT_FUNCTION: Syntax   
DBA_HIST_IOSTAT_FUNCTION_NAME: Syntax   
DBA_HIST_JAVA_POOL_ADVICE: Syntax   
DBA_HIST_LATCH: Syntax   
DBA_HIST_LATCH_CHILDREN: Syntax   
DBA_HIST_LATCH_MISSES_SUMMARY: Syntax   
DBA_HIST_LATCH_NAME: Syntax   
DBA_HIST_LATCH_PARENT: Syntax   
DBA_HIST_LIBRARYCACHE: Syntax 

DBA_HIST_LOG: Syntax   
DBA_HIST_MEMORY_RESIZE_OPS: Syntax   
DBA_HIST_MEMORY_TARGET_ADVICE: Syntax   
DBA_HIST_MEM_DYNAMIC_COMP: Syntax   
DBA_HIST_METRIC_NAME: Syntax   
DBA_HIST_MTTR_TARGET_ADVICE: Syntax   
DBA_HIST_OSSTAT: Syntax   
DBA_HIST_OSSTAT_NAME: Syntax   
DBA_HIST_PARAMETER: Syntax   
DBA_HIST_PARAMETER_NAME: Syntax   
DBA_HIST_PGASTAT: Syntax   
DBA_HIST_PGA_TARGET_ADVICE: Syntax   
DBA_HIST_PROCESS_MEM_SUMMARY: Syntax   
DBA_HIST_RESOURCE_LIMIT: Syntax   
DBA_HIST_ROWCACHE_SUMMARY: Syntax   
DBA_HIST_RSRC_CONSUMER_GROUP: Syntax

DBA_HIST_SGA: Syntax   
DBA_HIST_SGASTAT: Syntax   
DBA_HIST_SGA_TARGET_ADVICE: Syntax   
DBA_HIST_SHARED_POOL_ADVICE: Syntax   
DBA_HIST_SHARED_SERVER_SUMMARY: Syntax   
DBA_HIST_SNAPSHOT: Syntax   
DBA_HIST_SNAP_ERROR: Syntax   
DBA_HIST_SQLBIND: Syntax   
DBA_HIST_SQLCOMMAND_NAME: Syntax   
DBA_HIST_SQLSTAT: Syntax   
DBA_HIST_SQLTEXT: Syntax   
DBA_HIST_SQL_BIND_METADATA: Syntax   
DBA_HIST_SQL_PLAN: Syntax   
DBA_HIST_SQL_SUMMARY: Syntax   
DBA_HIST_SQL_WORKAREA_HSTGRM: Syntax   
DBA_HIST_STAT_NAME: Syntax 

DBA_HIST_SYSSTAT: Syntax   
DBA_HIST_SYSTEM_EVENT: Syntax 

DBA_HIST_TABLESPACE_STAT: Syntax   
DBA_HIST_TBSPC_SPACE_USAGE: Syntax   
DBA_HIST_TEMPFILE: Syntax   
DBA_HIST_TEMPSTATXS: Syntax   
DBA_HIST_THREAD: Syntax   
DBA_HIST_TOPLEVELCALL_NAME: Syntax   
DBA_HIST_UNDOSTAT: Syntax   
DBA_HIST_WAITCLASSMET_HISTORY: Syntax   
DBA_HIST_WAITSTAT: Syntax 

DBA_INDEXES: Syntax   
DBA_INDEXTYPES: Syntax 
DBA_IND_COLUMNS: Syntax 
DBA_IND_PARTITIONS: Syntax
DBA_IND_PENDING_STATS: Syntax
DBA_IND_STATISTICS: Syntax   
DBA_IND_SUBPARTITIONS: Syntax

DBA_PROCEDURES: Syntax   
DBA_PROFILES: Syntax   
DBA_SCHEDULER_JOBS: Syntax
DBA_SCHEDULER_JOB_LOG: Syntax   
DBA_SCHEDULER_JOB_ROLES: Syntax   
DBA_SCHEDULER_JOB_RUN_DETAILS: Syntax   
DBA_SCHEDULER_NOTIFICATIONS: Syntax   
DBA_SCHEDULER_PROGRAMS: Syntax 

V$LOCK: Syntax   
V$LOCKED_OBJECT: Syntax   
V$LOCK_ACTIVITY: Syntax   
V$LOCK_TYPE: Syntax 

V$SESSION: Syntax   
V$SESSION_BLOCKERS: Syntax   
V$SESSION_CONNECT_INFO: Syntax   
V$SESSION_CURSOR_CACHE: Syntax   
V$SESSION_EVENT: Syntax   
V$SESSION_FIX_CONTROL: Syntax   
V$SESSION_LONGOPS: Syntax   
V$SESSION_OBJECT_CACHE: Syntax   
V$SESSION_WAIT: Syntax   
V$SESSION_WAIT_CLASS: Syntax   
V$SESSION_WAIT_HISTORY: Syntax

V$SESSTAT: Syntax   
V$SESS_IO: Syntax   
V$SESS_TIME_MODEL: Syntax   
V$SES_OPTIMIZER_ENV: Syntax   
V$SGA: Syntax   
V$SGAINFO: Syntax   
V$SGASTAT: Syntax