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;

Querying sequences


SELECT seq.CURRVAL 
FROM   dual;
SELECT seq.NEXTVAL 
FROM   dual;

if you execute the first script before executing second one, you will get the error

ORA-08002: 
"sequence %s.CURRVAL is not yet defined in this session"
*Cause: sequence CURRVAL has been selected before sequence NEXTVAL
*Action: select NEXTVAL from the sequence before selecting CURRVAL

The solution is simple. Execute the second script initially.

List indexes which you named intentionally


Here’s a nice Oracle SQL script that lists indexes which you named on purpose.

SELECT index_name 
FROM all_indexes 
WHERE table_owner = 'SCHEME' --schema name
AND table_name = 'TABLE' --table name
AND index_name NOT LIKE 'SYS%';

if you comment out this part, this script lists all indexes.

AND index_name NOT LIKE 'SYS%';

Oracle prefixes “SYS_” constraints and indexes when u dont give names to them. That’s why we add the condition.