To find the queries running for more than 5 or x of minutes you can try the below query and change the seconds parameter as per your need:

Query:

SELECT S.USERNAME
,Q.SQL_ID
,ROUND (S.LAST_CALL_ET, 2) TIME_IN_SECONDS
,ROUND (S.LAST_CALL_ET / 60, 2) TIME_IN_MINS
,S.SID
,Q.SQL_TEXT
FROM GV$SESSION S, V$SQL Q
WHERE S.SQL_ID = Q.SQL_ID
AND STATUS = 'ACTIVE'
AND USERNAME LIKE '%APPS%'
AND S.LAST_CALL_ET > 300;

Output:

USERNAME SQL_ID TIME_IN_SECONDS TIME_IN_MINS
—————————— ————- ————— ————
SID
———-
SQL_TEXT
——————————————————————————–
APPS 497wh6n7hu14f 10562436 176040.6
331
BEGIN FND_CP_GSM_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); END;

1 row selected.

Note: 300 is (5*60). change it as you want to.

To get the list of columns of a table in Oracle, we use to query the view “USER_TAB_COLUMNS” to get the columns of the tables or views owned by the current user. Or use the other view “USER_TAB_COLS“.

SELECT table_name
,column_name
,data_type
,data_length
,nullable
,data_default
FROM  user_tab_columns
WHERE table_name = '&YOUTABLENAME'
ORDER BY column_id;

in the above query, we selected 7 columns to retrieve the most important information that you might be looking for in table columns which are

• table names: the table name.
• column name: column name.
• data type: column data type like varchar2, number, etc.
• data length: the value length.
• nullable: whether the table accepts the null value or not.
• data_default: whether the column has a default value or not.

Note: you have to specify the table name parameter in uppercase.

Note 2: If you use the above view, you will get all unhidden columns, but if you query the view “USER_TAB_COLS” you will get all columns, including hidden columns.

Another view is “ALL_TAB_COLUMNS” this one is the same as “USER_TAB_COLUMNS” except that the “ALL_TAB_COLUMNS” view has a column called “OWNER” in which you can specify the owner of the table that you want to get its columns list.

BULK COLLECT in Oracle PL/SQL:

BULK COLLECT in Oracle PL/SQL reduces the round trip between SQL engine and PL/SQL engine and allows SQL engine to fitch a bulk collection of data at once instead of the traditional LOOP statement.

We are using the BULK COLLECT clause in the SELECT statement to fitch the cursor result set in bulk or to populate the records in bulk.

Since the BULK COLLECT fetches a result set or more than one record, the INTO clause must contain a collection variable like Oracle TYPE.

Example:

DECLARE
CURSOR oraask_dept_details
IS
SELECT * FROM departments;

TYPE l_dept_details_tbl IS TABLE OF departments%ROWTYPE;

l_dept_details l_dept_details_tbl;
BEGIN
OPEN oraask_dept_details;

LOOP
FETCH oraask_dept_details BULK COLLECT INTO l_dept_details LIMIT 10;

EXIT WHEN l_dept_details.count = 0;

FOR dept_details_rec IN l_dept_details.first .. l_dept_details.last
LOOP
dbms_output.put_line ('DEPARTMENT ID:' || l_dept_details (dept_details_rec).department_id || ' - DEPARTMENT NAME:' || l_dept_details (dept_details_rec).department_name);
END LOOP;
END LOOP;

CLOSE oraask_dept_details;
END;
/

Output :

DEPARTMENT ID:10 - DEPARTMENT NAME:Administration
DEPARTMENT ID:20 - DEPARTMENT NAME:Marketing
DEPARTMENT ID:30 - DEPARTMENT NAME:Purchasing
DEPARTMENT ID:40 - DEPARTMENT NAME:Human Resources
DEPARTMENT ID:50 - DEPARTMENT NAME:Shipping
DEPARTMENT ID:60 - DEPARTMENT NAME:IT
DEPARTMENT ID:70 - DEPARTMENT NAME:Public Relations
DEPARTMENT ID:80 - DEPARTMENT NAME:Sales
DEPARTMENT ID:90 - DEPARTMENT NAME:Executive
DEPARTMENT ID:100 - DEPARTMENT NAME:Finance
DEPARTMENT ID:110 - DEPARTMENT NAME:Accounting
DEPARTMENT ID:120 - DEPARTMENT NAME:Treasury
DEPARTMENT ID:130 - DEPARTMENT NAME:Corporate Tax
DEPARTMENT ID:140 - DEPARTMENT NAME:Control And Credit
DEPARTMENT ID:150 - DEPARTMENT NAME:Shareholder Services
DEPARTMENT ID:160 - DEPARTMENT NAME:Benefits
DEPARTMENT ID:170 - DEPARTMENT NAME:Manufacturing
DEPARTMENT ID:180 - DEPARTMENT NAME:Construction
DEPARTMENT ID:190 - DEPARTMENT NAME:Contracting
DEPARTMENT ID:200 - DEPARTMENT NAME:Operations
DEPARTMENT ID:210 - DEPARTMENT NAME:IT Support
DEPARTMENT ID:220 - DEPARTMENT NAME:NOC
DEPARTMENT ID:230 - DEPARTMENT NAME:IT Helpdesk
DEPARTMENT ID:240 - DEPARTMENT NAME:Government Sales
DEPARTMENT ID:250 - DEPARTMENT NAME:Retail Sales
DEPARTMENT ID:260 - DEPARTMENT NAME:Recruiting
DEPARTMENT ID:270 - DEPARTMENT NAME:Payroll

In the above script, we used a LIMIT clause to limit fetching the bulk of records to 10 records at a time. This clause is very handful when dealing with a large number of records to avoid running out of memory because the collection type we use is expanding as more records are inserted into it.

Do you find it helpful? Share to spread the knowledge

Before Oracle database release 12c, the error ORA-01417 is raised when you have more than one table on the left-hand side of an outer join. To overcome this limit, we can convert the join to an ANSI syntax, e.g.:

SELECT *
FROM EMPLOYEES E
LEFT JOIN DEPARTMENTS D ON (D.DEPARTMENT_ID = E.DEPARTMENT_ID)
LEFT JOIN LOCATIONS L ON (L.LOCATION_ID = D.LOCATION_ID);

From the 12c version, Oracle has supported having multiple tables on the left-hand side of the join.

To check current user permission in oracle execute the following queries:

First Query:

SELECT *
FROM USER_SYS_PRIVS
WHERE USERNAME = USER;

The result of this query would be:

Second Query:

SELECT *
FROM USER_TAB_PRIVS
WHERE GRANTEE = USER;

The result of this query would be:

Third Query:

SELECT *
FROM USER_ROLE_PRIVS
WHERE USERNAME = USER;

The result of this query would be:

hope this help.