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


CUUG

更多oracle视频教程请点击：http://crm2.qq.com/page/portalpage/wpa.php?uin=800060152&f=1&ty=1&aty=0&a=&from=6