我的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
结果为：

1 2009-11-10 17:41:48 2008-05-10 17:41:48