ORACLE常用SQL集锦

–分析器数据表
select ‘analyze table ‘||t.TABLE_NAME||’ compute statistics ;’ from user_tables t;
–验证表数据量，统计（1485）
select t.TABLE_NAME,t.TABLESPACE_NAME,t1.COMMENTS,t.NUM_ROWS from user_tables t,user_tab_comments t1
where t.TABLE_NAME = t1.TABLE_NAME and t.TABLE_NAME like ‘T0%’; --308

–导出表数据语句
select ‘exp aml/aml rows=y compress=n constraints=n file=’||table_name||’.dmp tables=’||table_name exps
from tabs
where table_name not like ‘TMP%’
and table_name not like ‘T6%’
and table_name not like ‘%LOG’
and table_name not like ‘%MID’
and table_name not like ‘%MID1’
and table_name not like ‘%TEMP’
and table_name not like ‘%TEMP1’
and table_name not like ‘%TEMP2’
and table_name not like ‘TEMP%’
order by table_name;

–导入表数据语句
select ‘imp aml/aml fromuser=aml touser=aml rows=y ignore=y statistics=none commit=y file=’||table_name||’.dmp ’ imps
from tabs
where table_name not like ‘TMP%’
and table_name not like ‘T6%’
and table_name not like ‘%LOG’
and table_name not like ‘%MID’
and table_name not like ‘%MID1’
and table_name not like ‘%TEMP’
and table_name not like ‘%TEMP1’
and table_name not like ‘%TEMP2’
and table_name not like ‘TEMP%’
order by table_name;

–移动表到指定表空间
SELECT ‘ALTER TABLE AML.’||T.TABLE_NAME||’ MOVE TABLESPACE TSDAT01;’ FROM USER_TABLES T WHERE
( T.TABLE_NAME LIKE ‘T0%’ OR T.TABLE_NAME LIKE ‘T18%’ OR T.TABLE_NAME LIKE ‘T21%’ OR T.TABLE_NAME LIKE ‘T87%’ OR T.TABLE_NAME LIKE ‘T23%’
OR T.TABLE_NAME LIKE ‘T31%’) AND T.TABLE_NAME NOT LIKE ‘T07%’;
SELECT ‘ALTER TABLE AML.’||T.TABLE_NAME||’ MOVE TABLESPACE TSDAT02;’ FROM USER_TABLES T WHERE
(T.TABLE_NAME LIKE ‘T07%’ OR T.TABLE_NAME LIKE ‘T37%’ OR T.TABLE_NAME LIKE ‘T27%’ OR T.TABLE_NAME LIKE ‘M%’);
SELECT ‘ALTER TABLE AML.’||T.TABLE_NAME||’ MOVE TABLESPACE TSDAT03;’ FROM USER_TABLES T WHERE T.TABLE_NAME LIKE ‘T47%’ OR T.TABLE_NAME LIKE ‘CZ%’ OR T.TABLE_NAME LIKE ‘ZH%’;
SELECT ‘ALTER TABLE AML.’||T.TABLE_NAME||’ MOVE TABLESPACE TSDAT05;’ FROM USER_TABLES T WHERE T.TABLE_NAME LIKE ‘T67%’ OR T.TABLE_NAME LIKE ‘T68%’
OR T.TABLE_NAME LIKE ‘T64%’ OR T.TABLE_NAME LIKE ‘T65%’ OR T.TABLE_NAME LIKE ‘TMP%’;

–移动索引到指定索引表空间
TSIND01

SELECT ‘ALTER INDEX ‘||T.INDEX_NAME||’ REBUILD TABLESPACE TSIND01;’ FROM USER_INDEXES T WHERE
( T.TABLE_NAME LIKE ‘T0%’ OR T.TABLE_NAME LIKE ‘T18%’ OR T.TABLE_NAME LIKE ‘T21%’ OR T.TABLE_NAME LIKE ‘T87%’ OR T.TABLE_NAME LIKE ‘T23%’
OR T.TABLE_NAME LIKE ‘T31%’) AND T.TABLE_NAME NOT LIKE ‘T07%’;

TSIND02

非分区表
SELECT ‘ALTER INDEX ‘||T.INDEX_NAME||’ REBUILD TABLESPACE TSIND02;’ FROM USER_INDEXES T WHERE
(T.TABLE_NAME LIKE ‘T07%’ OR T.TABLE_NAME LIKE ‘T37%’ OR T.TABLE_NAME LIKE ‘T27%’ OR T.TABLE_NAME LIKE ‘M%’);

---

无子分区表
SELECT 'ALTER INDEX '|| index_name|| ’ REBUILD PARTITION '|| PARTITION_NAME|| ’ TABLESPACE TSIND02; 'FROM USER_IND_PARTITIONS T where
SUBPARTITION_COUNT = 0 and (T.INDEX_NAME LIKE ‘T07%’ OR T.INDEX_NAME LIKE ‘%T37%’ OR T.INDEX_NAME LIKE ‘%T27%’ );

有子分区表

SELECT 'ALTER INDEX '|| index_name|| ’ REBUILD SUBPARTITION '|| SUBPARTITION_NAME|| ’ TABLESPACE TSIND02; 'FROM USER_IND_SUBPARTITIONS T where (T.INDEX_NAME LIKE ‘T07%’ OR T.INDEX_NAME LIKE ‘%T37%’ OR T.INDEX_NAME LIKE ‘%T27%’);

---

TSIND03

非分区表

SELECT ‘ALTER INDEX ‘||T.INDEX_NAME||’ REBUILD TABLESPACE TSIND03;’ FROM USER_INDEXES T WHERE T.TABLE_NAME LIKE ‘T47%’ OR T.TABLE_NAME LIKE ‘CZ%’ OR T.TABLE_NAME LIKE ‘ZH%’ AND T.TABLESPACE_NAME = ‘TSDAT03’;

无子分区表

SELECT 'ALTER INDEX '|| index_name|| ’ REBUILD PARTITION '|| PARTITION_NAME|| ’ TABLESPACE TSIND03; 'FROM USER_IND_PARTITIONS T where
SUBPARTITION_COUNT = 0 and (T.INDEX_NAME LIKE ‘%T47%’) AND T.TABLESPACE_NAME = ‘TSDAT03’;

有子分区表
SELECT 'ALTER INDEX '|| index_name|| ’ REBUILD SUBPARTITION '|| SUBPARTITION_NAME|| ’ TABLESPACE TSIND03; 'FROM USER_IND_SUBPARTITIONS T where
(T.INDEX_NAME LIKE ‘%T47%’ ) AND T.TABLESPACE_NAME = ‘TSDAT03’;

---

TSIND04
非分区表

SELECT ‘ALTER INDEX ‘||T.INDEX_NAME||’ REBUILD TABLESPACE TSIND04;’ FROM USER_INDEXES T WHERE (T.INDEX_NAME LIKE ‘%T47_TRANSACTION%’ );

无子分区
SELECT 'ALTER INDEX '|| index_name|| ’ REBUILD PARTITION '|| PARTITION_NAME|| ’ TABLESPACE TSIND04; 'FROM USER_IND_PARTITIONS T where
SUBPARTITION_COUNT = 0 and (T.INDEX_NAME LIKE ‘%T47_TRANSACTION%’ );

有子分区
SELECT 'ALTER INDEX '|| index_name|| ’ REBUILD SUBPARTITION '|| SUBPARTITION_NAME|| ’ TABLESPACE TSIND04; 'FROM USER_IND_SUBPARTITIONS T where
(T.INDEX_NAME LIKE ‘%T47_TRANSACTION%’) ;

---

TSIND05

非分区表
SELECT ‘ALTER INDEX ‘||T.INDEX_NAME||’ REBUILD TABLESPACE TSIND05;’ FROM USER_INDEXES T WHERE T.TABLE_NAME LIKE ‘T67%’
OR T.TABLE_NAME LIKE ‘T68%’ OR T.TABLE_NAME LIKE ‘T64%’ OR T.TABLE_NAME LIKE ‘T65%’ OR T.TABLE_NAME LIKE ‘TMP%’;

无子分区
SELECT 'ALTER INDEX '|| index_name|| ’ REBUILD PARTITION '|| PARTITION_NAME|| ’ TABLESPACE TSIND05; 'FROM USER_IND_PARTITIONS T where
SUBPARTITION_COUNT = 0 and (T.INDEX_NAME LIKE ‘%T67%’ or T.INDEX_NAME LIKE ‘%T68%’ or T.INDEX_NAME LIKE ‘%T64%’ or T.INDEX_NAME LIKE ‘%T65%’ );
有子分区
SELECT 'ALTER INDEX '|| index_name|| ’ REBUILD SUBPARTITION '|| SUBPARTITION_NAME|| ’ TABLESPACE TSIND05; 'FROM USER_IND_SUBPARTITIONS T where
(T.INDEX_NAME LIKE ‘%T67%’ or T.INDEX_NAME LIKE ‘%T68%’ or T.INDEX_NAME LIKE ‘%T64%’ or T.INDEX_NAME LIKE ‘%T65%’ ) ;

---

–导出表名注释
SELECT ‘COMMENT ON TABLE AML.’||T.TABLE_NAME||’ IS ‘’’||T.COMMENTS||’’’;’ FROM USER_TAB_COMMENTS T WHERE T.COMMENTS IS NOT NULL;
–导出表字段注释
SELECT ‘COMMENT ON COLUMN AML.’||T.TABLE_NAME||’.’||T.COLUMN_NAME||’ IS ‘’’||REPLACE(T.COMMENTS,’’’’,’’)||’’’;’
FROM USER_COL_COMMENTS T WHERE T.COMMENTS IS NOT NULL;
–初始化表，只保留一个分区，其余分区删除
SELECT ‘ALTER TABLE ‘||TTT.TABLE_NAME||’ DROP PARTITION ‘||TTT.PARTITION_NAME||’;’ FROM (
SELECT TT.TABLE_NAME,TT.PARTITION_NAME,ROW_NUMBER() OVER(PARTITION BY TT.TABLE_NAME ORDER BY TT.PARTITION_NAME) ROW_NUM
FROM USER_TAB_PARTITIONS TT WHERE TT.TABLE_NAME IN (
SELECT T.TABLE_NAME FROM USER_TAB_PARTITIONS T WHERE T.TABLE_NAME <> ‘T47_TRANSACTION_OPP_MID’ GROUP BY T.TABLE_NAME HAVING COUNT(*) >4)
) TTT WHERE TTT.ROW_NUM <> ‘1’;

–查询数据表块占空间大小
select T.segment_name,SUM(BYTES/1024/1024)||‘M’ from
user_extents T where segment_type = ‘TABLE’ GROUP BY SEGMENT_NAME;

–分配空间
select ‘alter table ‘||t.TABLE_NAME||’ allocate extent;’ from user_tables t where t.NUM_ROWS = 0;

–导出aml用户下的所有结构及数据
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
exp aml/aml file=aml_dev.dmp log=aml_dev.log owner=aml
–导入aml用户对象
imp aml/aml file=aml_dev.dmp log=aml_dev.log fromuser=aml touser=aml commit=y ignore=y

–导出amlods用户下的所有结构及数据
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
exp amlods/amlods file=amlods_dev.dmp log=amlods_dev.log owner=amlods
–导入amlods用户对象
imp amlods/amlods file=amlods_dev.dmp log=amlods_dev.log fromuser=amlods touser=amlods commit=y ignore=y

–删除用户下各对象数据
select ‘drop table ‘||t.TABLE_NAME||’ purge;’ from user_tables t;
select ‘drop PROCEDURE ‘||t.object_name||’;’ from user_procedures t where t.object_type = ‘PROCEDURE’;
select ‘drop FUNCTION ‘||t.object_name||’;’ from user_procedures t where t.object_type = ‘FUNCTION’;
select distinct ‘drop PACKAGE ‘||t.object_name||’;’ from user_procedures t where t.object_type = ‘PACKAGE’;
select ‘drop sequence ‘||t.SEQUENCE_NAME||’;’ from user_sequences t;
select ‘drop view ‘||t.VIEW_NAME||’;’ from user_views t;
–统一删除各对象数据

select ‘drop table ‘||t.TABLE_NAME||’ purge;’ from user_tables t
union all
select ‘drop PROCEDURE ‘||t.object_name||’;’ from user_procedures t where t.object_type = ‘PROCEDURE’
union all
select distinct ‘drop PACKAGE ‘||t.object_name||’;’ from user_procedures t where t.object_type = ‘PACKAGE’
union all
select ‘drop FUNCTION ‘||t.object_name||’;’ from user_procedures t where t.object_type = ‘FUNCTION’
union all
select ‘drop sequence ‘||t.SEQUENCE_NAME||’;’ from user_sequences t
union all
select ‘drop view ‘||t.VIEW_NAME||’;’ from user_views t;
–授权amlods存储过程给aml用户语句
SELECT ‘GRANT EXECUTE ON AMLODS.’||T.OBJECT_NAME||’ TO AML;’ FROM USER_PROCEDURES T;
SELECT ‘GRANT SELECT ON AMLODS.’||T.TABLE_NAME||’ TO AML;’ FROM USER_TABLES T;

---

授权表空间

alter user amln quota unlimited on TSDAT01;

---

查询表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) “表空间名”,
D.TOT_GROOTTE_MB “表空间大小(M)”,
D.TOT_GROOTTE_MB - F.TOTAL_BYTES “已使用空间(M)”,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),‘990.99’) “使用比”,
F.TOTAL_BYTES “空闲空间(M)”,
F.MAX_BYTES “最大块(M)”
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

---

查询表空间
select
b.file_id file_id,
b.tablespace_name tablespace_name ,
b.bytes bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) last,
sum(nvl(a.bytes,0))/(b.bytes)*100 last_pct
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id;

select dbf.tablespace_name,
dbf.totalspace “总量(M)”,
dbf.totalblocks as 总块数,
dfs.freespace “剩余总量(M)”,
dfs.freeblocks “剩余块数”,
(dfs.freespace / dbf.totalspace) * 100 “空闲比例”
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);

---

查询表压缩状态
select table_name,compression from user_tables where table_name like ‘’;
创建压缩表
普通表，直接在建表语句后加compress
分区表，给分区加压缩

例如：
CREATE TABLE T37_PARTY_BY
(
PARTY_ID VARCHAR2(24),
STATISTICDATE DATE,
PARTY_CLASS_CD CHAR(1),
PARTY_CHN_NAME VARCHAR2(200),
CREATE_DT DATE,
ORGANKEY VARCHAR2(12),
HOST_CUST_ID VARCHAR2(24),
GRANULARITY CHAR(1),
OBJORGANKEY VARCHAR2(12)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
COMPRESS BASIC STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE TSDATE04
PARTITION BY LIST (OBJORGANKEY)
(PARTITION PL_0095 VALUES (‘0095’)
PCTUSED 40
INITRANS 1
MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
INITIAL 32768
NEXT 16384
MINEXTENTS 1
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL
DEFAULT FLASH_CACHE
DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE TSDATE04
) ;
create index PI_T37_PARTY_BY on T37_PARTY_BY (PARTY_ID, OBJORGANKEY)
local;

---

导出建表语句
select dbms_metadata.get_ddl(‘TABLE’,‘T37_PARTY_BY’) FROM DUAL;

---

查询不可用索引
select index_name,status from user_indexes where status=‘UNUSABLE’;

重建索引
SELECT ‘ALTER INDEX ’ || INDEX_NAME||’ REBUILD;’ FROM USER_INDEXES WHERE status=‘UNUSABLE’;

---

查询是否锁表
select object_id,session_id,serial#,oracle_username,os_user_name,s.process
from v$locke{d}_{o}bjecta,v$session s
where a.session_id = s.sid
or
select SESS.sid, SESS.SERIAL#, LO.ORACLE_USERNAME, LO.OS_USER_NAME,
AO.OBJECT_NAME, LO.LOCKED_MODE
from V$LOCKE{D}_{O}BJECTLO,DB{A}_{O}BJECTsAO,V$SESSION SESS
where AO.OBJECT_ID = LO.OBJECT_ID
and lo.session_id = sess.sid;

---

创建dblink

– Create database link
create database link ODSDB.DEV.CQRCB.COM
connect to ODSALL
using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.181.76.155)(PORT = 1541)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = oradev)))’;

---

/测试交换分区 T47_TRANSACTION_UH 数据往 T47_TRANS_BAK 迁移/

---

–创建表结构、索引、主键相同的表用作存放数据
create table T47_TRANS_BAK AS SELECT * FROM T47_TRANSACTION_UH WHERE 1=2;
create index I_T47_TRANS_BAK_DA on T47_TRANS_BAK (TX_DT, ACCT_NUM);
create index I_T47_TRANS_BAK_DO on T47_TRANS_BAK (TX_DT, ORGANKEY);
create index I_T47_TRANS_BAK_DP on T47_TRANS_BAK (TX_DT, PARTY_ID);
create index I_T47_TRANS_BAK_PK on T47_TRANS_BAK (TRANSACTIONKEY);
–DROP TABLE T47_TRANS_BAK;

---

–交换分区
alter table T47_TRANSACTION_UH exchange partition PT_20100101
WITH TABLE T47_TRANS_BAK INCLUDING INDEXES WITH VALIDATION;

---

–查询数据
SELECT * FROM T47_TRANS_BAK;
SELECT * FROM T47_TRANSACTION_UH;
—查看索引是否失效
SELECT STATUS FROM USER_INDEXES WHERE TABLE_NAME=‘T47_TRANSACTION_UH’;
SELECT STATUS FROM USER_INDEXES WHERE TABLE_NAME=‘T47_TRANS_BAK’;

---

---

正则表达式替换特殊字符
select regexp_replace(t.aa,’[？！?!@$%^*]’,’’) from sue t;

---

创建dblink

create database link AMLHIS.DEV.CQRCB.COM
connect to AMLHIS IDENTIFIED BY AMLHIS
using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =
(PROTOCOL = TCP)(HOST = oradbgbk.uat.cqrcb.com)(PORT = 1521)))
(CONNECT_DATA =(SERVER = DEDICATED)
(SID = ORCL)))’;

---

查询分区空间大小
无子分区
select SEGMENT_NAME,SUM(BYTES)/1024/1024 Mbyte from user_segments
where segment_type=‘TABLE PARTITION’ GROUP BY SEGMENT_NAME;
有子分区

select SEGMENT_NAME,SUM(BYTES)/1024/1024 Mbyte from user_segments
where segment_type=‘TABLE SUBPARTITION’ AND SEGMENT_NAME=‘T37_PARTY_RESULT_CURR’
AND SUBSTR(PARTITION_NAME,1,11)=‘PL_20140831’ GROUP BY SEGMENT_NAME;

---

强制走索引

select /+index(T37_PARTY_RESULT PI_T37_PARTY_RESULT_CU)/ * from t37_party_result;

–查询数据表最小分区
select t.table_name,min(t.partition_name) partition_name from user_tab_partitions t
where t.table_name in (
select a.table_name from t18_tab_maintain_cfg a where a.table_struct = ‘D’
)
group by t.table_name order by t.table_name;

–查询排除该数据表最小分区的其他所有分区
select ‘ALTER TABLE ‘||b.table_name||’ DROP PARTITION ‘||b.partition_name||’;’ from user_tab_partitions b inner join (
select t.table_name,min(t.partition_name) partition_name from user_tab_partitions t
where t.table_name in (
select a.table_name from t18_tab_maintain_cfg a where a.table_struct = ‘D’
) group by t.table_name) c on b.table_name = c.table_name
where b.partition_name <> c.partition_name order by b.table_name,b.partition_name;

–获取测试数据拼接语句
select ‘insert into ‘||b.TABLE_NAME||’(’||wm_concat(b.COLUMN_NAME)||’) select ‘||
wm_concat(b.COLUMN_NAME)||’ from aml.’||b.TABLE_NAME||’;’
from USER_TAB_COLUMNS B WHERE B.TABLE_NAME IN (
select T.TABLE_NAME from user_tables t where t.TABLE_NAME IN (
‘T47_INDIVIDUAL’,
‘T47_PARTY’,
‘T47_CORPORATION’,
‘T47_ID_DEPOSIT’,
‘T47_CP_DEPOSIT’,
‘T47_TRANSACTION’
)) group by b.TABLE_NAME;