Oracle “DROP TABLE”


DROP  TABLE

Use the DROP TABLE statement to move a table or object table to the recycle bin or to remove the table and all its data from the database entirely.

Dropping a table invalidates dependent objects and removes object privileges on the table. Thus if you just want to claim the space, have a look at ” TRUNCATE TABLE”.

SYNTAX

DROP TABLE [ schema. ] table  [ CASCADE CONSTRAINTS ] [ PURGE ] ;

PURGE

Specify PURGE if you want to drop the table and release the space associated with it in a single step. If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin.

Using this clause is equivalent to first dropping the table and then purging it from the recycle bin.

Recycle Bin

Shit !!! I dropped “Users” table. What can ı do now ? Hint: “FLASHBACK TABLE”; but that’s for another post.

CASCADE CONSTRAINTS

Specify CASCADE CONSTRAINTS to drop all referential integrity constraints (foreign keys) that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, then the database returns an error and does not drop the table.

 

 

 

Difference between user_objects, all_objects and dba_objects


Difference between those views is;

  • USER_OBJECTS shows the current user objects.
  • ALL_OBJECTS shows all objects of current and those object which you have rights to access.
  • DBA_OBJECTS shows all object of all users .

Lets generalize this valuable information. It is applicable to every user_<blabla>, all_<blabla>, dba_<blabla> views.

I got this information here. But a wise man one said.

“Repeat the real truth wherever it is needed, altough its not revealed by you”

Dont ask me who was this guy. Never saw him.

List locked objects


SELECT VLO.OS_USER_NAME               “OS USERNAME”,
VLO.ORACLE_USERNAME            “DB USER”,
VP.SPID                        “SPID”,
AO.OWNER                       “OWNER”,
AO.OBJECT_NAME                 “OBJECT LOCKED”,
AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE, 1, ‘NO LOCK’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCL’,
6, ‘EXCLUSIVE’,
NULL) “MODE OF LOCK”,
VS.STATUS                      “CURRENT STATUS”
FROM   V$LOCKED_OBJECT VLO,
ALL_OBJECTS AO,
V$SESSION VS,
V$PROCESS VP
WHERE  VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> ‘KILLED’
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;