Dropping user with QUEUE tables

I don't know whether it's a bug or something...but if you try to drop a user with cascade that is having some queue tables. It won't allow you to drop!!!

Would throw the below error:

ORA-00604: error occurred at recursive SQL level 1
ORA-24170: SCOTT.IAI_QUEUE_R is created by AQ, cannot be dropped directly
ORA-06512: at "SYS.DBMS_RULE_ADM", line 158
ORA-06512: at line 1

How to ressolve?

1. Grant AQADM execute permission to the user

GRANT EXECUTE ON DBMS_AQADM to SCOTT.

2. Login as scott and find the QUEUE tables it belongs to.

select * from user_tables

where table_name like 'AQ%'

3. Drop the QUEUE tables using the following command.

 exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_IAI_NR',force => TRUE);

4. Login as DBA user and issue

drop user SCOTT cascade

Now it works!!!

Buffer Cache Hit Ratio

Tuning the buffer cache hit ratio


A logical read occurs whenever a user requests data from the database. It occurs whenever the data is in the buffer cache or whether the user process must read it from disk. If the data must be read from disk then a physical read occurs. Oracle keeps track of logical and physical reads in the V$SYSSTAT table.

Use the following SQL statement to determine the values required for the hit radio calculation:
 

select name, value From v$sysstat
where name in ('db block gets', 'consistent gets', 'physical reads');

       Hit ratio = 1 - (physical reads / (db block gets + consistent gets))

Resizing buffer_cache and shared_pool:

If the cache-hit ratio goes below 90% then:

• For Oracle 8 and earlier: increase the initialisation parameter DB_BLOCK_BUFFERS.
• For Oracle 9 onwards: increate the initialisation parameter DB_CACHE_SIZE.

calculating the cache hit ratio:

select
100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"
from
v$sysstat v1, v$sysstat v2, v$sysstat v3
where
v1.name = 'db block gets' and
v2.name = 'consistent gets' and
v3.name = 'physical reads'

SQL> show parameter db_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 721420288

SQL> show parameter show parameter shared_pool
SQL> show parameter shared_pool_size


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 134217728


Activity:


1. Resize Buffer cache
alter system set db_cache_size=4096M scope=spfile;

2. Resize Shared pool size
alter system set shared_pool_size=4096M scope=spfile;

3. Restart database
Shutdown immediate;
Startup;

5. Checking parameter change.

show parameter db_cache_size
show parameter shared_pool


6. Align pfile with spfile
Create pfile from spfile;

sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

After a successful RDBMS installation sqlplus may through the below error:

[oracle@test ~]$ sqlplus ‘/as sysdba’
sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

The reason is that SELinux is running in “enforcing” mode.

You can check it on file /etc/pam.d/login . Oracle development has recommended the following workaround while they correct the problem:

Switch SELinux from the default “Enforcing” mode that it is running in, to the “Permissive” mode.

Commands, as root:
======================
getenforce       (returns “Enforcing”)
setenforce 0
getenforce       (returns “Permissive”)

This allows SELinux to continue running, and logging denial messages, but SELinux will not actually deny any operations. Once Development has resolved this issue, you can (and should) return SELinux to the default “Enforcing” mode as follows:

Commands, as root:
======================
setenforce 1
getenforce       (returns “Enforcing”)

Column Encryption using DBMS_CRYPTO

Oracle Database 10g provides means to encrypt and decrypt your sensitive data using the built-in API called DBMS_CRYPTO. Using DBMS_CRYPTO API one can encrypt sensitive information like SSN, Credit Card Numbers, Debit Card Numbers, etc stored in the database as these are not supposed to be stored in plain text for security reasons.

Let’s  see how DBMS_CRYPTO can be used to encrypt data and also consider its performance implications.

create table emp(empid number, ename varchar2(100), ssn varchar2(80)); 

Table created.    

insert into emp values (1, 'SMITH',        123456701);    

1 row created. 

insert into emp values (2, 'ALLEN',        123456702);

 1 row created. 

 insert into emp values (3, 'WARD',         123456703); 

 1 row created. 

commit; 

select * from emp; 

      EMPID ENAME                          SSN 

 ---------- ------------------------------ ------------- 

          1 SMITH                          123456701 

          2 ALLEN                          123456702 

          3 WARD                           123456703

The SSN has been inserted into the table in plain text and can be read by anybody having SELECT privilege on EMP table.

Now, let’s create a package using DBMS_CRYPTO API to handle encryption and decryption.

Create or replace package cryptit is 

Function encrypt_data( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC; 

Function decrypt_data( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC; 

End cryptit; 

/ 

  

 Package created. 

  

Create or replace package body cryptit is 

V_Key       RAW(128) := UTL_RAW.cast_to_raw('testkey1');          -- Key 

  Function encrypt_data( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC 

    IS 

   l_data RAW(2048) := utl_raw.cast_to_raw(p_data); 

   l_encrypted RAW(2048); 

   BEGIN 

   NULL; 

   l_encrypted := dbms_crypto.encrypt                        -- Algorithm 

( src => l_data, 

                 typ => DBMS_CRYPTO.DES_CBC_PKCS5, 

   key => V_KEY ); 

   Return l_encrypted; 

END encrypt_data; 

Function decrypt_data( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC 

   IS 

l_decrypted RAW(2048); 

BEGIN 

l_decrypted := dbms_crypto.decrypt                              -- Algorithm 

 ( src => p_data, 

typ => DBMS_CRYPTO.DES_CBC_PKCS5, 

key => V_KEY ); 

Return utl_raw.cast_to_varchar2(l_decrypted); 

END decrypt_data; 

End cryptit; 

/ 

Package body created. 

The package body consists of functions to encrypt and decrypt data along with a key. As these functions will always output the same value for a specific input, we can make them Deterministic functions. (A Deterministic Function always returns the same result any time they are called with a specific set of input values.)

Let's now make use of these functions to encrypt our sensitive data.

update emp set ssn = cryptit.encrypt_data(ssn); 

3 rows updated. 

select * from emp; 

      EMPID ENAME                          SSN 

---------- ------------------------------ ---------------------------------- 

         1 SMITH                          5F3168C22E54060DE7D97B31F7E38BB6 

         2 ALLEN                          5F3168C22E54060D0C2527FBBD3DCD6C 

         3 WARD                           5F3168C22E54060D8166D3757932A112 

When inserting new records we simply make use of "cryptit.encrypt_data" function in the INSERT statement.

insert into emp values( 4, 'NADVI', cryptit.encrypt_data(123456704)); 

1 row created. 

 commit; 

#  

Commit complete. 

select * from emp; 

      EMPID ENAME                          SSN 

---------- ------------------------------ ---------------------------------------- 

          1 SMITH                          5F3168C22E54060DE7D97B31F7E38BB6 

          2 ALLEN                          5F3168C22E54060D0C2527FBBD3DCD6C 

          3 WARD                           5F3168C22E54060D8166D3757932A112 

          4 NADVI                          5F3168C22E54060D2CFF7E7A35B14187 

  

 select empid, ename, cryptit.decrypt_data(ssn) decrypted_ssn, ssn from emp; 

     EMPID ENAME                          DECRYPTED_SSN   SSN 

 ---------- ------------------------------ --------------- ---------------------------------------- 

          1 SMITH                          123456701       5F3168C22E54060DE7D97B31F7E38BB6 

          2 ALLEN                          123456702       5F3168C22E54060D0C2527FBBD3DCD6C 

          3 WARD                           123456703       5F3168C22E54060D8166D3757932A112 

          4 NADVI                          123456704       5F3168C22E54060D2CFF7E7A35B14187 

All is fine as far as encryption is concerned, but lets see what happens when SSN is used in the WHERE clause of queries.

We will create an index on SSN and run a query against it.

create index t_ssn on emp(ssn);

Index created.

 select * from emp where ssn = cryptit.encrypt_data('123456701');

     EMPID ENAME                          SSN

---------- ------------------------------ ----------------------------------------

         1 SMITH                          5F3168C22E54060DE7D97B31F7E38BB6

Execution Plan

----------------------------------------------------------

Plan hash value: 2894032564

-------------------------------------------------------------------------------------

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |     1 |   107 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP   |     1 |   107 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_SSN |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("SSN"=RAWTOHEX("CRYPTIT"."ENCRYPT_DATA"('123456701')))

Note

-----

   - dynamic sampling used for this statement

Well, the index "T_SSN" has been selected by the optimizer to be cost efficient. When a range of values is to be scanned, optimizer falls flat on its face as it has no idea and picks up FTS.

select * from emp where cryptit.decrypt_data(ssn) between '123456702' and '123456704';

     EMPID ENAME                          SSN

---------- ------------------------------ ----------------------------------------

         2 ALLEN                          5F3168C22E54060D0C2527FBBD3DCD6C

         3 WARD                           5F3168C22E54060D8166D3757932A112

         4 NADVI                          5F3168C22E54060D2CFF7E7A35B14187

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |   107 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |   107 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))>='123456702'

              AND "CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))<='123456704')

Note

-----

   - dynamic sampling used for this statement

We can also create Function Based Indexes on encrypted columns such that data is accessed faster with a better execution plan. This is the reason I have marked both ENCRYPT_DATA and DECRYPT_DATA as DETERMINISTIC functions.

create index f_ssn_idx on emp(cryptit.decrypt_data(ssn));

Index created.

select * from emp where cryptit.decrypt_data(ssn) between '123456702' and '123456704';

     EMPID ENAME                          SSN

---------- ------------------------------ ----------------------------------------

         2 ALLEN                          5F3168C22E54060D0C2527FBBD3DCD6C

         3 WARD                           5F3168C22E54060D8166D3757932A112

         4 NADVI                         5F3168C22E54060D2CFF7E7A35B14187

Execution Plan

----------------------------------------------------------

Plan hash value: 9274740

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |     1 |   107 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |   107 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | F_SSN_IDX |     1 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))>='123456702' AND

              "CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))<='123456704')

Note

-----

   - dynamic sampling used for this statement

The optimizer has rightly picked up the Function based index to access requested data quickly.

DBMS_CRYPTO is a great way to encrypt sensitive data and we can also create indexes on the encrypted columns to speedup our queries. Lastly, remember to wrap the CRYPTIT package body so that the key is not exposed.

DBMS_CRYPTO can also be used to encrypted data recursively. Let us try to encrypt the already encrypted SSN from the above example.

select * from emp;

     EMPID ENAME    SSN

---------- -------- --------------------------------------------------------------------------------

         1 SMITH    5F3168C22E54060DE7D97B31F7E38BB6

         2 ALLEN    5F3168C22E54060D0C2527FBBD3DCD6C

         3 WARD     5F3168C22E54060D8166D3757932A112

         4 NADVI    5F3168C22E54060D2CFF7E7A35B14187

update emp set ssn = cryptit.encrypt_data(ssn);

4 rows updated.

select * from emp;

     EMPID ENAME    SSN

---------- -------- --------------------------------------------------------------------------------

         1 SMITH    455E357D80B1C81F91D52088DA0FE03A0C133D129CFC3879EE7A8DE993FD459DB5C9E1EBEF37062C

         2 ALLEN    455E357D80B1C81F91D52088DA0FE03A98FC410A71CE64D57935691692D433B770C5F8AF07C9113F

         3 WARD     455E357D80B1C81F91D52088DA0FE03A9D32D14FE64F41DD66BE7FEF4C31384F032C16826EAA4830

         4 NADVI    455E357D80B1C81F91D52088DA0FE03A04D5C0AD79E689EABD46F8EC335308E32D6F7E94167F6EDF

commit;

Commit complete.

select empid, ename, cryptit.decrypt_data(cryptit.decrypt_data(ssn)) decrypted_ssn, ssn from emp;

     EMPID ENAME    DECRYPTED_SSN   SSN

---------- -------- --------------- --------------------------------------------------------------------------------

         1 SMITH    123456701       455E357D80B1C81F91D52088DA0FE03A0C133D129CFC3879EE7A8DE993FD459DB5C9E1EBEF37062C

         2 ALLEN    123456702       455E357D80B1C81F91D52088DA0FE03A98FC410A71CE64D57935691692D433B770C5F8AF07C9113F

         3 WARD     123456703       455E357D80B1C81F91D52088DA0FE03A9D32D14FE64F41DD66BE7FEF4C31384F032C16826EAA4830

         4 NADVI    123456704       455E357D80B1C81F91D52088DA0FE03A04D5C0AD79E689EABD46F8EC335308E32D6F7E94167F6EDF

Tablespace NOLOGGING and objects

What happens with a NOLOGGING tablespace ? Will all the tables ( segments) created in that tablespace will not be logged by default?

By default the Tables (created in this Tablespace) will inherit the option NOLOGGING. It can be overridden at the Table level if you specify the option LOGGING when you create it.

So you can have Tables with the option LOGGING in a Tablespace with the option NOLOGGING.

The only reason to create a tablespace with nologging, or to later set the nologging attribute on a tablespace, is to change the default of all objects (segments) created in that tablespace from that point forward. It has no effect at all on segments that already exist in the tablespace.
That's it. It has no effect on how long it will take the tablespace to be created, or recoverability of that tablespace.

At the table/index level, it will only have an effect for direct load operations, such as insert /*+ append */, CTAS, or alter table move on a table, or index creation or rebuild.
Besides this, you have also the FORCE LOGGING /NO FORCE LOGGING mode.
The FORCE LOGGING / NO FORCE LOGGING is used to control at the Database or Tablespace level the Logging mode. By default the Database and Tablespaces are in NO FORCE LOGGING.

 The FORCE LOGGING mode always overrides the NOLOGGING option at the Tablespace or Table level.

So if you have the Database in FORCE LOGGING mode, your Tables will generates Redo logs even if these Tables are created with the option NOLOGGING.

It can be useful in a Data Guard so as to enforce Redo log generation to maintain the Standby Databases.