查看Oracle 10g 视图
1、 查看数据库的名字和归档状态
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
HB130000 ARCHIVELOG
2、 查看数据库的instance名字和状态
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
hb130000 OPEN
3、查看表空间名称、状态和治理方式
SQL> select tablespace_name,status,extent_management from dba_tablespaces;
TABLESPACE_NAME STATUS EXTENT_MAN
------------------------------ --------- ----------
SYSTEM ONLINE LOCAL
UNDOTBS1 ONLINE LOCAL
SYSAUX ONLINE LOCAL
TEMP ONLINE LOCAL
USERS ONLINE LOCAL
GFB ONLINE LOCAL
YSZX130000002004 ONLINE LOCAL
RMAN ONLINE LOCAL
8 rows selected.
4、查看控制文件的位置和名字
SQL> select name from v$controlfile;
NAME
/oracle/app/oracle/OraHome/dbs/hb130000/control01.ctl
/oradata/hb130000/control02.ctl
/oradata/hb130000/control03.ctl
5、查看日志文件的组名、成员数量、状态和大小
SQL> select group#,members,bytes,status from v$log;
GROUP# MEMBERS BYTES STATUS
---------- ---------- ---------- ----------------
1 2 52428800 INACTIVE
2 2 52428800 INACTIVE
3 2 52428800 CURRENT
6、查看日志文件的位置
SQL> select group#,member from v$logfile;
GROUP#
----------
MEMBER
3
/oradata/hb130000/redo03.log
3
/oracle/app/oracle/OraHome/dbs/hb130000/redo03.rdo
2
/oradata/hb130000/redo02.log
GROUP#
----------
MEMBER
2
/oracle/app/oracle/OraHome/dbs/hb130000/redo02.rdo
1
/oradata/hb130000/redo01.log
1
/oracle/app/oracle/OraHome/dbs/hb130000/redo01.rdo
6 rows selected.
7、查看数据文件的位置和大小
SQL> select name,bytes from v$datafile;
NAME
BYTES
----------
/oradata/hb130000/system01.dbf
492830720
/oradata/hb130000/undotbs01.dbf
41943040
/oradata/hb130000/sysaux01.dbf
346030080
NAME
BYTES
----------
/oradata/hb130000/users01.dbf
5242880
/oradata/hb130000/GFB.DBF
20971520
/oradata/hb130000/YSZX130000002004.DBF
176160768
NAME
BYTES
----------
/oradata/hb130000/rman.dbf
104857600
7 rows selected.
8、查看用户拥有的对象和类型
SQL> select object_name,object_type from dba_objects where owner='SCOTT';
OBJECT_NAME
OBJECT_TYPE
--------------------------------------------------------------------------------
------------------------------------------------ -------------------
DEPT
TABLE
PK_DEPT
INDEX
EMP
TABLE
PK_EMP
INDEX
BONUS
TABLE
SALGRADE
TABLE
6 rows selected.
9、查看临时文件的信息
SQL> select name,bytes from v$tempfile;
NAME
BYTES
----------
/oradata/hb130000/temp01.dbf
20971520
10、查看归档日志信息
SQL> select name, FIRST_CHANGE#,next_change# from v$archived_log;
NAME
--------------------------------------------------------------------------------
FIRST_CHANGE# NEXT_CHANGE#
------------- ------------
/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_53_532853380.dbf
1669178 1707392
/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_54_532853380.dbf
1707392 1734524
/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_55_532853380.dbf
1734524 1772848
NAME
--------------------------------------------------------------------------------
FIRST_CHANGE# NEXT_CHANGE#
------------- ------------
/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_56_532853380.dbf
1772848 1798092
/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_57_532853380.dbf
1798092 1831478
/oracle/app/oracle/OraHome/dbs/archive/hb130000_1_58_532853380.dbf
1831478 1865033