oracle系统相关表摘要

Oracle在线文档

http://www.oracle.com/pls/db111/homepage

1.查看oracle数据库版本信息

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0    Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

 

2.查看临时文件相关信息

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_         TS#     RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
     1           2151 16-APR-09           3      1 OFFLINE READ WRITE
     0        0      20971520     8192
/u01/app/oracle/oradata/orcl/temp01.dbf

 

3.查看临时表空间相关信息

SQL> select * from v$tablespace;

       TS# NAME               INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
     0 SYSTEM              YES NO  YES
     1 SYSAUX              YES NO  YES
    11 TEST               YES NO  YES
     3 TEMP               NO  NO  YES
     4 USERS              YES NO  YES
     9 UNDOTEMP              YES NO  YES

4.查看数据文件相关信息

SQL> select file#,ts#,status,blocks,block_size,name from v$datafile

FILE#  TS# STATUS   BLOCKS BLOCK_SIZE   NAME

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

1    0    SYSTEM    2227200    8192    /u01/app/oracle/oradata/orcl/system01.dbf
2    1    ONLINE    193480    8192    /u01/app/oracle/oradata/orcl/sysaux01.dbf
3    4    ONLINE    4194302    8192    /u01/app/oracle/oradata/orcl/users02.dbf
4    4    ONLINE    4194302    8192    /u01/app/oracle/oradata/orcl/users01.dbf
5    4    ONLINE    1190528    8192    /u01/app/oracle/oradata/orcl/users03
6    11    ONLINE    1280    8192    /u01/app/oracle/product/11.1.0/db_1/dbs/C:oracleoradatadbtest.dbf
7    4    ONLINE    963584    8192    /u01/app/oracle/oradata/orcl/users03.dbf
8    9    ONLINE    131072    8192    /u01/app/oracle/oradata/orcl/undotbs01.dbf

5.更直观地查看表空间所使用的数据文件

SQL> select t.name,d.name,d.status from v$tablespace t,v$datafile d where d.ts#=t.ts# ;

NAME       NAME_1                                                       STATUS
------        ------                                                             ------
SYSTEM    /u01/app/oracle/oradata/orcl/system01.dbf    SYSTEM
SYSAUX    /u01/app/oracle/oradata/orcl/sysaux01.dbf    ONLINE
USERS    /u01/app/oracle/oradata/orcl/users02.dbf    ONLINE
USERS    /u01/app/oracle/oradata/orcl/users01.dbf    ONLINE
USERS    /u01/app/oracle/oradata/orcl/users03.dbf    ONLINE
USERS    /u01/app/oracle/oradata/orcl/users03    ONLINE
UNDOTEMP    /u01/app/oracle/oradata/orcl/undotbs01.dbf    ONLINE
TEST    /u01/app/oracle/product/11.1.0/db_1/dbs/C:oracleoradatadbtest.dbf    ONLINE