What are Oracle System tables | Oracle System tables list with example

Oracle System tables:

While working with the Oracle we need to know some of the basic Oracle System tables which gives us idea about the database objects,database constraints.In this article i will give you the information about the tables which gives us the information of database objects such as tables,views,functions,triggers and packages.

The tables which belongs to oracle System tablespace are called as Oracle System Tables..

Following are some most important queries which are useful to get database information about database objects:

1.Find Table Information:

1.1.How to Find table name and its owner?

Make sure that the database user have logged in with SYS user.

Select table_name,Owner from All_tables order by table_name,owner;

1.2.How to find Selected Tables from a User?

SELECT Table_Name FROM User_Tables WHERE Table_Name LIKE ‘STU%’;

2. Find Schema information

Following query will give all oracle schema on that Oracle database instance.

2.1)How to Select Users from Database?

SELECT Username FROM All_Users ORDER BY Username;

3.Find View information

3.1)How to check all oracle views created on the database?

select VIEW_NAME, OWNER from ALL_VIEWS order by OWNER, VIEW_NAME;

CLICK HERE TO GET INTERVIEW QUESTIONS FOR TECH MAHINDRA

4.Find Constraint information:

4.1) How to find all details about Constraints?

SELECT * From User_Constraints;

SELECT * FROM User_Cons_Columns;

4.2) How to find Constraint Name?

SELECT Table_Name, Constraint_Name FROM User_Constraints;

4.3) How to find Constraint Name with Column_Name?

SELECT Column_Name, Table_Name, Constraint_Name FROM User_Cons_Columns;

4.4) How to find Selected Tables which have Constraint?

SELECT Table_Name FROM User_Cons_Columns WHERE Table_Name LIKE ‘STU%’;

4.5) How to find Constraint_Name, Constraint_Type, Table_Name?

SELECT Table_Name, Constraint_Type, Constraint_Name FROM User_Constraints;

SELECT Table_Name, Constraint_Type, Constraint_Name, Generated FROM User_Constraints;

5.Find Sequence information

5.1) How to check Sequences?

SELECT * FROM USER_SEQUENCES;

6.Find Procedure information

6.1) How to check Procedures?

 SELECT * FROM User_Source

WHERE Type=’PROCEDURE’

AND NAME IN (‘SP_CONNECTED_AGG’,’SP_UNCONNECTED_AGG’);

6.2)How to find procedure columns information?

select OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE;

CLICK HERE TO GET DATABASE OPTIMIZATION INTERVIEW QUESTIONS

7.Find Function information

7.1)How to find functions created in database?

select OBJECT_NAME, OWNER from ALL_OBJECTS where upper(OBJECT_TYPE) = upper(‘FUNCTION’) order by OWNER, OBJECT_NAME ;

8.Find Index information

8.1) How to find indexes used in database?

Select  * from USER_INDEXES;

Select * from ALL_IND_COLS where Index_name=’Name of Index’;

9.Find trigger information

select TRIGGER_NAME, OWNER from ALL_TRIGGERS order by OWNER, TRIGGER_NAME;

10. How to find DB Name?

SELECT Ora_Database_Name FROM DUAL;

SELECT * FROM GLOBAL_NAME;

SELECT Name from V$DATABASE;

This article on Oracle System Tables gives everyone the clear view of important and useful system tables which are used in oracle.I have tried to explain the system tables which are really useful in real life scenarios in industry.Hope everyone will like this useful article.If you have any suggestion kindly comment it on comment section.