oracle表空间的查看

查看存在的表空间

SQL> select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------------------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
CNBUGSDB                       ONLINE    PERMANENT

6 rows selected.

查看oracle数据库表空间对应的数据文件

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/cnbugsdb01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf

查看表空间对应的数据信息

SQL> select file_name,tablespace_name,online_status from dba_data_files;

FILE_NAME                                              TABLESPACE ONLINE_
------------------------------------------------------------ ---------- -------
/u01/app/oracle/oradata/orcl/system01.dbf                SYSTEM     SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf                SYSAUX     ONLINE
/u01/app/oracle/oradata/orcl/cnbugsdb01.dbf              CNBUGSDB   ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf                 USERS      ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf               UNDOTBS1   ONLINE

查看指定用户使用的默认表空间

SQL> select default_tablespace,username from dba_users where username like 'SYS%';

DEFAULT_TABLESPACE   USERNAME
-------------------- --------------------
SYSTEM               SYS
SYSTEM               SYSTEM
USERS                SYSBACKUP
USERS                SYSRAC
USERS                SYSKM
SYSTEM               SYS$UMF
USERS                SYSDG

7 rows selected.

查看SYSTEM默认表空间的使用情况

SQL> select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space where tablespace_name='SYSTEM';

TABLESPACE    FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ---------- ----------
SYSTEM              1     102472     458752         56
SYSTEM              1     103296    3145728        384

发表评论

后才能评论