Queries for DD tables in Oracle

Find the queries for various DD tables in the document https://docs.google.com/open?id=180eCMrfSn-SkDRA-eXEAFIQCi85wdiugzlt9jzwZSjqoCLHyVA_HQnqnqzTa

Data dictionary tables for Roles and Privileges in Oracle

/* Issue this query to find out what roles have been created for the    */

/* oracle db you are accessing. the 1st 19 roles are default roles that */

/* are created when you install oracle; */

select * from dba_roles;

/* Use this query to find what usrs (grantees) have been granted what roles */

select grantee, granted_role from dba_role_privs where grantee not in

('SYS', 'SYSTEM') order by grantee;

/* use this query to see tables that were created by others (=owner),*/

/* that you have been granted access too, including what privileges  */

/* you were granted (=privilege) and whether you can grant access to */

/* others (=GRA) */

select * from user_tab_privs_recd;

/* to find what system privileges have been granted to roles */

Select privilege, admin_option from role_sys_privs where role like ‘ROLENAME%’;

/* to find what roles have been granted to roles */

Select granted_role, admin_option from role_role_privs where role like ‘ROLENAME%’;

/* to find what table privileges have been granted to roles */

Select * from role_tab_privs where role like ‘ROLENAME%’;

/* to find what system privileges have been granted to users and roles */

Select * from dba_sys_privs where grantee like ‘USERORROLENAME;

Installing and configuring OAM server 11g

Steps to Install and configure OAM server 11g:  http://itnaf.org/oracle-access-manager-11g-new/

Enabling SSO for ADF Application using OAM 11g

Useful video link http://www.youtube.com/watch?v=8mOZJM8ZZ2E

Delete duplicate rows from Oracle tables

Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:

•  Subquery to identify duplicate rows
• Use RANK to find and remove duplicate table rows
• Use self-join to remove duplicate rows
• Use analytics to detect and remove duplicate rows
• Delete duplicate table rows that contain NULL values

Use subquery to delete duplicate rows

Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:

DELETE FROM 
   table_name A
WHERE
  a.rowid >
   ANY (
     SELECT
        B.rowid
     FROM
        table_name B
     WHERE
        A.col1 = B.col1
     AND
        A.col2 = B.col2
        );

Use RANK to delete duplicate rows

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:

delete from $table_name where rowid in
  (
  select "rowid" from
     (select "rowid", rank_n from
         (select rank() over (partition by $primary_key order by rowid) rank_n, rowid as "rowid"
             from $table_name
             where $primary_key in
                (select $primary_key from $table_name
                  group by $all_columns
                  having count(*) > 1
                )
             )
         )
     where rank_n > 1
  )

One of the most important features of Oracle is the ability to detect and remove duplicate rows from a table. While many Oracle DBA place primary key referential integrity constraints on a table, many shops do not use RI because they need the flexibility.

Use self-join to delete duplicate rows

The most effective way to detect duplicate rows is to join the table against itself as shown below.

select 
   book_unique_id,
   page_seq_nbr,
   image_key
from
   page_image a
where
   rowid >
     (select min(rowid) from page_image b
      where
         b.key1 = a.key1
      and
         b.key2 = a.key2
      and
         b.key3 = a.key3
      );

Please note that you must specify all of the columns that make the row a duplicate in the SQL where clause. Once you have detected the duplicate rows, you may modify the SQL statement to remove the duplicates as shown below:

delete from 
   table_name a
where
   a.rowid >
   any (select b.rowid
   from
      table_name b
   where
      a.col1 = b.col1
   and
      a.col2 = b.col2
   )
;

Use analytics to delete duplicate rows

You can also detect and delete duplicate rows using Oracle analytic functions:

delete from
   customer
where rowid in
 (select rowid from
   (select
     rowid,
     row_number()
    over
     (partition by custnbr order by custnbr) dup
    from customer)
  where dup > 1);

As we see, there are several ways to detect and delete duplicate rows from Oracle tables

Eliminating special characters from a value or Finding values with special characters

Query to Eliminate special characters from a column value:

          select translate('e%rerA%' , 'A(%$*&@,;''/+-' , 'A') from dual;

Query to retrieve only the special characters from the column value:


select  translate(string_column,'%' || translate(string_column,'X(%$*&@,;''/+-)','X'),'%') special_characters
  from  (select 'test%er'  string_column from dual);

Query to select only the rows whose column values has the special characters:


  SELECT string_column FROM (select 'test%er'  string_column from dual)
  WHERE string_column != nvl(translate(string_column , 'A(%$*&@,;''/+-' , 'A'), 'A');

Oracle Listener service is not starting

When you try to start the listener service and it gives you the below message and stops automatically.

The OracleOraDb10g_home1TNSListener service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.


Solution which worked for me:

1) listener.ora file was missing under the NETWORK/ADMIN folder so I have added the file to this folder with the below content.

            LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST =  <IPADDRESS>)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

2) Made sure the sqlnet.ora file has the below entry.



    SQLNET.AUTHENTICATION_SERVICES= (NTS)

     NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


3) Made sure the tnsnames.ora has the proper entry as shown below


  orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <IPADDRESS>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )



4) Restart the machine and manually start the oracle service. This time it will start properly and you can able to connect to it via any oracle developer tools.

Note: Even-though the listener is not running you will be able to connect to database via sqlplus available under oracle home application development utility.

Displaying all dates between two given dates

Displaying all dates between two dates. 


Table "tab_dates" contains the below data

START_DATE  |   END_DATE   

---------------------------------
01-JAN-12          07-JAN-12            

08-JAN-12         16-JAN-12            




Required Output:



DAY_DATE                  START_DATE           END_DATE                  
------------------------- ------------------------- ------------------------- 
01-JAN-12                 01-JAN-12                 07-JAN-12                 
02-JAN-12                 01-JAN-12                 07-JAN-12                 
03-JAN-12                 01-JAN-12                 07-JAN-12                 
04-JAN-12                 01-JAN-12                 07-JAN-12                 
05-JAN-12                 01-JAN-12                 07-JAN-12                 
06-JAN-12                 01-JAN-12                 07-JAN-12                 
07-JAN-12                 01-JAN-12                 07-JAN-12                 
08-JAN-12                 08-JAN-12                 16-JAN-12                 
09-JAN-12                 08-JAN-12                 16-JAN-12                 
10-JAN-12                 08-JAN-12                 16-JAN-12                 
11-JAN-12                 08-JAN-12                 16-JAN-12                 
12-JAN-12                 08-JAN-12                 16-JAN-12                 
13-JAN-12                 08-JAN-12                 16-JAN-12                 
14-JAN-12                 08-JAN-12                 16-JAN-12                 
15-JAN-12                 08-JAN-12                 16-JAN-12                 
16-JAN-12                 08-JAN-12                 16-JAN-12 


{code} -- Applies only to Oracle 11g database

with tab_dates as 
      (select to_date('01-JAN-12','dd-mon-yy') start_date,to_date('07-JAN-12','dd-mon-yy') end_date from dual
       union all
        select to_date('08-JAN-12','dd-mon-yy') start_date,to_date('14-JAN-12','dd-mon-yy') end_date  from dual)


    select start_date + i day_date, start_date, end_date from tab_dates,
     xmltable('for $i in 0 to xs:int(D)-1 return $i' passing 
     xmlelement(D,  (end_date-start_date)+1 ) columns i integer path '.') ;
{/code}

Explanation:


Using XMLTable() generate the list of numbers (ex: 0..6 for dates 6 days apart)   


(end_date-start_date)+1  = number of days in-between and the result is substituted in the place of "D" using the XMLElement() function.


The temporary XMLTable now contains a column named "i".