如何计算一个表所占用的空间
SQL> select sum(bytes)/1024/1024 MB from user_segments where segment_name='Table_name';
MB
----------
.4375
在user_tables中有表平均行长度和表行数的字段,所以两个之积即为表中数据所占的空间大小。
SQL> select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,NUM_ROWS*AVG_ROW_LEN from user_tables where table_name='T';
TABLE_NAME NUM_ROWS AVG_ROW_LEN NUM_ROWS*AVG_ROW_LEN
------------------------------ ---------- ----------- --------------------
T
为什么查出来什么都没有呢?因为在CBO模式下,表没有进行分析,没有统计信息.
SQL> analyze table t estimate statistics sample 5 percent; <-对该表进行分析
Table analyzed.
再次运行查询,即可得出:
SQL> select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,NUM_ROWS*AVG_ROW_LEN from user_tables where table_name='T';
TABLE_NAME NUM_ROWS AVG_ROW_LEN NUM_ROWS*AVG_ROW_LEN
------------------------------ ---------- ----------- --------------------
T 10000 35 350000
SQL> select 350000/1024/1024 from dual;
350000/1024/1024
----------------
.333786011
提示符修改成用户@实例的形式,可以直接看出当前的用户名和所在的实例,防止在多实例的时候出错,也非常方便
请看设置方法:
在 $ORACLE/sqlplus/admin目录下glogin.sql中增加如下语句即可:
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1,
decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on
如何得到数据库中表,索引的定义
SQL> select DBMS_METADATA.get_ddl('TABLE','DUAL') from dual;