My Hints

Transparent Data Encryption : create table accounts ( acc_no number not null, first_name varchar2(30) not null, SSN varchar2(9) folio_id number )

ENCRYPT USING 'AES128', ENCRYPT USING 'AES128'

The clauses after the columns SSN and FOLIO_ID, which specify ENCRYPT using the 128-bit Advanced Encryption Standard. To set the wallet password, use the command: alter system set encryption key authenticated BY "topSecret"; Encrypting External Tables : create table account_ext organization external ( type oracle_datapump default directory dump_dir location ('accounts_1_ext.dmp', 'accounts_2_ext.dmp', 'accounts_3_ext.dmp', 'accounts_4_ext.dmp') ) parallel 4 as select ACC_NO, FIRST_NAME, SSN ENCRYPT IDENTIFIED BY "topSecret", FOLIO_ID ENCRYPT IDENTIFIED BY "topSecret" from accounts; Query XML in SQL : SQL> xquery 2 for $var1 in (1,2,3,4,5,6,7,8,9) 3 let $var2 := $var1 + 1 4 where $var2 < 6 5 order by $var2 descending 6 return $var2 7 / Enhanced COMMIT COMMIT WRITE WAIT; COMMIT WRITE NOWAIT; COMMIT WRITE BATCH; COMMIT WRITE IMMEDIATE; ALTER SYSTEM SET COMMIT_WRITE = NOWAIT; ALTER SESSION SET COMMIT_WORK = NOWAIT;

Catch the Error and Move On: Error Logging Clause : SQL> insert into accounts 2 select * from accounts_ny; insert into accounts * ERROR at line 1: ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated Instead of this use the below mentioned way, First, you need to create a table to hold the records rejected by the DML statement. Call that table ERR_ACCOUNTS. exec dbms_errlog.CREATE_ERROR_LOG ('ACCOUNTS','ERR_ACCOUNTS') SQL> 2 3 4 5

insert into accounts select * from accounts_ny log errors into err_accounts reject limit 200 /

6 rows created. select ORA_ERR_NUMBER$, ORA_ERR_MESG$, ACC_NO from err_accounts; Protect the Code at Source: WRAP Package begin dbms_ddl.create_wrapped ('create or replace procedure p1 as begin null; end;') end; / select text from user_source where name = 'P1'; OR select dbms_ddl.wrap ('create or replace procedure p1 as begin null; end;') from dual Conditional Compilation in PL/SQL: Write Once, Execute Many

In C language, which supports the concept of compiler directives. In C programs, depending on the version of the compiler involved, the value of certain variables may differ. In Oracle Database 10g Release 2, PL/SQL has a similar feature: pre-processor directives can now be provided that are evaluated during compilation, not runtime. create or replace function myfunc 2 return varchar2 3 as 4 begin 5 $if $$ppval $then 6 return 'PPVAL was TRUE'; 7 $else 8 return 'PPVAL was FALSE'; 9 $end 10* end;

ppval is a pre-processor variable, not a normal PL/SQL one, you have specified it using the $$ notation. Also, to let the compiler know that it has to process the lines during compilation only, you have specified the evaluations with the special $ notation, e.g. $if instead of if. Now, compile this function with different values of the variable ppval. alter session set plsql_ccflags = 'PPVAL:TRUE';

Unlimited DBMS Output LONG to LOB Conversion via Online Redef : create table ACC_MESG_INT(acc_no clob );

number, mesg_dt

date,

mesg_text

Now start the redefinition process. begin dbms_redefinition.start_redef_table (UNAME => 'ARUP', ORIG_TABLE => 'ACC_MESG',INT_TABLE => 'ACC_MESG_INT', COL_MAPPING => 'acc_no acc_no, mesg_dt mesg_dt, to_lob(MESG_TEXT) MESG_TEXT'); end;

Note line 6, where the columns have been mapped. The first two columns have been left the same but the third column MESG_TEXT has been mapped so that the destination table's column MESG_TEXT is populated by applying the function TO_LOB on the source table's column. If the table to be redefined is large, you will need to synchronize the data between the source and target tables periodically. This approach makes the final sync-up faster. begin dbms_redefinition.sync_interim_table(uname => 'ARUP', orig_table => 'ACC_MESG', int_table => 'ACC_MESG_INT'); end;

You may have to give the above command a few times, depending on the size of the table. Finally, complete the redefinition process with begin dbms_redefinition.finish_redef_table ( ORIG_TABLE => 'ACC_MESG', INT_TABLE end; /

UNAME => 'ARUP', => 'ACC_MESG_INT');

The table ACC_MESG has changed:

SQL> desc acc_mesg Name Null? Type ----------------------------------------- -------- --------ACC_NO MESG_DT MESG_TEXT

NOT NULL NUMBER NOT NULL DATE

Note the column MESG_TEXT is now CLOB, instead of LONG. Drop a Table in Chunks : Have you ever noticed how much time it takes to drop a partitioned table? That's because each partition is a segment that has to be dropped. In Oracle Database 10g Release 2, when you drop a partitioned table, partitions are dropped one by one. Because each partition is dropped individually, fewer resources are required than when the table is dropped as a whole.