查看自己所拥有的对象和对象所占得大小:
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;
从大到小: 表空间,段,区,块 tablespace segment extend block
查看用户默认的表空间.
select username,default_tablespace from dba_users where username='';
查看某一表空间剩余的容量:
SELECT SUM(BYTES)/1024/1024 FROM dba_free_space WHERE tablespace_name=''
将一个表从一个表空间迁移到另一个表空间
alter table table_name move tablespace another_tablespace_name;
查询表的索引
select * from user_ind_columns where table_name='';
重构索引(索引会自己查找表所在的表空间):
alter index index_name rebuild;
查询自己有table, package, procedure,function等:
select * from all_objects where owner='scheme_name';
分析表:
analyze table table_name compute statistics; 对数据做全部分析
analyze table table_name estimate statistics; 采样分析
分析索引:
analyze index index_name compute statistics; 对数据做全部分析
analyze index index_name estimate statistics; 采样分析
查看一个表的表空间:
select * from user_tables where table_name='BSR58824_SMRY1_TMP1_2009';
创建一个表:采用一个表A的结构,并增加一些列:
create table
as
select m.*,
cast(null as varchar2(13)) column_name
from A m
where 1=2;
查看表空间下的所有表:
select table_name from all_tables where tablespace_name='大写';
在unix里查看一个文件的前二行:
head -2 1.txt
select owner, to_char(sum(a.bytes)/power(2,30),'999,990.00') gb_used
from dba_segments a
where a.tablespace_name like '%%'
group by owner
order by gb_used desc
select to_char((a.bytes)/power(2,30),'999,990.00') gb_used, a.*
from dba_segments a
where owner='shema name' and segment_name like '%%'
order by gb_used desc
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;
从大到小: 表空间,段,区,块 tablespace segment extend block
查看用户默认的表空间.
select username,default_tablespace from dba_users where username='';
查看某一表空间剩余的容量:
SELECT SUM(BYTES)/1024/1024 FROM dba_free_space WHERE tablespace_name=''
将一个表从一个表空间迁移到另一个表空间
alter table table_name move tablespace another_tablespace_name;
查询表的索引
select * from user_ind_columns where table_name='';
重构索引(索引会自己查找表所在的表空间):
alter index index_name rebuild;
查询自己有table, package, procedure,function等:
select * from all_objects where owner='scheme_name';
分析表:
analyze table table_name compute statistics; 对数据做全部分析
analyze table table_name estimate statistics; 采样分析
分析索引:
analyze index index_name compute statistics; 对数据做全部分析
analyze index index_name estimate statistics; 采样分析
查看一个表的表空间:
select * from user_tables where table_name='BSR58824_SMRY1_TMP1_2009';
创建一个表:采用一个表A的结构,并增加一些列:
create table
as
select m.*,
cast(null as varchar2(13)) column_name
from A m
where 1=2;
查看表空间下的所有表:
select table_name from all_tables where tablespace_name='大写';
在unix里查看一个文件的前二行:
head -2 1.txt
select owner, to_char(sum(a.bytes)/power(2,30),'999,990.00') gb_used
from dba_segments a
where a.tablespace_name like '%%'
group by owner
order by gb_used desc
select to_char((a.bytes)/power(2,30),'999,990.00') gb_used, a.*
from dba_segments a
where owner='shema name' and segment_name like '%%'
order by gb_used desc