oracle表空间管理sql,ORACLE 管理，SQL 篇--表空间

查看表大小

有两种含义的表大小：一种是分配给一个表的物理空间数量，而不管空间是否被使用。可以这样查询获得字节数：

select segment_name, bytes

from user_segments

where segment_type = 'TABLE';

或者

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

另一种表实际使用的空间。这样查询：

analyze tableAREAINFOcompute statistics;

select  TABLE_NAME,TABLESPACE_NAME, NUM_ROWS ,AVG_ROW_LEN,  NUM_ROWS*AVG_ROW_LEN

from user_tables

where table_name = 'AREAINFO';

说明：

表名称要大写，红色加粗部分。

查看每个表空间的大小

Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group ByTablespace_Name

看数据库有多少个tablespace

oracle@mmsg:~>sqlplus / as sysdba

SQL*Plus:Release11.1.0.7.0 - Production on星期四7月1 17:37:14 2010

Copyright(c) 1982, 2008, Oracle.  All rightsreserved.

连接到:

OracleDatabase11gEnterprise Edition Release11.1.0.7.0 -64bit Production

Withthe Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>set wrap on

SQL>set linesize 700

SQL>select * from dba_tablespaces;

TABLESPACE_NAME                                              BLOCK_SIZEINITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS                 CONTENTS           LOGGING            FORCE_ EXTENT_MANAGEMENT    ALLOCATION_TYPE    PLUGGE SEGMENT_SPAC DEF_TAB_COMPRESSRETENTION                  BIGFILPREDICATE_EVAL ENCRYP COMPRESS_FOR

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

SYSTEM                                                             8192          65536                       1  2147483645 2147483645                       65536 ONLINE             PERMANENT          LOGGING            NO     LOCAL                SYSTEM             NO     MANUAL  DISABLED         NOT APPLY              NO     HOST           NO

SYSAUX                                                            8192          65536                       1  2147483645 2147483645                       65536 ONLINE             PERMANENT         LOGGING            NO     LOCAL                SYSTEM             NO     AUTO    DISABLED         NOT APPLY              NO     HOST           NO

UNDOTBS1                                                          8192          65536                       1 2147483645 2147483645                       65536 ONLINE             UNDO               LOGGING            NO     LOCAL                SYSTEM             NO     MANUAL  DISABLED         NOGUARANTEE            NO     HOST           NO

TEMP                                                               8192        1048576     1048576           1             2147483645                0    1048576 ONLINE             TEMPORARY          NOLOGGING          NO    LOCAL                UNIFORM            NO    MANUAL   DISABLED         NOT APPLY              NO     HOST           NO

USERS                                                             8192          65536                       1  2147483645 2147483645                       65536 ONLINE             PERMANENT          LOGGING            NO     LOCAL                SYSTEM             NO     AUTO    DISABLED         NOT APPLY              NO     HOST           NO

MMSG                                                              8192          65536                       1  2147483645 2147483645                       65536 ONLINE             PERMANENT          LOGGING            NO     LOCAL                SYSTEM             NO     AUTO    DISABLED         NOT APPLY              NO     HOST          NO

MMSG_TMP                                                          8192        1048576     1048576           1             2147483645                0    1048576 ONLINE             TEMPORARY          NOLOGGING          NO    LOCAL                UNIFORM            NO     MANUAL  DISABLED         NOT APPLY              NO     HOST           NO

已选择7行。

SQL>

SQL>select instance_number,instance_name,status fromv$instance;

INSTANCE_NUMBERINSTANCE_NAME    STATUS

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

1 mmsgdb           OPEN

SQL>