DDL Wait Option:
You might have come across with the scenario that whenever you try to execute an alter statement on a particular table, instead of getting a success message on your window an error occurs as displayed below
ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
This will be irritating most of the time.
The reason behind this error is, some other session is doing some activity on this table and has acquired a lock because of which an exclusive lock request for changing the structure of the table is not provided and hence the error message. The person trying to alter the table has to keep on hitting the alter statement until the next session complete the task and do a commit. This is a painful task. In a large system where the business activity happens very frequently the possibility of having the tables objects unlocked is comparatively less.
Oracle 11g comes up with a solution for this. Set the waiting period for the lock.
SQL> alter session set ddl_lock_timeout = 10;
Now, when the DDL statement is submitted, it will wait for 10 sec and in this 10 sec keep on checking for an exclusive lock to do the changes and if not found throws error.
This parameter value can be set at session level and system level as well.
ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10;
Adding columns with default value:
Most of the time a developer will come across a situation where he/she has to add one additional column to an exiting table having data. Say for example our table has 4 million data. And what if this column should have a NOT NULL constraint?
The only option available is to write the alter statement specifying the default value as below.
SQL> alter table emp add emp_spousename varchar2(20) default 'xxx' NOT NULL;
Now we have a issue here, if we proceed with this statement. Oracle will add the additional column and update default value for all the existing records. This process not only take a long time but fills up the undo segment, generates a large amount of redo and performance issue as well. So we have to go for a down time for the application to complete this task. But not, if we are using 11g.
In Oracle 11g, this particular column will be added but the column values are not updated with the default value. Hence there is no risk of filling up of undo segment and generating redo files and no performance issue as well. For new records added the default value will be set and for existing records, when a user fires a select query Oracle refers the data dictionary to get info on this column and substitutes the default value.
Virtual Columns:
Scenario: A new col has to be added to a table for which the value is generated based on some business condition.
Here the business don't want to do any changes to the code to populate value into the column. Now only option left to the developer is to create a trigger to populate value into this particular column. This is a bad habit as it has performance impact due to switching context from and into the trigger codes.
Oracle 11 g comes up with a solution for this. Create a virtual column.
When we say virtual columm its really virtual. You have the column created in table but the functionality is different from normal cols.
SQL> create table sales
2 (
3 sales_id number,
4 cust_id number,
5 sales_amt number,
6 sale_category varchar2(6)
7 generated always as
8 (
9 case
10 when sales_amt <= 10000 then 'LOW'
11 when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM'
12 when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
13 else 'ULTRA'
14 end
15 ) virtual
16 );
Note lines 6-7; the column is specified as "generated always as", meaning the column values are generated at runtime, not stored as part of the table. That clause is followed by how the value is calculated in the elaborate CASE statement. Finally, in line 15,"virtual" is specified to reinforce the fact that this is a virtual column. Now, if you insert some records:
SQL> insert into sales (sales_id, cust_id, sales_amt) values (1,1,100); 1 row created. SQL> insert into sales (sales_id, cust_id, sales_amt) values (2,102,1500); 1 row created. SQL>insert into sales (sales_id, cust_id, sales_amt) values (3,102,100000); 1 row created. SQL> commit; Commit complete. SQL> select * from sales; SALES_ID CUST_ID SALES_AMT SALE_C ---------- ---------- ---------- ------ 1 1 100 LOW 2 102 1500 LOW 3 102 100000 MEDIUM 3 rows selected.
You can't insert value in to this particular column.It will throw error.
You can create index and it will be a function-based index
You can also partition this particular column.
Invisible indexes
Indexing a column wont be helping us always. So we end up analyzing the impact of adding one index to a column.
You created an index on a column, ran the query which use this column in its WHERE clause and analyzed the performance. Now you want to test it without index and so you removed the index and later you need to recreate it. The recreation of an index is an expensive process.
Oracle 11g comes up with a solution. Make the index invisible. When you say the index to be invisible the optimizer wont be able to see this index and query is processed without index. If you want to use this invisible index in your query you have to specifally mention that using Hint.
SQL> alter index index_name invisible;
select /*+ index (table index_name) */ id form emp;
By giving this hint, Oracle optimizer will be able to see the index and it will make use of the index while generationg the data.
Alternatively, you can set a session-level parameter to use the invisible indexes:
SQL> alter session set optimizer_use_invisible_indexes = true;
You can see details about indexes in user_indexes table.
Read only tables.
This is mainly use in DWH. There use to be a periodical updates on every table. At this point of time we may come across a condition that the users should not do any DML statement on the tables.
To achieve this we either revoke the access or triggers with alert or create VPD (virtual private database) policy on the tables.
Revoking access is not advisable, triggers has performance issue and VPD policy eventhough better than triggers gives a wrong error message to the user.
Oracle 11g comes up with a new solution: Change the read only status
SQL> alter table emp read only; Table altered.
Now when a user tries to issue a DML such as that shown below:
SQL> delete trans;
Oracle Database 11g throws an error right away:
delete trans
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TRANS"
The error message does not reflect the operation to the letter but conveys the message as intended, without the overhead of a trigger or VPD policy.
When you want to make the table update-able, you will need to make it read/write, as shown below:
SQL> alter table trans read write; Table altered.
Now DMLs will be no problem:
SQL> update trans set amt = 1 where trans_id = 1; 1 row updated.
With creating read only option an user is only blocked from DML statements but not from DDL, the user can perform DDLs like creating index, managing partition etc...
Fine-Grained Dependency Tracking
Create a new table, create a view on this table selecting few columns for information hiding. On checking the dependency status of the view you can see this view depends on above table and status in VALID.Change the sturcture of the table, say add a new column. Check the dependency of view, you can see it as INVALID even though that column is not used in view, as it is a child depending on parent table. This happens only in 10g and all previous releases.
Your application go for toss, Recompile the view will help you to make it active.
Oracle 11g wont invalidate the view is such scenario. All dependent objects of the view, such as other views, packages and procedures are also valid.
Overall availability of entire application is ensured. No need to stop the app during some changes.
If the column involved in view is altered then view go inactive which is desirable.
This scenario is applicable with other DB objects like procedures and packages as well. For example you have created a package which is being called form one of your function. Here your function depend on the package. Now for some reason you modified your package to add one more procedure and your function goes invalid in 10g and all below versions but not in 11g.
Here there is one exceptional scenario where if the new procedure is added at first position the depending object, function will be invalid in 11g as well because the slot provided for the procedures in package is rearranged. If we are adding new procedure after the existing one, we merely add one additional slot.
****
Hope this is helpful. Phoenix
