$ 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.