oracle 常用的语句,oracle 常用语句 .

oracle 常用语句

http://www.oschina.net/code/snippet_121156_8968

--查询表的大小

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;