我的SQL经验积累
--给中间表授权
1、m_data.dc_department和m_data.dc_department_mid表的操作权限赋给fs_pwsc,语法是
grant select, insert, update, delete on m_data.dc_department to FS_PWSC;
grant select, insert, update, delete on m_data.dc_department_mid to FS_PWSC;
2
--重建索引
SELECT 'alter ' || object_type || ' ' || object_name || ' rebuild;'
FROM user_objects
WHERE object_type IN ('INDEX');
删除用户前的操作:
--删除过程,函数,视图,包,同义词
SELECT 'DROP ' || object_type || ' ' || object_name || ';'
FROM user_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'SEQUENCE', 'VIEW', 'PACKAGE', 'SYNONYM');
--删除表
SELECT 'DROP TABLE "' || object_name || '" CASCADE CONSTRAINTS PURGE;'
FROM user_objects
WHERE object_type = 'TABLE';
3:
当前时间向前 推8个月的时间
1:
select sysdate - INTERVAL '8' MONTH from dual
2:
SELECT add_months(SYSDATE ,-8) FROM dual
3:根据当前时间,向前推1年零6个月后的时间
select sysdate - INTERVAL '1-6' YEAR TO MONTH from dual
结果为:
12009-11-10 17:41:482008-05-10 17:41:48