$ 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.
©著作权归作者所有：来自51CTO博客作者unix1oracle的原创作品，如需转载，请注明出处，否则将追究法律责任
职场SQL数据oracle