视图杂记3

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', /* every other day */
   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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22308399/viewspace-750703/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22308399/viewspace-750703/