Home » Database

Oracle

Oracle Database Tips

Delete All Oracle User Objects

Fri, 04/04/2014 - 11:04 — sandip

If you don't have system level access, and want to clean your oracle schema, the following sql will produce a series of drop statments, which can then be executed. Not all of them will execute - if you drop with cascade, dropping the PK_* indices will fail.

select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';9;)from user_objects;

Confirm with:

select * from user_objects

Purge out the recyclebin if not needed:

purge recyclebin;

Oracle SQL Plus Command Reference

Fri, 01/14/2005 - 17:47 — nepalexpo

Introduction

At the SQL prompt, you can begin typing any SQL command. Upon hitting return (i.e., enter key) the SQL prompt will change to line number prompts. When you are finished typing a command, type / or RUN to execute the SQL command. Also, a semicolon at the end of the SQL command will execute the command immediately after hitting return. In addition to SQL commands, /, and RUN, other commands can be issued at the SQL prompt (a semicolon does not have to follow the nonSQL commands).

Commands

  • DESCRIBE tablename -- lists the attributes and their specifications of tablename
Comment