Database questions

Which system tables contain information about privileges granted and privileges obtained ?

USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD are the tables which contains the above asked information .

What command is used to create a table by copying the structure of another table ?

CREATE TABLE .. AS SELECT command

Explanation:

To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.

CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;

If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to

the new table.

Difference between stored procedure and Trigger :

Procedures can be called explicitely where as Triggers are automatically invoked when the action defined in Trigger is executed.

Triggers can also be enabled and Disabled.

Information related to stored procedures can be seen in USER_SOURCE and USER_OBJECTS(Current User) tables.

Where As

Information related to Triggers can be found in USER_SOURCE AND USER_TRIGGER(Current User) tables.

Datebase Questions

What Is The Use Of CASCADE CONSTRAINT ?

cascading is used for maintaining referencial integrity rules, which says that foreign key attributes values should be either subset of primary key values or null.

with cascading when parent is deleted child could be deleted/set null/set default according to cascade type.All this is for maintaing integrity.

The cascade option of the drop clause causes any dependent constraints also to be dropped.

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

What is the difference between TRUNCATE and DELETE command ?

TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back.

WHERE clause can be used with DELETE and not with TRUNCATE.

TRUNCATE resets the identity and DELETE does not .

If we use TRUNCATE command it will delete the physical structure of the table and retains the logical structure of same as for the next usage.
DELETE is used to delete a particular row from the table.

when we truncate a table then no trigger fired with associate table but in delete sts the trigger will be fire.

Truncate is fast and delete is not because delete have undo information .