Oracle Data Dictionary Search

View All Scripts Login to Run Script

Script Name Oracle Data Dictionary Search
Description Search Oracle Data Dictionary for Objects such as TABLES , COLUMNS, VIEWS and PROCEDURE
Area SQL General
Contributor Stephen Cloete
Created Wednesday August 26, 2020

Statement 1

Search through the Oracle Data Dictionary for any table referencing emp within the HR Schema

Table Search Script

SELECT  
OWNER AS "Schema"  
,TABLE_NAME AS "Table"  
,TABLESPACE_NAME  
,NUM_ROWS --No. of Rows in Table since last Scan  
,LAST_ANALYZED --Latest Scan Date  
,DEGREE --Degree of parallel /*+ parallel(emp,8)*/  
FROM ALL_TABLES  
WHERE OWNER = 'HR'  
AND LOWER(TABLE_NAME) LIKE '%emp%'


Schema Table TABLESPACE_NAME NUM_ROWS LAST_ANALYZED DEGREE
HR EMPLOYEES EXAMPLE 107 20-DEC-16          1

Schema	Table	TABLESPACE_NAME	NUM_ROWS	LAST_ANALYZED	DEGREE
HR	EMPLOYEES	EXAMPLE	107	20-DEC-16	1

Statement 2

Search through the Oracle Data Dictionary for any Column referencing EMPLOYEE_ID within the HR Schema Tables

Column Search Script

SELECT  
OWNER AS "Schema"  
,TABLE_NAME AS "Table"  
,COLUMN_NAME  
,DATA_TYPE  
,NULLABLE  
,NUM_DISTINCT  
,LAST_ANALYZED --Latest Scan Date  
FROM ALL_TAB_COLUMNS  
WHERE  OWNER = 'HR'  
AND COLUMN_NAME LIKE '%EMPLOYEE_ID%' --Search for Column  
ORDER BY TABLE_NAME ASC, COLUMN_ID ASC


Schema Table COLUMN_NAME DATA_TYPE NULLABLE NUM_DISTINCT LAST_ANALYZED
HR EMPLOYEES EMPLOYEE_ID NUMBER N 107 20-DEC-16
HR EMP_DETAILS_VIEW EMPLOYEE_ID NUMBER N  -  - 
HR JOB_HISTORY EMPLOYEE_ID NUMBER N 7 20-DEC-16

3 rows selected.

Schema	Table	COLUMN_NAME	DATA_TYPE	NULLABLE	NUM_DISTINCT	LAST_ANALYZED
HR	EMPLOYEES	EMPLOYEE_ID	NUMBER	N	107	20-DEC-16
HR	EMP_DETAILS_VIEW	EMPLOYEE_ID	NUMBER	N	-	-
HR	JOB_HISTORY	EMPLOYEE_ID	NUMBER	N	7	20-DEC-16

Statement 3

Search through the Oracle Data Dictionary for any View referencing the commission_pct within the Code of the View

View Code Search Script

SELECT  
OWNER AS "Schema"  
,VIEW_NAME AS "View"  
,TEXT_VC AS "View Code"  
FROM all_views  
WHERE OWNER = 'HR'  
AND LOWER(TEXT_VC) LIKE '%commission_pct%'


Schema View View Code
HR EMP_DETAILS_VIEW SELECT
  e.employee_id,
  e.job_id,
  e.manager_id,
  e.department_id,
  d.location_id,
  l.country_id,
  e.first_name,
  e.last_name,
  e.salary,
  e.commission_pct,
  d.department_name,
  j.job_title,
  l.city,
  l.state_province,
  c.country_name,
  r.region_name
FROM
  employees e,
  departments d,
  jobs j,
  locations l,
  countries c,
  regions r
WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id
WITH READ ONLY

Schema	View	View Code
HR	EMP_DETAILS_VIEW	SELECT e.employee_id, e.job_id, e.manager_id, e.department_id, d.location_id, l.country_id, e.first_name, e.last_name, e.salary, e.commission_pct, d.department_name, j.job_title, l.city, l.state_province, c.country_name, r.region_name FROM employees e, departments d, jobs j, locations l, countries c, regions r WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id AND c.region_id = r.region_id AND j.job_id = e.job_id WITH READ ONLY

Statement 4

Build a sample procedure to test the search functionality

Procedure Code Search Script Part 1

CREATE OR REPLACE PROCEDURE my_test 
AS 
BEGIN  
dbms_output.put_line('Hello World');    
END;

Procedure created.

Statement 5

Using the test procedure from earlier , run the statement to search for a reference to "Hello World" in all the procedures within the Current Schema , note* to expand the search , make use of ALL_SOURCE or DBA_SOURCE and set the OWNER to the desired Schema

Procedure Code Search Script Part 2

select  
--OWNER AS "Schema", --uncomment when quering all_source or dba_source  
NAME AS "Package"  
,line AS "At Line no."  
,text AS "Code"  
FROM   user_source -- or use ALL_SOURCE / DBA_SOURCE  
WHERE TYPE = 'PROCEDURE' --FUNCTION/TYPE  
AND NAME IN (select distinct name from  user_source where lower(text) like lower('%Hello World%') )  
ORDER BY NAME ASC, LINE ASC


Package At Line no. Code
MY_TEST 1 PROCEDURE my_test 
MY_TEST 2 AS 
MY_TEST 3 BEGIN  
MY_TEST 4 dbms_output.put_line('Hello World');    
MY_TEST 5 END;

5 rows selected.

Statement 6
Tidy up the example procedure
Clean up Example Procedures
```
DROP PROCEDURE my_test
```
```
Procedure dropped.
```

Package	At Line no.	Code
MY_TEST	1	PROCEDURE my_test
MY_TEST	2	AS
MY_TEST	3	BEGIN
MY_TEST	4	dbms_output.put_line('Hello World');
MY_TEST	5	END;

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database