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;
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
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;
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;
Click on Topic You want to learn:
- History of SQL
- SQL Create Table(DDL in SQL)
- SQL DML Statements(INSERT,UPDATE,DELETE)
- SQL Select Statement Execution
- Operators in SQL
- Views in SQL
- Materialized View in SQL
- Joins in SQL
- Inner Join / Outer Join
- Full Outer Join / Cartesian Join
- Union and Union ALL
- Intersect and Minus
- Indexing in SQL
- Rank and Dense Rank
- SubQueries and Correlated Subqueries
- Parser and Optimizer
- Oracle 11 G new Features
- SQL Functions List
- Constraints in SQL
- Database Normalization
- Table Partitioning
- Pivot in SQL
- Difference Between Truncate,Delete and drop
- Oracle System Tables
Unix Tutorials :
Hope this article is useful for you.If you like this article kindly comment.