Oracle常用操作

数据库误删恢复

--10分钟前数据可用于误删恢复
create table 表名_tmp as 
(
SELECT * FROM 表名 AS OF TIMESTAMP SYSDATE - 10/1440
)；
select * from 表名_tmp；
update 表名A  set A.S_GRADE=(select S_GRADE  from 表名_TMP B where A.ID=B.ID)；
select * from 表名；

--闪回
alter table 表名 enable row movement;
flashback table 表名 to timestamp to_timestamp('2018-08-09 10:00:00','yyyy-mm-dd HH24:MI:SS');
alter table 表名 disable row movement; 

处理锁定

--处理锁定
select t1.sid, t1.serial#, t1.username, t1.logon_time from v$session t1 , v$locked_object t2
where t1.sid = t2.session_id order by t1.logon_time;
alter system kill session '1025,1152'

查询历史执行记录

--查看oracle执行的语句
select * from v$sql t
where
t.PARSING_SCHEMA_NAME = '数据库名称' --数据库
and lower(t.SQL_TEXT) like '%sql语句中包含的信息%' --查询某类SQL语句
and  t.LAST_ACTIVE_TIME>(sysdate - interval '1' MINUTE)  --执行1分钟内的SQL语句
and (t.MODULE is null or t.MODULE not like '%PL/SQL%') --不是在某些终端里的执行

触发器和序列

--序列
CREATE SEQUENCE POWERPMS.S_NBG_MAT_APPLY
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 1000000000000000000000000000
    NOMINVALUE
    NOCYCLE
    CACHE 20
    NOORDER
GO
--触发器
CREATE OR REPLACE TRIGGER POWERPMS.TID_NBG_MAT_APPLY
  before insert on NBG_MAT_APPLY
  for each row
BEGIN
  SELECT S_NBG_MAT_APPLYT.NEXTVAL INTO :NEW.OLD_UNIQUEID FROM DUAL; 
END TID_NBG_MAT_APPLY;
GO

关联更新

MERGE INTO  表A USING  表B ON ( 表A.字段= 表B.字段) WHEN MATCHED THEN
UPDATE
SET 表A.字段 = 表B.字段

行转列

10g ：wm_concat

11g : pivot ，unpivot

12c : listagg

参考链接

表空间不足

项目中使用出现报错

ORA-01653: 表xx无法通过 8192 (在表空间 xx_data 中) 扩展。

-- 查询剩余表空间，发现没有要找的表空间
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 T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

-- 修改表空间文件扩展方式:  路径是上面查询的 FILE_NAME
ALTER DATABASE  DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\NBGPMC\POWERPMS.ORA' AUTOEXTEND  ON NEXT 50M MAXSIZE UNLIMITED