oracle 常用语句 oracle --查询表的大小select t.owner, t.segment_name, (sum(t.blocks) * 8) / 1024 || 'm' as s, t.segment_type from dba_segments t where t.owner = 'user_name' group by t.owner, t.segment_name, t.segment_type order by t.owner, s
oracle 常用语句
oracle --查询表的大小select t.owner, t.segment_name, (sum(t.blocks) * 8) / 1024 || 'm' as s, t.segment_type from dba_segments t where t.owner = 'user_name' group by t.owner, t.segment_name, t.segment_type order by t.owner, s desc--修改表所在表空间select t.owner, t.segment_name, t.tablespace_name --, sum(t.blocks) from dba_segments t where t.owner = 'user_name'order by t.ownerselect 'alter table ' || t.table_name || ' move tablespace bip_ts;' from user_all_tables tselect 'alter index '|| index_name ||' rebuild tablespace bip_ts;' from user_indexes; --查看索引是否生效 select index_name, index_type, tablespace_name, table_type, status from user_indexes t where t.tablespace_name = 'tablespacename' and status = 'unusable'-- 清空回收站purge recyclebin;--查询建表语句select dbms_metadata.get_ddl('table','unieap_report_category') from dualselect dbms_metadata.get_ddl('tablespace','bip_ts') from dual--获取授权信息select dbms_metadata.get_granted_ddl('system_grant') from dual;--查询表空间使用率的语句select a.a1 表空间名称, c.c2 类型, c.c3 区管理, b.b2 / 1024 / 1024 表空间大小m, (b.b2 - a.a2) / 1024 / 1024 已使用m, substr((b.b2 - a.a2) / b.b2 * 100, 1, 5) 利用率 from (select tablespace_name a1, sum(nvl(bytes, 0)) a2 from dba_free_space group by tablespace_name) a, (select tablespace_name b1, sum(bytes) b2 from dba_data_files group by tablespace_name) b, (select tablespace_name c1, contents c2, extent_management c3 from dba_tablespaces) c where a.a1 = b.b1 and c.c1 = b.b1;--修改分区名alter table table_name rename partition old_partition_name to new_partition_name;--查询分区脚本select table_name ,partition_name from user_tab_partitions where table_name like '%'select table_name ,partition_name from user_tab_partitions where table_name like '%'--查询索引脚本select index_name,table_name from user_indexes where index_name like '%'--查看oracle连接用户select s.username,s.machine from v$session s where s.status = 'active'--查看oracle最大连接数select value from v$parameter where name = 'processes' --修改最大连接数:alter system set processes = 300 scope = spfile;--给表添加字段alter table table_name add (col_name varchar2(32));--修改表字段alter table table_name modify (col_name datatype [default value][null/not null]);--删除表字段alter table table_name drop (col_name);