查看表大小
有兩種含義的表大锌：一種是分配給一個(gè)表的物理空間數(shù)量，而不管空間是否被使用屁奏⊙矣埽可以這樣查詢獲得字節(jié)數(shù)：
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
另一種表實(shí)際使用的空間。這樣查詢：
analyze table AREAINFOcompute statistics;
select??TABLE_NAME,TABLESPACE_NAME, NUM_ROWS ,AVG_ROW_LEN,? NUM_ROWS*AVG_ROW_LEN??
from user_tables
where table_name = 'AREAINFO';
說明：
表名稱要大寫，紅色加粗部分勇边。
查看每個(gè)表空間的大小
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group ByTablespace_Name
看數(shù)據(jù)庫(kù)有多少個(gè)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>
如何查有多少個(gè)數(shù)據(jù)庫(kù)實(shí)例
SQL>select instance_number,instance_name,status fromv$instance;
INSTANCE_NUMBERINSTANCE_NAME??? STATUS
------------------------------- ------------
????????????? 1 mmsgdb?????????? OPEN
SQL>
