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.

Oracle system tables

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;

Oracle system tables

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.

 

About admin 136 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development

8 Trackbacks / Pingbacks

  1. Grep Command in unix | Grep Command with Examples | Grep Command with Options | Search String in Unix File | Practical Examples of Grep command | fgrep command with example | egrep command with example
  2. Filter Commands in Unix | Cut command with Example | Paste Command With Example | Translate (tr) Command with Example
  3. Table partition | Table Partitioning in Oracle | Partitioning in SQL | How Partitioning helps improving Performance | Table Partitioning with Examples | Partitioning on existing table
  4. Database Normalization Examples | Normalization in Database with Examples | Database Normalization with Real Life Examples | Designing Normalized Database
  5. Difference between Truncate and Delete, Drop in SQL | Truncate Delete and Drop Commands in SQL | SQL Difference between Truncate,Delete and Drop Commands | Real Life Examples of Truncate Delete and Drop Command
  6. Interview Questions for TCS | SQL Interview Questions for TCS | Interview Questions Asked in TCS | TCS Interview Questions
  7. Interview Questions for HCL | SQL Interview Questions for HCL | Interview Questions Asked in HCL | HCL Interview Questions | 20 Most Important SQL Interview Questions for HCL
  8. Real Time Scenarios in SQL Queries | SQL Scenarios

Leave a Reply

Your email address will not be published.


*