SQLplus provides several powerful tools and methods for accessing and manipulating the data stored in your Oracle database. This tutorial will take you through the basics of using the tool to access table metadata from the command line.
Before you start
Before you can use SQLplus to access data you will need:
- A working instance of Oracle.
- The SQLplus client tools installed on your PC.
- An database logon with the relevant permissions.
Yadda
With the pre-requisites in place, you can now begin the process of accessing and manipulating data using SQLplus.
- Fire up SQLplus from the command prompt using the credentials supplied by your administrator:
Enter your password when prompted and press enter:If login has been successful you should see an SQL command prompt:
- You can now execute any combination of SQLPlus statements on the database tables to which you have access. For instance, the command show user will show you which user account you are currently logged on with:
- To find out which tables you “own”, you can use the desc user_tables which accesses a view that contains details of table ownership. The command will return a list of columns including data types:
Note that the SQLPlus command desc is short for describe and it totally different to the PSQL desc command which relates to sorting data in a descending order. - Next you can use the following command to compile a list of the tables:
select table_name from user_tables;
This will then result in a much smaller list congaing just the tables to which you have access:
- You can now use the desc command to view the structure of any of those tables.
desc departments
returns:
You can clearly see the data type stored in each column, along with details of whether the column support null values or not. Creating a new department record will require you to supply a DEPARTMENT_ID and a DEPARTMENT_NAME for instance. - SQLPlus also allows you to view table constraints. The user_constraints view contains the metadata you need to formulate a query that will return constraints for a particular table.
desc user_constraints
returns:
- Using this metadata you can then view specific table constraint information with the following query:
select constraint_name, constraint_type, from user_constraints where table_name = 'EMPLOYEES';
To understand the constraints for this table:
P = Primary key
U = Unique constraint
C = Check constraint
R = Relational (foreign key constraints) - There is even more information available about constraints in the user_cons_columns view which can be accessed with:
desc user_cons_columns
- This information then provides an alternative method for finding the primary key on a specific table:
select column_name from user_cons_column where table_name = 'EMPLOYEES' and constraint_name = 'EMP_EMP_ID_PK';
returning just the name of the primary key column:
Obviously much of this information can be accessed using a graphical tool, but it helps to know how to obtain the same data using SQLPlus just in case your preferred method is unavailable.
For further guidance, check out the Firebox Training YouTube channel for a video version of this tutorial.
Please Share This Knowledge With Others!