DML privileges on selective columns .

We can selectively grant privileges on any columns in a table . This applies for INSERT , DELETE , UPDATE statements.

Here is an example , where the schema owner ( in this case , dbuser ) wants to restrict the
appuser to insert only two columns in a table . Oracle throws an error message if the appuser tries to insert any other columns other than provisioned .

See below for an example.

SQL> show user
USER is "DBUSER"

SQL> create table t as select object_id , object_name , object_type from all_objects;
Table created.

SQL> grant insert (object_id , object_name ) on t to appuser ;
Grant succeeded.


SQL> insert into dbuser.t ( object_id , object_name , object_type) values ( 500 , 'MOHIDEEN' , 'DBA');
1 row created.

SQL> conn appuser/app
Connected.

SQL> show user
USER is "APPUSER"


SQL> insert into dbuser.t ( object_id , object_name , object_type) values ( 561 , 'ZAHIR' , 'DBA') ;
insert into dbuser.t ( object_id , object_name , object_type) values ( 561 , 'ZAHIR' , 'DBA')
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> insert into dbuser.t ( object_id , object_name ) values ( 561 , 'ZAHIR' ) ;
1 row created.

Enhanced TRUNCATE in Oracle 11g Release 2 ( 11.2.0.2)

Oracle 11g Release 2 patch 2 ( 11.2.0.2) introduced "Enhanced Truncate " Functionality , by which you can release all 
the storage when you truncate the table .

Please see below for example.

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> create table t as select object_id , object_name from all_objects;

Table created.

SQL> select sum(bytes)/1024/1024 from user_segments where segment_name ='T';

SUM(BYTES)/1024/1024
--------------------
                   3

SQL> truncate table t;

Table truncated.

SQL>  select sum(bytes)/1024/1024 from user_segments where segment_name ='T';

SUM(BYTES)/1024/1024
--------------------
               .0625

SQL> drop table t purge;

Table dropped.

SQL> create table t as select object_id , object_name from all_objects;

Table created.

SQL> truncate table t drop storage ; 
Table truncated.

SQL> select sum(bytes)/1024/1024 from user_segments where segment_name ='T';

SUM(BYTES)/1024/1024
--------------------
               .0625


SQL> drop table t purge;

Table dropped.

SQL> create table t as select object_id , object_name from all_objects;

Table created.






Let us use 'drop all storage '  clause with the TRUNCATE .

SQL>  truncate table t drop all storage ;

Table truncated.

SQL>  select sum(bytes)/1024/1024 from user_segments where segment_name ='T';

SUM(BYTES)/1024/1024
--------------------

SQL> Select count(*) from user_segments where  segment_name ='T';

  COUNT(*)
----------
         0
As you can see , there is no storage associated with the table 'T' .

Comments Welcome.