怎么检查某一表空间的数据文件都散布了哪些数据目标51CTO博客 - 亚美娱乐

怎么检查某一表空间的数据文件都散布了哪些数据目标51CTO博客

2019-01-04 21:35:54 | 作者: 光济 | 标签: 宋体,数据文件,找到 | 浏览: 1563

$ 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.
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表亚美娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章