视图杂记2

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;

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

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