SQL query to convert Comma/Colon separated List to Rows

        select  regexp_substr('san@abc.com; san@xyz.com','[^; ]+', 1, level) 
from dual
        connect by regexp_substr('san@abc.com; san@xyz.com', '[^; ]+', 1, level) is not null;

Recursive update of all records in a table with huge data

Method 1:

Begin
 loop
   update xx_test_table
   set  xx_flag='Y'
 where xx_flag='N'
  and rownum <= 5000;
exit when sql%notfound;
commit;
end loop;
commit;
end;

Method 2:

declare
type rowid_array_tbl_type is table of rowid;
ids_tbl rowid_array_tbl_type;

cursor cur is
select rowid as id
from xx_test_table
where xx_flag='N';

begin
      open cur;
      loop
         fetch cur bulk collect into ids_tbl limit 1000;
         exit when ids_tbl.count = 0;
forall i in ids_tbl.first .. ids_tbl.last
            update xx_test_table set  xx_flag='Y' where rowid=ids_tbl(i);
commit;   
      end loop;

    end;

Query to find current running SQL for a given concurrent request id

Query to find current running SQL for a given concurrent request id

SELECT FCR.REQUEST_ID,
       D.SID,
       D.SERIAL#,
       D.OSUSER,
       D.PROCESS,
       C.SPID,
       E.SQL_TEXT
  FROM APPS.FND_CONCURRENT_REQUESTS   FCR,
       APPS.FND_CONCURRENT_PROCESSES  FCP,
       V$PROCESS                      C, 
       V$SESSION                      D,
       V$SQL                          E
 WHERE     FCR.CONTROLLING_MANAGER = FCP.CONCURRENT_PROCESS_ID
       AND C.PID = FCP.ORACLE_PROCESS_ID
       AND FCP.SESSION_ID = D.AUDSID
       AND D.SQL_ADDRESS = E.ADDRESS
       AND FCR.REQUEST_ID = :P_REQUEST_ID;

(In multi node -- use GV$)

Long Running Concurrent Requests

  SELECT ROUND (
               (NVL (ACTUAL_COMPLETION_DATE, SYSDATE) - actual_start_date)
             * 24
             * 60,
             2)
             run_time_mins,
         a.*
    FROM apps.fnd_amp_requests_v a
   WHERE     1 = 1
         AND actual_start_date > TRUNC (SYSDATE) - 1
         AND phase_code = 'R'
         AND ROUND (
                   (NVL (ACTUAL_COMPLETION_DATE, SYSDATE) - actual_start_date)
                 * 24
                 * 60,
                 2) >
             60

ORDER BY 1 DESC;

Invalid number error when working with DFF attributes..

Invalid number error when working with DFF attributes when using to_number:

When we are working with DFFs, segments can be configured to store numbers but due to user wrong input or some misconfiguration some rows might end with having string with numbers and characters. When we use that attribute in a query and try to compare it to a number or try to convert to a number, the system will throw error. To avoid the error, we can use the following condition in the query:

select * from po_headers_all
where org_id=101
and nvl(length(regexp_replace(attribute5,'^[0-9]+', ''),0)=0;


to find invalid rows you can use below.

nvl(length(regexp_replace(attribute5,'^[0-9]+', ''),0)>0;

Useful Queries

1. Print Date and time using sysdate:
    SELECT TO_CHAR (SYSDATE, 'DD-MM-YY HH:MI:SS') FROM DUAL;

2. Print Numbers from 1 to 10.
 SELECT ROWNUM r
      FROM DUAL
CONNECT BY ROWNUM <= 10;

3. Kill Session
alter system kill session '1266,54057'; --'sid, serial#'

4. Gather Schema stats:
begin
DBMS_STATS.GATHER_TABLE_STATS('AP','AP_SUPPLIER_SITES_ALL');
end;
/

5. Remove special  (control chars) characeters from the string

SELECT REGEXP_REPLACE(colx, '[[:cntrl:]]', ' ') FROM xx_tab;

6. REPLACE ONE OR MORE COMMAS WITH SINGLE COMMA AND SPACE

select regexp_replace(' Address Line1,,Addressline3,,Postal Code,,57000  ',',{1,}', ', ') from dual;

Useful data dictionary views

NLS_DATABASE_PARAMETERS --> lists permanent NLS parameters of the database.NLS_INSTANCE_PARAMETERS lists NLS parameters of the instance.
There is one row for each component.You can query any of DBA_*, ALL_*,USER_*.In the below table I am listing only USER_

Dictionary Name	Details
USER_OBJECTS	Stores details about all the objects in a schema – Object Types – Table, View, Synonym, Trigger, Sequence, Procedure, Function, Package, Package Body, Trigger, Index, Database Link, Type, polcies, Rules (VPD policy rules)
USER_TABLES	Table – Table Space, Disk block allocation details
USER_TAB_COLS	Column of table
USER_VIEWS	Views data dictionary – You can get the view text
USER_SEQUENCES	Sequence information – Seq min, max, increment, etc.
user_synonyms	Synonyms – Reference object details
User_triggers	Stores the all the trigger in the schema. – You can get the trigger text and trigger table etc.
User_trigger_cols	Trigger column details
User_source	Stores all the source code – You can get code for trigger, procedure, package, package body, type body, type, function, procedure.
User_procedures	Lists all the procedure – You can get a list of procedure for a given package.
User_arguments	List of all the parameters for a given procedure. It includes package procedures as well.
User_indexes	List of Indexes ex: select * from user_indexes where table_name=''
user_ind_columns	Index column names
user_dependencies	Dependencies  select * from user_dependencies                  where referenced_name='GL_LEDGER_NORM_SEG_VALS'                  and name not like 'XX%'                 and referenced_type='SYNONYM' and referenced_owner='APPS';

Useful Dynamic Performance (V$) Views

V$ Views vs GV$ Views:V$ views are CONTAINER_DATA objects. When a user connected to the root queries a V$ view, the query results will depend on the CONTAINER_DATA attribute for users. For almost every V$ view,  Oracle has a corresponding GV$ (global V$) view. In Oracle Real Application Clusters, querying a GV$ view retrieves the V$ view information from all qualified instances. In addition to the V$ information, each GV$ view contains an extra column named INST_ID of data type NUMBER. The INST_ID column displays the instance number from which the associated V$ view information was obtained.
V$ACCESS displays objects in the database that are currently locked and the sessions that are accessing them.V$ACTIVE_INSTANCES displays the mapping between instance names and instance numbers for all instances that have the database currently mounted.V$AQ displays statistics for the queues in the database.V$CONTEXT lists set attributes in the current session.V$CONTROLFILE lists the names of the control files.V$DATABASE displays information about the database from the control file.V$DATAFILE contains datafile information from the control file.V$DBFILE lists all datafiles making up the database. This view is retained for historical compatibility. Use of V$DATAFILE is recommended instead.V$DBLINK describes all database links (links with IN_TRANSACTION = YES) opened by the session issuing the query on V$DBLINK. These database links must be committed or rolled back before being closed.V$INSTANCE view displays the state of the current instance.V$LICENSE contains information about license limits.V$NLS_PARAMETERS view contains current values of NLS parameters. =>NLS_CALENDAR, NLS_CHARACTERSET, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_ISO_CURRENCY, NLS_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_SORT, NLS_TERRITORY, NLS_UNION_CURRENCY, NLS_NCHAR_CHARACTERSET, NLS_COMPV$NLS_VALID_VALUES lists all valid values for NLS parameters. V$OBJECT_USAGE to monitor index usage. The view displays statistics about index usage gathered from the database. All indexes that have been used at least once can be monitored and displayed in this view. V$OPTION lists options that are installed with the Oracle Database.V$PARAMETER displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.V$PARAMETER2 displays information about the initialization parameters that are currently in effect for the session, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values displayed in the V$SYSTEM_PARAMETER2 view.Presenting the list parameter values in this format enables you to quickly determine the values for a list parameter. For example, if a parameter value is a, b, then the V$PARAMETER view does not tell you if the parameter has two values (both a and b) or one value (a, b). V$PARAMETER2 makes the distinction between the list parameter values clear.V$SESSION view lists session information for each current session.V$SGA displays summary information about the system global area (SGA).V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered.V$SQL_BIND_DATA For each distinct bind variable in each cursor owned by the session querying this view/V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.V$SQL_PLAN_STATISTICS provides execution statistics at the row source level for each child cursor.V$SQLTEXT contains the text of SQL statements belonging to shared SQL cursors in the SGA.V$VERSION displays version numbers of core library components in the Oracle Database. 

ORA-00947: not enough values even when number of VALUES matches the insert

ORA-00947: not enough values even when number of VALUES matches the insert

I faced the issue "ORA-00947: not enough values even when number of VALUES matches the insert" - I checked the couple of sites but did not get any clue finally found that the issue is because of 'VALUES' clause with 'SELECT' statement..


Wrong:
insert into tabA(a,b,c) Values (Select 'a', 'b', 'c' from dual); 

Correct: - No Values clause
insert into tabA(a,b,c) 
(Select 'a', 'b', 'c' from dual);  

Difference between dba_* and all_* and user_* dictionary views

Difference between dba_* and all_* and user_* dictionary views

• An ALL_* view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
• A DBA_* view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
• A USER_* view displays all the information from the schema of the current user. No special privileges are required to query these views.