常用SQL

一下子删除所有表（添加CASCADE CONSTRAINTS便会无视制约）
SELECT 'DROP TABLE ' || TABLE_NAME || ' CASCADE CONSTRAINTS;' FROM USER_TABLES ;

其实就是从USER_TABLES里取出当前USER所拥有的表，
然后用||拼凑字符串来生成删除所有表的SQL文。

同理可用于删除所有VIEW：
SELECT 'DROP view ' || view_NAME || ';' FROM user_views ;

删除所有SEQUENCE:
SELECT 'DROP sequence ' || SEQUENCE_NAME || ';' FROM user_sequences ;

只要结合USER_XXX里的东西就可以生成出很多便利的SQL文。

PACKAGE等存于user_source中。
SELECT 'DROP PACKAGE ' || DISTINCT NAME || ';' FROM USER_SOURCE where type='PACKAGE' ;

以下为Dictionary的基础知识.

The Oracle Data Dictionary
Just like you use Oracle tables to store your data, Oracle uses tables to store its data. A set of tables, called the Oracle data dictionary, contains information about all the structures (tables, views, etc.) and procedural code (triggers, PL/SQL procedures, etc.) created by each user.

For example, there's a table called USER_TAB_COLUMNS that contains information about all the columns you've defined, including: what table the column belongs to, the data type (number, varchar, etc.), what the default value is, whether the column can be null, etc.

The Oracle data dictionary is huge and contains a lot of esoteric stuff, but when you whittle it down to only the info you need, it's not so menacing. Here are the data dictionary tables I find useful. You can do SELECTs on them, just as you would any other table in Oracle:

USER_TABLES Lists each table that belongs to your Oracle user.
USER_TAB_COMMENTS Shows comments on the tables and views.
USER_TAB_COLUMNS Tells you the names, data types, default values, etc. of each column in each table.
USER_COL_COMMENTS Shows comments on the columns.
USER_CONSTRAINTS Gives you all constraints (either single- or multi-column), such as primary key, foreign key, not null, check constraints, etc.
USER_CONS_COLUMNS Maps constraints to columns (since a constraint can act on one or many columns).
USER_INDEXES Lists indexes defined on columns (either defined explicitly when creating the data model or defined automatically by Oracle, as is the case with indexes on primary keys).
USER_IND_COLUMNS Maps indexes to columns.
USER_VIEWS Lists all views, along with the text used to originally create them.
USER_SYNONYMS Lists the synonyms and original table names.
USER_SEQUENCES Lists all sequences, including min value, max value, and amount by which to increment.
USER_TRIGGERS Contains trigger names, criteria for activating each trigger, and the code that is run.
USER_SOURCE Contains the source code for all PL/SQL objects, including functions, procedures, packages, and package bodies.