Oracle 11g new features: Several features

I will try to note here some Oracle 11g features.
--------------------------------------------------------------------------------------------------

KILL RAC INSTANCE`s SESSION

--------------------------------------------------------------------------------------------------

Now starting Oracle 11g we can kill/disconnect session using third parameter (instance id)

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

Before this we have to connect essential grid and from there kill session:

ALTER SYSTEM KILL SESSION 'sid,serial#;

Let`s check:

Suppose we have Rac with 4 nodes. We need to kill session from 2-nd node.

select

   inst_id,

   substr(b.sid,1,5) sid,

   b.serial# serial#,

   b.username username,

   b.program program

from

   gv$session b,

   v$process a

where

b.paddr = a.addr

and b.username='APPL_USER' // use needed username

order by 1;

INST_ID	SID	SERIAL#	USERNAME	PROGRAM
1	271	19783	APPL_USER	toad.exe
1	280	49033	APPL_USER	oracle@grid01  (PZ99)
2	278	13828	APPL_USER	oracle@grid02  (PZ99)
3	292	53910	APPL_USER	Toad.exe
3	298	48245	APPL_USER	plsqldev.exe
3	253	739	APPL_USER	toad.exe
3	230	2798	APPL_USER	oracle@grid03 (PZ99)
4	270	17328	APPL_USER	Toad.exe
4	248	51874	APPL_USER	oracle@grid04 (PZ99)

ALTER SYSTEM KILL SESSION '278,13828,@2';

--------------------------------------------------------------------------------------------------

TABLE READ ONLY 

--------------------------------------------------------------------------------------------------

Another interesting feature of Oracle 11g is now we can switch table state from READ WRITE to READ ONLY and back.

SQL> alter table my_tbl read only;

Table altered.

After status of table changed no one can make DML on base table.

SQL> insert into my_tbl values(1);

insert into my_tbl values(1)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "ULFET"."MY_TBL"

SQL> delete from my_tbl;
delete from my_tbl
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "ULFET"."MY_TBL"

SQL>

Also we could not alter table:

SQL> alter table my_tbl add (name varchar2(10));
alter table my_tbl add (name varchar2(10))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "ULFET"."MY_TBL"

But instead of DML user can make (add/remove partition in a nutshell maintain partition, create index etc)

SQL> create index my_tbl_idx on my_tbl (id);

 Index created.

To switch back:

SQL> alter table my_tbl read write;

Table altered.

To see table`s status use  user/all/dba_tables tables:

select table_name, read_only from user_tables where table_name = ‘MY_TBL’;

TABLE_NAME     READ_ONLY

------------------       ---------

MY_TBL              NO 

--------------------------------------------------------------------------------------------------

SEQUENCE 

--------------------------------------------------------------------------------------------------

Before Oracle 11g we should use sequence with inside select statement like this select seq1.nextval into defined_variable...

But now starting 11g we can easly use without on select ... into

create sequence seq1;

declare
seq_val number;
begin
seq_val := seq1.nextval;
dbms_output.put_line(seq_val);
end;

But before 11g realese we used above statement like this:

Declare
seq_val number;
begin
select seq1.nextval into seq_val from dual;
dbms_output.put_line(seq_val);
end;

However 11 g we use sequence easyly, On Oracle 12 c we will start to use default value of column which can use a sequence.nextval also, we can create table column with incremental sequence like MySQL

--------------------------------------------------------------------------------------------------

CASE SENSITIVE PASSWORDS  

--------------------------------------------------------------------------------------------------

Before Oralce 11g user passwords do not mean sensitive. Otherwise password=Password=PASSWORD=PaSsWoRd were identical. But now we can set SEC_CASE_SENSITIVE_LOGON initialization parameter gives control over case sensitive passwords. This parameter is dynamic and not needed to restart instance

SQL> show parameter sec_case

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon             boolean     TRUE

SQL> 

Create new user with sensitive password and grant connect role to him:

SQL> create user case_test identified by Case_Test;

User created.

SQL> grant connect to case_test;

Grant succeeded.

SQL> 

Now try to connect with new created user

SQL> conn case_test/Case_Test

Connected.

Successed!

SQL> conn case_test/case_test

ERROR:

ORA-01017: invalid username/password; logon denied

Failed!

SQL>

Set sec_case_sensitive_logon`s value to false

SQL> conn /as sysdba

Connected.

SQL> alter system set sec_case_sensitive_logon=FALSE;

System altered.

SQL>  

Now try again to connect with case_test user with several sensitive passwords

SQL> conn case_test/case_test

Connected.

SQL> conn case_test/CASE_test

Connected.

SQL> conn case_test/Case_Test

Connected.

SQL> 

--------------------------------------------------------------------------------------------------

SEC_MAX_FAILED_LOGIN_ATTEMPTS 

--------------------------------------------------------------------------------------------------

SEC_MAX_FAILED_LOGIN_ATTEMPTS parameters uses by Oracle drops the connection after the specified number of login attempts fail for any user. 
Default value is 10

--------------------------------------------------------------------------------------------------

MEMORY_TARGET and MEMORY_MAX_TARGET 

--------------------------------------------------------------------------------------------------

In Oracle 11g automatic memory management feature is developed. Both the system global area (SGA) and the program global area (PGA) will expand and shrink based on the instance demands. To enable this feature, set the following new parameters:

• MEMORY_TARGET  this parameter sets the system-wide usable memory that will be used by the instance for SGA and PGA. 
• MEMORY_MAX_TARGET  this parameter sets the maximum value you can set for MEMORY_TARGET parameter. 

--------------------------------------------------------------------------------------------------

CREATING INIT FILE FROM MEMORY 

--------------------------------------------------------------------------------------------------

Now using Oracle 11g we can create create a pfile or spfile from the current values of active instance.

Create pfile from memory;
Create spfile from memory;

--------------------------------------------------------------------------------------------------

SHRINK TEMPORARY TABLESPACE 

--------------------------------------------------------------------------------------------------

In Oracle 11g we can shrink temporary tablespace and tempfile.

ALTER TABLESPACE temp SHRINK SPACE KEEP 100M; 
ALTER TABLESPACE temp SHRINK TEMPFILE '/home/oracle/oracle/11.2.0/oradata/mydb/temp01.dbf'; 

-- to get information about temporary space usage 
SELECT * FROM DBA_TEMP_FREE_SPACE;

--------------------------------------------------------------------------------------------------

ENHANCED TRUNCATE STATEMENT  

--------------------------------------------------------------------------------------------------

Starting Oracle 11g R2 we can truncate table using "DROP ALL STORAGE" statement.

This means drop whole segment.

Truncate table table_name drop all storage;

--------------------------------------------------------------------------------------------------

VIRTUAL COLUMN 

--------------------------------------------------------------------------------------------------

Another interesting feature of Oracle 11g is provide us to create virtual column(s) rather than storing derived data. You may have a virtual column on based in the same row (other column`s value). Before we can create view on based table. Example creating annual salary on bases salary column which we made calculation:

create view ..... as
begin

select 

       employee_id, first_name||' '||last_name name, department_id, 

       job_id, salary, salary*12 annual_salary 

from employees;

end;

Now we can creative virtual column and do not create view for above action.

Create table employees_new_virtual_col

(

  employee_id number,

  first_name varchar2(10),

  last_name varchar2(20),

  department_id number,

  job_id char(10),

  salary number,

  annual_salary number generated always as (salary*12) virtual

);

When creating new virtual column use "generated always" and "virtual" key words.

Also Oracle 11g provide us create index on virtual column and partitioning. 

Please note that we do not need insert data for virtual column. Oracle will maintain for us.

Ex:

SQL> insert into employees_new_virtual_col values

(1, 'Omar', 'Tanriverdiyev', 10, 'IT_ADMIN',5000);

SQL> select employee_id emp_id, first_name name, job_id, salary, annual_salary 

from employees_new_virtual_col;

EMP_ID    NAME      JOB_ID          SALARY    ANNUAL_SALARY

1               OMAR      IT_ADMIN     5000           60000 

To list virtual column on the table select from data dictionary:

select column_name, data_default, virtual_column from dba/all/user_tab_cols;

--------------------------------------------------------------------------------------------------

OTHERS

--------------------------------------------------------------------------------------------------

New dynamic view was produced about default passwords.

Using DBA_USERS_WITH_DEFPWD view reports accounts with default passwords.

Also Oracle 11g provides a new password verification function with strong settings:

SQL> @$ORACLE_HOME/rdbms/admin/utlpwddmg.sql;

Default retention of AWR snapshots increased up to 8 days.