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.owner
select 'alter table ' || t.table_name || ' move tablespace bip_ts;' from user_all_tables t
select '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 dual
select 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);