oracle 如何查看一个表属于哪一个数据文件,如何查看某一表空间的数据文件都分布了哪些数据对象...

$ sqlplus /nolog

SQL*Plus: Release 10.1.0.3.0 - Production on Thu Jun 7 10:02:20 2007

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> conn / as sysdba;

Connected.

找到表空间对应的数据文件

SQL> desc v$datafile;

Name                                      Null?    Type

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

FILE#                                              NUMBER

CREATION_CHANGE#                                   NUMBER

CREATION_TIME                                      DATE

TS#                                                NUMBER

RFILE#                                             NUMBER

STATUS                                             VARCHAR2(7)

ENABLED                                            VARCHAR2(10)

CHECKPOINT_CHANGE#                                 NUMBER

CHECKPOINT_TIME                                    DATE

UNRECOVERABLE_CHANGE#                              NUMBER

UNRECOVERABLE_TIME                                 DATE

LAST_CHANGE#                                       NUMBER

LAST_TIME                                          DATE

OFFLINE_CHANGE#                                    NUMBER

ONLINE_CHANGE#                                     NUMBER

ONLINE_TIME                                        DATE

BYTES                                              NUMBER

BLOCKS                                             NUMBER

CREATE_BYTES                                       NUMBER

BLOCK_SIZE                                         NUMBER

NAME                                               VARCHAR2(513)

PLUGGED_IN                                         NUMBER

BLOCK1_OFFSET                                      NUMBER

AUX_NAME                                           VARCHAR2(513)

SQL> set pagesize 200;

SQL> col name for a60

SQL> select file#, name from v$datafile;

FILE# NAME

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

1 /oracle/app/oracle/oradata/csdb1/system01.dbf

2 /oracle/app/oracle/oradata/csdb1/undotbs01.dbf

3 /oracle/app/oracle/oradata/csdb1/sysaux01.dbf

4 /oracle/app/oracle/oradata/csdb1/users01.dbf

5 /oracle/app/oracle/oradata/csdb1/example01.dbf

6 /oracle/app/oracle/oradata/csdb1/river01.dbf

7 /oracle/app/oracle/oradata/csdb1/jyz1.dbf

8 /oracle/app/oracle/oradata/csdb1/river02.dbf

9 /oracle/app/oracle/oradata/csdb1/MONITOR.dbf

10 /oracle/app/oracle/oradata/csdb1/EXPERT.dbf

11 /oracle/app/oracle/oradata/csdb1/GAS.dbf

12 /oracle/app/oracle/oradata/csdb1/BRAINBANK.dbf

13 /oracle/app/oracle/oradata/csdb1/DATASHARE.dbf

14 /oracle/app/oracle/oradata/csdb1/EXPERTTMP.dbf

15 /oracle/app/oracle/oradata/csdb1/SDE.dbf

16 /oracle/app/oracle/oradata/csdb1/NSDCMONITOR.dbf

16 rows selected.

查看数据文件user01.dbf都分布了哪些数据对象

SQL> desc dba_extents;

Name                                      Null?    Type

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

OWNER                                              VARCHAR2(30)

SEGMENT_NAME                                       VARCHAR2(81)

PARTITION_NAME                                     VARCHAR2(30)

SEGMENT_TYPE                                       VARCHAR2(18)

TABLESPACE_NAME                                    VARCHAR2(30)

EXTENT_ID                                          NUMBER

FILE_ID                                            NUMBER

BLOCK_ID                                           NUMBER

BYTES                                              NUMBER

BLOCKS                                             NUMBER

RELATIVE_FNO                                       NUMBER

SQL> col owner for a20

SQL> col segment_name for a40

SQL> col segment_type for a20

SQL> set linesize 120

SQL> select owner, segment_name, segment_type from dba_extents where file_id=4;

OWNER                SEGMENT_NAME                             SEGMENT_TYPE

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

SCOTT                DEPT                                     TABLE

SCOTT                EMP                                      TABLE

SCOTT                BONUS                                    TABLE

SCOTT                SALGRADE                                 TABLE

OE                   PURCHASEORDERS                           TABLE

OE                   PURCHASEORDERS                           TABLE

OE                   PURCHASEORDERS                           TABLE

MONITOR              USER_INFO                                TABLE

MONITOR              USER_MPOINT                              TABLE

MONITOR              USER_ROLE                                TABLE

...

...

BRAINBANK            SYS_LOB0000062336C00015$$                LOBSEGMENT

BRAINBANK            SYS_LOB0000062594C00021$$                LOBSEGMENT

EXPERTTMP            SYS_LOB0000063244C00013$$                LOBSEGMENT

EXPERTTMP            SYS_LOB0000063244C00013$$                LOBSEGMENT

EXPERTTMP            SYS_LOB0000063285C00008$$                LOBSEGMENT

EXPERTTMP            SYS_LOB0000063285C00008$$                LOBSEGMENT

EXPERTTMP            SYS_LOB0000063285C00008$$                LOBSEGMENT

680 rows selected.