管理表空间（十一）--显示表空间和数据文件

 

1)显示表空间和数据文件信息

00:03:23 SQL> col tablespace_name for a20or a20

02:17:46 SQL> select tablespace_name,status ,contents,bigfile

02:18:03   2   from dba_tablespaces;

TABLESPACE_NAME      STATUS    CONTENTS  BIG

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

SYSTEM               ONLINE    PERMANENT NO

UNDOTBS1             ONLINE    UNDO      NO

SYSAUX               ONLINE    PERMANENT NO

TEMP                 ONLINE    TEMPORARY NO

USERS                ONLINE    PERMANENT NO

EXAMPLE              ONLINE    PERMANENT NO

USERS01              ONLINE    PERMANENT NO

USERS02              ONLINE    PERMANENT NO

UNDOTBS02            ONLINE    UNDO      NO

TEMP01               ONLINE    TEMPORARY NO

TEMP02               ONLINE    TEMPORARY YES

TEMP03               ONLINE    TEMPORARY NO

TBSP_16K             ONLINE    PERMANENT NO

13 rows selected.

2）显示表空间的编号和名称

02:19:40 SQL> select * from v$tablespace;

TS# NAME                           INC BIG FLA ENC

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

0 SYSTEM                         YES NO  YES

1 UNDOTBS1                       YES NO  YES

2 SYSAUX                         YES NO  YES

4 USERS                          YES NO  YES

3 TEMP                           NO  NO  YES

6 EXAMPLE                        YES NO  YES

7 USERS01                        YES NO  YES

8 USERS02                        YES NO  YES

10 UNDOTBS02                      YES NO  YES

11 TEMP01                         NO  NO  YES

12 TEMP02                         NO  YES YES

14 TEMP03                         NO  NO  YES

17 TBSP_16K                       YES NO  YES

13 rows selected.

Inc  用于标识当使用RMAN进行完全数据库备份时是否包含该表空间。

Fla  用于标识表空间是否支持FLASHBACK DATABASE 特征。

3) 显示空间所包含的数据文件

02:19:56 SQL> col file_name for a40 or a40

02:23:05 SQL> col file_name for a40

02:23:08 SQL> select file_name,bytes,maxbytes,autoextensible

02:23:29   2   from dba_data_files

02:23:44   3    where tablespace_name='USERS01';

FILE_NAME                                     BYTES   MAXBYTES AUT

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

/disk1/oradata/orcl/user01.dbf             10485760          0 NO

02:24:05 SQL>

4）显示数据文件动态信息

02:23:05 SQL> col file_name for a40

02:23:08 SQL> select file_name,bytes,maxbytes,autoextensible

02:23:29   2   from dba_data_files

02:23:44   3    where tablespace_name='USERS01';

FILE_NAME                                     BYTES   MAXBYTES AUT

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

/disk1/oradata/orcl/user01.dbf             10485760          0 NO

02:24:05 SQL> COL NAME FORMAT A25T A25

02:25:49 SQL> COL NAME FORMAT A25

02:25:51 SQL> select name,checkpoint_time,checkpoint_change#

02:26:13   2   from v$datafile;

NAME                      CHECKPOINT_TIME     CHECKPOINT_CHANGE#

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

/u01/app/oracle/oradata/o 2011-02-22 00:03:08             857333

rcl/system01.dbf

/u01/app/oracle/oradata/o 2011-02-22 00:03:08             857333

rcl/undotbs01.dbf

/u01/app/oracle/oradata/o 2011-02-22 00:03:08             857333

rcl/sysaux01.dbf

/u01/app/oracle/oradata/o 2011-02-22 00:03:08             857333

rcl/users01.dbf

/u01/app/oracle/oradata/o 2011-02-22 00:03:08             857333

rcl/example01.dbf

/disk1/oradata/orcl/user0 2011-02-22 00:03:08             857333

1.dbf

NAME                      CHECKPOINT_TIME     CHECKPOINT_CHANGE#

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

/disk2/oradata/orcl/user0 2011-02-22 00:03:08             857333

1.dbf

/disk2/oradata/orcl/tbs_1 2011-02-22 00:03:08             857333

6k02.dbf

/disk2/oradata/orcl/undo0 2011-02-22 00:03:08             857333

2.dbf

/disk2/oradata/orcl/tbs_1 2011-02-22 00:03:08             857333

6k01.dbf

10 rows selected.

5）显示临时表空间组的信息

02:26:20 SQL> select tablespace_name from dba_tablespace_groups

02:28:19   2    where group_name='TEMGRP01';

TABLESPACE_NAME

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

TEMP01

TEMP02

TEMP03

6)显示临时表空间所包含的临时文件

02:30:13 SQL> select file_name,bytes,maxbytes ,autoextensible

02:30:22   2   from dba_temp_files;

FILE_NAME                                     BYTES   MAXBYTES AUT

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

/u01/app/oracle/oradata/orcl/temp01.dbf    20971520 3.4360E+10 YES

/disk3/oradata/orcl/temp01.dbf              5242880          0 NO

/disk3/oradata/orcl/temp02.dbf              5242880          0 NO

/disk3/oradata/orcl/temp03.dbf              5242880          0 NO

7）显示临时文件动态信息

02:30:24 SQL> select name,status ,enabled

02:31:26   2   from v$tempfile;

NAME                      STATUS  ENABLED

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

/u01/app/oracle/oradata/o ONLINE  READ WRITE

rcl/temp01.dbf

/disk3/oradata/orcl/temp0 ONLINE  READ WRITE

1.dbf

/disk3/oradata/orcl/temp0 ONLINE  READ WRITE

2.dbf

/disk3/oradata/orcl/temp0 ONLINE  READ WRITE

3.dbf