How much do you understand the implication of running a DDL on your database schema ?
So, this section has been presented in a mini FAQ format so as to keep the content concise to the point.
What is DDL ?
Data definition language (DDL) statements let you to perform these tasks:
Create, alter, and drop schema objects
Grant and revoke privileges and roles
Analyze information on a table, index, or cluster
Establish auditing options
Add comments to the data dictionary
The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.
The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.
Oracle Database implicitly commits the current transaction before and after every DDL statement.
Source: Oracle documentation
What are the side -effects ?
- Implicit commit before and after every DDL statement
- If you alter the definition of a referenced object, dependent objects may or may not continue to function without error, depending on the type of alteration. For example, if you drop a table, no view or procedure or package based on the dropped table is usable. Same is the effect with ALTER.
Dependency management - How does this affect us ?
- When DDLs are checked-in, it may result in invalidating the objects that have direct or indirect dependency.
- The invalidated object could be a trigger on a table, which might even show you a failed INSERT result because one of the trigger in invalid.
If you are interested further in knowing the invalid objects in your schema...
Run the following query to view the invalid objects in your database
- select object_name, object_type from user_objects where status !='VALID' order by object_type, object_name;
Run the query below BEFORE and AFTER running a DDL to know how many objects are being invalidated by your change.
- select count(*) from user_objects where status !='VALID';
How do you compile the invalid objects ?
I have a log on how to compile invalid DB objects in the same blog.
http://databasetiger.blogspot.in/2009/04/recompiling-to-validate-invalid-objects.html
So, this section has been presented in a mini FAQ format so as to keep the content concise to the point.
What is DDL ?
Data definition language (DDL) statements let you to perform these tasks:
Create, alter, and drop schema objects
Grant and revoke privileges and roles
Analyze information on a table, index, or cluster
Establish auditing options
Add comments to the data dictionary
The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.
The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.
Oracle Database implicitly commits the current transaction before and after every DDL statement.
Source: Oracle documentation
What are the side -effects ?
- Implicit commit before and after every DDL statement
- If you alter the definition of a referenced object, dependent objects may or may not continue to function without error, depending on the type of alteration. For example, if you drop a table, no view or procedure or package based on the dropped table is usable. Same is the effect with ALTER.
Dependency management - How does this affect us ?
- When DDLs are checked-in, it may result in invalidating the objects that have direct or indirect dependency.
- The invalidated object could be a trigger on a table, which might even show you a failed INSERT result because one of the trigger in invalid.
If you are interested further in knowing the invalid objects in your schema...
Run the following query to view the invalid objects in your database
- select object_name, object_type from user_objects where status !='VALID' order by object_type, object_name;
Run the query below BEFORE and AFTER running a DDL to know how many objects are being invalidated by your change.
- select count(*) from user_objects where status !='VALID';
How do you compile the invalid objects ?
I have a log on how to compile invalid DB objects in the same blog.
http://databasetiger.blogspot.in/2009/04/recompiling-to-validate-invalid-objects.html
No comments:
Post a Comment