日常使用Oracle语句备份

–创建表空间

CREATE TABLESPACE fault LOGGING DATAFILE 'D:\ORADATA\FAULT\FAULT.ORA' SIZE 512M AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

–创建用户

CREATE USER FAULT PROFILE DEFAULT IDENTIFIED BY FAULT_2018 DEFAULT TABLESPACE FAULT  ACCOUNT UNLOCK

–赋权

GRANT SELECT ANY TABLE TO FAULT
GRANT CONNECT TO FAULT;
GRANT EXP_FULL_DATABASE TO FAULT
GRANT IMP_FULL_DATABASE TO FAULT
GRANT RESOURCE TO FAULT
GRANT DEBUG  CONNECT SESSION TO FAULT
GRANT UPDATE ANY TABLE TO FAULT
GRANT DELETE ANY TABLE TO FAULT
GRANT INSERT ANY TABLE TO FAULT
GRANT DROP ANY TABLE TO FAULT

–查询数据库文件存储路径

SELECT B.FILE_NAME 物理文件名,  B.TABLESPACE_NAME 表空间, B.BYTES/1024/1024 大小M, (B.BYTES SUM(NVL(A.BYTES,0)))/1024/1024  已使用M,SUBSTR((B.BYTES-SUM(NVL(A.BYTES,0)))/(B.BYTES)*100,1,5)  利用率 FROM DBA_FREE_SPACE A,DBA_DATA_FILES B WHERE A.FILE_ID=B.FILE_ID GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTES ORDER BY B.TABLESPACE_NAME;`

–生成UUID

 SELECT LOWER(RAWTOHEX(SYS_GUID())) FROM DUAL CONNECT BY ROWNUM<20`

–时间查询

select * from dual where time between to_date('2012-06-18
00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-06-18
23:59:59','yyyy-mm-dd hh24:mi:ss');`

select * from dual where to_char(time,'yyyy-mm-dd')='2012-06-18' 

select * from dual where trunc(time)=to_date('2012-06-18','yyyy-mm-dd')

–按年月份查询统计

SELECT
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 1, L.FAULT_STATUS, 0 )) AS January,
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 2, L.FAULT_STATUS, 0 )) AS February,
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 3, L.FAULT_STATUS, 0 )) AS March,
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 4, L.FAULT_STATUS, 0 )) AS April,
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 5, L.FAULT_STATUS, 0 )) AS May,
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 6, L.FAULT_STATUS, 0 )) AS June,
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 7, L.FAULT_STATUS, 0 )) AS July,
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 8, L.FAULT_STATUS, 0 )) AS August,
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 9, L.FAULT_STATUS, 0 )) AS September,
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 10, L.FAULT_STATUS, 0 )) AS October,
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 11, L.FAULT_STATUS, 0 )) AS November,
	SUM(
	DECODE( EXTRACT( MONTH FROM L.CREATE_TIME ), 12, L.FAULT_STATUS, 0 )) AS December
FROM
	F_FAULT_LIST L 
WHERE
	EXTRACT( YEAR FROM L.CREATE_TIME ) = '2018'

–生成多个纯数字guid

SELECT SUBSTR( TO_CHAR( DBMS_RANDOM.VALUE ( 11, 99 )), 4, 32 ) ZBH FROM dual connect by rownum<23

–拼接字符串

SELECT DEPTNAME||'_admin' from LP_SYS_DEPT

–获取30min前的库表状态并保存新表

INSERT into "LP_SYS_USER_copy1" select * from LP_SYS_USER as of timestamp (systimestamp - interval '30' minute)