oracle cheatsheet,OracleDB Cheatsheet

删除某个用户下的所有表、视图等对象，但不删除用户本身

DECLARE

BEGIN

FOR r1 IN ( SELECT 'DROP ' || object_type || ' ' || object_name || DECODE ( object_type, 'TABLE', ' CASCADE CONSTRAINTS PURGE' ) AS v_sql

FROM user_objects

WHERE object_type IN ( 'TABLE', 'VIEW', 'PACKAGE', 'TYPE', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SEQUENCE' )

ORDER BY object_type,

object_name ) LOOP

EXECUTE IMMEDIATE r1.v_sql;

END LOOP;

END;

/

用exp导出数据库时，空表无法导出的解决方案

This might be because of these tables may not be extent allocated. Before taking backup you need to identify all the tables that do not have data. Then alter these tables to allocate extent.

ALTER TABLE ALLOCATE EXTENT;

Use the below script to alter all tables they do not have extent allocated.

DECLARE

BEGIN

FOR r1 IN ( SELECT 'ALTER TABLE '||table_name||' ALLOCATE EXTENT' AS v_sql

FROM user_tables

WHERE segment_created = 'NO') LOOP

EXECUTE IMMEDIATE r1.v_sql;

END LOOP;

END;

误删表后如何恢复

FLASHBACK TABLE HRDLGS_SENSITIVEAREA TO BEFORE DROP;