DDL Wait Option
This feature allows a DBA to retry a DDL operation at certain intervals automatically, if the DDL operation couldn’t get a exclusive lock on the object.
Eg: You are trying to lock a table and received a “resource busy “ error (ORA-0054). As a DBA you retry the operation manually until you succeed.
In Oracle 11g you have a ddl_lock_timeout parameter, which will retry the operation with in a specified time at regular intervals until it’s successful or the specified time interval expires.
Sql> alter session set ddl_lock_timeout = 30;
This will prevent the time out error , if it couldn’t get a exclusive lock on the object, instead it will retry the operation many times till its successful or reaches 30 seconds.
Eg :
Session 1. I have grabbed a lock on the table.
SQL> select * from scott.test for update;
Session 2: I am trying to drop a column from the same table.
SQL> alter table scott.test drop column testing;
alter table scott.test drop column testing
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter session set ddl_lock_timeout=120;
Session altered.
Elapsed: 00:00:00.00
Session 1
SQL>Rollback;
Elapsed: 00:00:00.00
Session 2
SQL> alter table scott.test drop column testing;
Table altered.
Elapsed: 00:00:12.82
SQL>
Session 2 didn’t throw any resource busy errors this time. Idid a rollback on session 1. The command executed successfully after 12 seconds in session 2.
Virtual Columns
Oracle 11g lets you create virtual columns on the tables and populates the values automatically. In the previous releases you need to physically add a new column and fill the data or allow nulls in the new column. You additionally have to recompile all the dependent objects. Application team doesn’t have to write new code or create triggers to populate the new column.
Lets take a look at the emp table in scott schema;
Sql> create table scott.test as select * from scott.emp;
SQL> SELECT * FROM SCOTT.TEST;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Here I'll create a virtual column named income category, which lists the high, low and medium income group based on the salary.
SQL> ALTER TABLE SCOTT.TEST ADD INCOME_CATEGORY VARCHAR2(10)
2 GENERATED ALWAYS AS
3 (
4 CASE
5 WHEN SAL <=2000 THEN 'LOW' 6 WHEN SAL >2000 AND SAL <=3000 THEN 'MEDIUM' 7 WHEN SAL >3000 THEN 'HIGH'
8 END
9 ) VIRTUAL;
Table altered.
SQL> SET LINESIZE 400
SQL> SELECT * FROM SCOTT.TEST;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO INCOME
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------
7369 SMITH CLERK 7902 17-DEC-80 800 20 LOW
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 LOW
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 LOW
7566 JONES MANAGER 7839 02-APR-81 2975 20 MEDIUM
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 LOW
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 MEDIUM
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 MEDIUM
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 MEDIUM
7839 KING PRESIDENT 17-NOV-81 5000 10 HIGH
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 LOW
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 LOW
7900 JAMES CLERK 7698 03-DEC-81 950 30 LOW
7902 FORD ANALYST 7566 03-DEC-81 3000 20 MEDIUM
7934 MILLER CLERK 7782 23-JAN-82 1300 10 LOW
You can create indexes on Virtual colums.
Invisible indexes
Indexes are created to enhance search performance. Indexes are good in a majority of the cases , but I have seen cases the existence of an Index really hurts the performance. Your application may have sql statements which uses the index effectively and also have sql statements which perform poorly because of the same index. Oracle 11g has provided an option to make the index invisible to the optimizer. This way the user doesn’t have to drop an index or mark the index unusable for certain queries and recreate them for other set of queries.
Lets take a look at the scott.test table in the previous example.
SQL> select * from dba_indexes where table_name like 'TEST' and owner like 'SCOTT';
no rows selected
Elapsed: 00:00:00.04
SQL> create index scott.test_empno on scott.test (empno);
Index created.
SQL> create index scott.test_empno on scott.test (empno);
Index created.
SQL> set linesize 300
SQL> set autotrace on
SQL> select * from scott.test where empno=7902;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO INCOME
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------
7902 FORD ANALYST 7566 03-DEC-81 3000 20 MEDIUM
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3658239601
------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 42 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID TEST 1 42 2 (0) 00:00:01
* 2 INDEX RANGE SCAN TEST_EMPNO 1 1 (0) 00:00:01
The execution plan shows that the newly created index is used. Lets make the index invisible to the optimizer.
SQL> alter index scott.test_empno invisible;
Index altered.
Elapsed: 00:00:00.01
SQL>
SQL> select visibility from dba_indexes where owner like 'SCOTT' and INDEX_NAME LIKE 'TEST_EMPNO';
VISIBILIT
---------
INVISIBLE
Elapsed: 00:00:00.01
SQL> select * from scott.test where empno=7902;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO INCOME
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------
7902 FORD ANALYST 7566 03-DEC-81 3000 20 MEDIUM
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1 42 3 (0) 00:00:01
* 1 TABLE ACCESS FULL TEST 1 42 3 (0) 00:00:01
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7902)
Statistics
----------------------------------------------------------
193 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
1070 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
You could see that the optimizer didn’t use the newly created index.
You could make the optimizer use invisible indexes by specifying a index hint or set the “OPTIMIZER_USE_INVISIBLE_INDEXES” to True at the session level or database level.To make index Visible again.
SQL> alter index scott.test_empno visible;
Index altered.
Elapsed: 00:00:00.01
SQL> select visibility from dba_indexes where owner like 'SCOTT' and INDEX_NAME LIKE 'TEST_EMPNO';
VISIBILIT
---------
VISIBLE
Elapsed: 00:00:00.00
Case sensitive passwords
The case sensitive passwords is a new feature in Oracle 11g. Developers using sqlserver
Databases are familiar with case sensitive passwords. Oracle 11g gives you the flexibility to turn on and off password case sensitivity.
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL>
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
System altered.
SQL> create user praveen identified by Praveen;
User created.
SQL> grant create session to praveen;
Grant succeeded.
SQL> connect praveen/praveen
Connected.
SQL> connect / as sysdba
Connected.
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;
System altered.
SQL> connect praveen/praveen
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect praveen/Praveen
Connected.
SQL>
To create a case in-sensitive password in the password file, recreate the password file using ignorecase=y option.
$ orapwd file=orapwDB11Gb entries=100 ignorecase=y password=mypassword
-----------------------------------------------------------------------------------------------------------------------
I'll discuss more features in next post.
------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment