| Lesson 6 | Removing a restriction |
| Objective | Use SQL to remove a restriction |
Removing Restrictions in SQL*Plus
SQL *Plus is a command-line interface for managing Oracle databases. At times, you may face certain restrictions in SQLPlus that need to be removed or modified. This guide will walk you through the process of removing a restriction in SQL*Plus using SQL commands and Oracle data dictionary views.
Identifying the Restriction
Before removing a restriction, it's essential to identify the specific constraint that is causing issues. The Oracle data dictionary views can help you with this. For instance, you can use the DBA_CONSTRAINTS or ALL_CONSTRAINTS views to retrieve information about constraints defined on a particular table.
Example:
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME' AND OWNER = 'YOUR_SCHEMA_NAME';
Replace 'YOUR_TABLE_NAME' and 'YOUR_SCHEMA_NAME' with the actual table and schema names, respectively.
This query will return a list of constraints along with their types and statuses.
Removing the Restriction
Once you have identified the constraint to be removed, you can use the ALTER TABLE statement with the DROP CONSTRAINT clause to remove the restriction.
Example:
ALTER TABLE YOUR_SCHEMA_NAME.YOUR_TABLE_NAME
DROP CONSTRAINT CONSTRAINT_NAME;
Replace YOUR_SCHEMA_NAME, YOUR_TABLE_NAME, and CONSTRAINT_NAME with the appropriate values. This command will remove the specified constraint from the table.
Verifying the Removal
After removing the constraint, you can run the initial query again to confirm that the restriction has been removed successfully.
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME' AND OWNER = 'YOUR_SCHEMA_NAME';
If the constraint is successfully removed, it will no longer appear in the results.
Keep in mind that removing constraints can affect data integrity and referential integrity. Always ensure that removing a restriction is necessary and will not lead to data inconsistency or corruption.
By now, you probably have a pretty good understanding of how SQL*Plus uses the
information you place into the PRODUCT_PROFILE table to limit its environment for an individual user. SQL*Plus looks at the rows in the table and imposes the restrictions defined by those rows. To remove a restriction, you have to delete the relevant row in the table.
Syntax
If you want to remove the restriction that prevents user BRIAN from accessing the INSERT command in SQL*Plus, use the following SQL statement:
DELETE * FROM PRODUCT_PROFILE WHERE product = ‘SQL*Plus’
AND userid = ‘BRIAN’ AND attribute = ‘INSERT’;
If you want to remove the restriction that prevents all users whose username begins with the letter B from using the INSERT statement, use the following SQL statement:
DELETE * FROM PRODUCT_PROFILE WHERE product = ‘SQL*Plus’
AND userid = ‘B%’ AND attribute = ‘INSERT’;
Keep in mind that these two statements are not comparable. To remove a restriction, you have to delete a specific row from the PRODUCT_PROFILE table. If you add both of these restrictions to the table, the second SQL statement will allow BRIAN to use the INSERT command. The first restriction will still exist, preventing access.
In the same way, you must delete the specific row you used to implement the restriction. Even if you used the following statement to delete all restrictions for BRIAN from the PRODUCT_PROFILE table
DELETE * FROM PRODUCT_PROFILE
WHERE product = ‘SQL*Plus’
AND userid = ‘BRIAN’;
you would still have the row that restricts all users whose name begins with the character B from using the INSERT statement. Understanding how to remove restrictions should help you understand that you should develop a well-thought-out scheme for using product profiles to make them as usable as possible. In the next lesson, you will learn how to report on the restrictions imposed through a product profile.
