Get results for all tables row count in schema and truncate all tables at a time in schema

get results for all tables row count in schema

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

SET SERVEROUTPUT ON;

DECLARE 

VAL NUMBER;

BEGIN

for i in (SELECT table_name FROM all_tables WHERE owner = 'Ora_Schema' and table_name like '%GL%')

LOOP

EXECUTE IMMEDIATE 'SELECT count(*) from Ora_Schema.' || i.table_name INTO VAL;

DBMS_OUTPUT.PUT_LINE(i.table_name || ' | ' || VAL);

END LOOP;

END;

truncate all tables at a time in schema

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

set serveroutput on;

begin

for t1 in (SELECT table_name FROM all_tables WHERE owner = 'Orc_schema' and table_name in ('GL_LEDGERS','GL_CALENDARS')) loop

    begin

       ----- dbms_output.put_line('truncate table '||t1.table_name || ';');    

        execute immediate ('truncate table '||t1.table_name);

    end;

end loop;

end;

Remove all characters after a specific character in oracle query

 

get/extract char , date , numeric values from varchar column

requirement:

amount column having numeric data , char data and date also

So char /date data into one column and numeric (+ve, -ve ) values in another column

use below query to acheive that:

select

case when  trim(TRANSLATE(replace(substr(amount_in_char,0,1),'-','')||substr(amount_in_char,2), '0123456789,.', ' ')) is not null 

then amount_in_char end getchardtae,

case  when trim(TRANSLATE(replace(substr(amount_in_char,0,1),'-','')||substr(amount_in_char,2), '0123456789,.', ' ')) is  null 

then amount_in_char end getnum,

amount_in_char original from w_plan_fs; 

Find out the long running queries/locked objects in database

 Use below queries

check the odi session id status in database

Example:

 select * from GV$SESSION WHERE ACTION LIKE '%21622%'; (odi session id)

SELECT * FROM V$SESSION_LONGOPS WHERE SID=1392;

SELECT * FROM V$SESSION WHERE SEQ#='9383';ACTION LIKE '%21622%';

SELECT SQL_FULLTEXT ,LOCKED_TOTAL,SQL_ID FROM GV$SQL WHERE SQL_ID='383g771j';

SELECT * FROM GV$SQL;

alter system kill session '648,51886' IMMEDIATE

select username, status from v$session where SID=12;

employee-manager relationship hierarchy using SQL Query

 

As you can see, I had to put a hardcoded value for EMPLEVEL field in the Recursive section of the query above. This was because I did not join the Recursive section of the query with our recursive CTE. Now, in order to make the entire hierarchical query work with actual hierarchical level of records assigned, we need to complete the INNER JOIN as in the below query. Notice the difference in value of EMPLEVEL field. This is the complete hierarchical query in CTE format. Please note that ORDER BY is optional here.

WITH OURCTE (EMPNO, ENAME, MGR, EMPLEVEL)
AS (SELECT EMPNO, ENAME, MGR, 1 EMPLEVEL ---Initial Subquery
FROM Emp
WHERE MGR IS NULL
UNION ALL
SELECT E.EMPNO, E.ENAME, E.MGR, CTE.EMPLEVEL + 1 ---Recursive Subquery
FROM EMP E
INNER JOIN OURCTE CTE ON E.MGR = CTE.EMPNO –Inner Join
WHERE E.MGR IS NOT NULL)
SELECT *
FROM OURCTE
ORDER BY EMPLEVEL;

If you need to display the manager name next to the manager ID, refer to the following query:

SELECT CTE.EMPNO, CTE.ENAME, CTE.MGR, E.ENAME “MGR NAME”, CTE.EMPLEVEL
FROM (
WITH OURCTE (EMPNO, ENAME, MGR, EMPLEVEL)
AS (SELECT EMPNO, ENAME, MGR, 1 EMPLEVEL –Initial Subquery
FROM Emp
WHERE MGR IS NULL
UNION ALL
SELECT E.EMPNO, E.ENAME, E.MGR, CTE.EMPLEVEL + 1 –Recursive Subquery
FROM EMP E
INNER JOIN OURCTE CTE ON E.MGR = CTE.EMPNO
WHERE E.MGR IS NOT NULL)
SELECT *
FROM OURCTE
ORDER BY EMPLEVEL) CTE
LEFT OUTER JOIN EMP E ON CTE.MGR = E.EMPNO
ORDER BY CTE.EMPLEVEL,E.ENAME;