时间:2021-07-01 10:21:17 帮助过:24人阅读
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 常用语句--查询表的大小 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);