Archive

Archive for the ‘oracle_11g_new_features’ Category

11g add a column with not null to the existing table

July 1, 2011 Leave a comment

Prior to Oracle database 11g, adding a new column with a default NOT NULL value were a challenge.if we have table with millions of rows and if we try to add not null column then we will get following error.

/home/oracle=>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 – Production on Mon Jun 27 17:01:56 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> create table add_notnull_column (customer_name varchar2(10),customer_city varchar2(10));

Table created.

SQL> insert into add_notnull_column values(‘UMA’,’CYPRESS’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from add_notnull_column;

CUSTOMER_N CUSTOMER_C
———- ———-
UMA        CYPRESS

SQL> alter table add_notnull_column add  ACTIVE_IND char(1) not null;
alter table add_notnull_column add  ACTIVE_IND char(1) not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

In this case we need to create a new table with same structure and populate the data and then rename to the original table.

Oracle database 11g offers a new quick, easy, instant solution; instead of updating ALL the rows of the table, Oracle 11g now updates the metadata only

<oracle:ora11gr21>:/home/oracle=>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 27 14:58:26 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Connected.
SQL> create table add_notnull_column (customer_name varchar2(10),customer_city varchar2(10));
insert into add_notnull_column values(‘UMA’,’CYPRESS’);
commit;
Table created.

SQL>
1 row created.

SQL> select * from add_notnull_column;

CUSTOMER_N CUSTOMER_C
———- ———-
UMA        CYPRESS

SQL> alter table add_notnull_column add  ACTIVE_IND char(1) default ‘Y’ not null;

Table altered.

SQL> select * from add_notnull_column;

CUSTOMER_N CUSTOMER_C A
———- ———- –
UMA        CYPRESS    Y

Advertisements

DDL_LOCK_TIMEOUT — new 11g wait parameter

June 27, 2011 Leave a comment

DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.

If a lock is not acquired before the timeout period expires, then an error is returned.

SQL> alter session set ddl_lock_timeout=1000001;
ERROR:
ORA-00068: invalid value 1000001 for parameter ddl_lock_timeout, must be
between 0 and 1000000

Elapsed: 00:00:00.00

<oracle:ora11gr21>:/home/oracle=>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 26 20:19:28 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter ddl_lock_timeout

NAME                                 TYPE        VALUE
———————————— ———– ——————————
ddl_lock_timeout                     integer     0

As of now DDL lock wait option is not set.

 

SQL> create table ddl_lock_timeout_test (CUSTOMER_ID number(10),
2  CUSTOMER_NAME varchar2(20));

Table created.

SQL> select * from ddl_lock_timeout_test;

CUSTOMER_ID CUSTOMER_NAME
———– ——————–
1 UMA

open a one more session and update the existing row.

SQL> update ddl_lock_timeout_test set CUSTOMER_NAME=’UMA KATRU’ where CUSTOMER_ID=1;

1 row updated.

alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);

 

SQL> alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);
alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

SQL> set timing on
SQL> alter session set ddl_lock_timeout=60;

Session altered.

Elapsed: 00:00:00.02

If we issue alter table statement again, it will not fail immediately instead it will keep trying to get an exclusive lock and execute the command for next 60 seconds and if it fails to obtain lock it will return an error.

SQL> alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);
alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Elapsed: 00:00:59.02

Now let us issue the same alter statement again. After few seconds or so, go to previous session from which we fired the update statement and commit the change. Once the change is committed, alter table command will go through fine from other session.

SQL> commit;

Commit complete.

SQL> alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);

Table altered.

Elapsed: 00:00:16.34

 

Virtual column in oracle 11g

June 6, 2011 Leave a comment

Virtual columns  allows users to create columns whose data is not supplied by the user, but it is derived by oracle server implicitly from other columns. Most importantly, their disk space consumption is NULL because their data is not stored in the table.

Key points about Virtual columns:

1)Data can not be inserted into virtual column

2) The virtual column and the columns to be used in the derivation of virtual column data must belong to the same table.

3)It can be used as normal columns without any restriction. It can be constrained, indexed, and can be used in DML or DDL statements. Note that it cannot be updated in UPDATE statement

4)We can partition the table based on virtual column

We can use below query to define virtual columns defined in the users schema.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE VIRTUAL_COLUMN = ‘YES’;

5)we can not create virtual columns on Temporary tables, object types, clusters, External tables and Index Organized Tables

I tried to create table using following statment and used sysdate in the virtual column expression and got the following error.

Syntax

COLUMN [DATA TYPE] [GENERATED ALWAYS] AS (EXPRESSION) [VIRTUAL]

create table virtual_column (MEMBER_ID number(10),
MEMBER_NAME varchar2(25),
BIRTH_DATE date,
AGE_IN_MONTHS number(5) AS(SYSDATE-BIRTH_DATE))

ORA-54002: only pure functions can be specified in a virtual column expression

create table virtual_column (MEMBER_ID number(10),
MEMBER_NAME varchar2(25),
MONTHLY_SALARY number(10,2),
ANNUAL_SALARY number(10,2) AS(12*MONTHLY_SALARY))

SQL> insert into virtual_column values(1,’POOJITHA’,5000,10000);
insert into ukatru.virtual_column values(1,’POOJITHA’,5000,10000)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(1,’POOJITHA’,5000);

SQL> select * from ukatru.virtual_column;

MEMBER_ID MEMBER_NAME               MONTHLY_SALARY ANNUAL_SALARY
———- ————————- ————– ————-
1 POOJITHA                            5000         60000

Create index on virtual columns:

CREATE INDEX IDX_ANUAL_SALARY ON virtual_column (ANNUAL_SALARY);

If you query user_indexes table the index  is created as function based index.

SELECT INDEX_NAME,  INDEX_TYPE, FUNCIDX_STATUS
FROM   USER_INDEXES
WHERE TABLE_NAME = ‘VIRTUAL_COLUMN’;

INDEX_NAME                     INDEX_TYPE                  FUNCIDX_
—————————— ————————— ——–
IDX_ANUAL_SALARY               FUNCTION-BASED NORMAL       ENABLED

we can use alter table command to add virtual column to the table.

alter table virtual_column
add QUARTERLY_SALARY AS (MONTHLY_SALARY*3)

Adding constraint on the virtual column.

ALTER TABLE virtual_column
ADD CONSTRAINT QUARTERLY_SALARY_CHECK CHECK(QUARTERLY_SALARY != 0);

SQL> insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(2,’POOJITHA’,0);
insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(2,’POOJITHA’,0)
*
ERROR at line 1:
ORA-02290: check constraint (QUARTERLY_SALARY_CHECK) violated

pivot in oracle 11g

June 6, 2011 Leave a comment

Pivot function is very important function in some interface projects and dataware house environments.

consider following pivot_test table for this exercise.

create table pivot_test
(customer_id number(10),
customer_category varchar2(10),
customer_account number(10)
)

insert into pivot_test values(1,’MED’,5000);
insert into pivot_test values(1,’RX’,5001);
insert into pivot_test values(1,’CAP’,5002);
insert into pivot_test values(1,’REV’,5003);
commit;

SQL> select * from pivot_test;
CUSTOMER_ID|CUSTOMER_C|CUSTOMER_ACCOUNT
———–|———-|—————-
1|MED       |            5000
1|RX        |            5001
1|CAP       |            5002
1|REV       |            5003

SQL> select *
from
(select CUSTOMER_ID
, CUSTOMER_CATEGORY
, CUSTOMER_ACCOUNT
from pivot_test ) s
PIVOT (sum(CUSTOMER_ACCOUNT)
for CUSTOMER_CATEGORY in (‘MED’,’RX’,’CAP’,’REV’)
)

CUSTOMER_ID|   ‘MED’|    ‘RX’|   ‘CAP’|   ‘REV’
———–|——–|——–|——–|——–
1|    5000|    5001|    5002|    5003

11g Enhancement in oracle sequence fetching

June 4, 2011 Leave a comment

In 10g we have created sequence called ora10g and we use e a SELECT..INTO..FROM DUAL construct.

DECLARE
i PLS_INTEGER;
BEGIN
i := ora10g.NEXTVAL;
DBMS_OUTPUT.PUT_LINE(‘Printing NextVal =’ || TO_CHAR(i));
END;
/

Error :

ORA-06550: line 4, column 11:
PLS-00201: identifier ‘ORA10G.NEXTVAL’ must be declared
ORA-06550: line 4, column 6:
PL/SQL: Statement ignored

Correct usage :

DECLARE
i PLS_INTEGER;
BEGIN
/* i := ora10g.NEXTVAL; */
SELECT ukatru.ora10g.NEXTVAL AS snval INTO i FROM dual;
DBMS_OUTPUT.PUT_LINE(‘Printing Nextval=’ || TO_CHAR(i));
END;
/

Printing Nextval=2

==========================================================

oracle 11g  has added sequence fetching (using NEXTVAL and CURRVAL) to the list of valid PL/SQL expressions. This means we can use sequence expressions in PL/SQL anywhere we can validly use a number.

DECLARE
i PLS_INTEGER;
BEGIN
i := ora11g.NEXTVAL;
DBMS_OUTPUT.PUT_LINE(‘Printing Nextval in 11g environment=’ || TO_CHAR(i));
END;
/

Printing Nextval in 11g environment=1

 

oracle 11g new feature—Automatic Memory Management(AMM)

June 4, 2011 Leave a comment

This article  explains Oracle Database 11g New Feature(AMM).

Memory Parameters:

oracle 11g introduced new feature called automatic memory management(AMM).We can expand or shrink both sga and pga based on the instance demands.

To enable this feature we need to set the following parameters.

1)memory_max_target
2)memory_target

SQL> show parameter memory

NAME                                 TYPE        VALUE
———————————— ———– ——————————
memory_max_target                    big integer 0
memory_target                        big integer

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 defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.

In oracle 10g we need to specify sga_target,pga_aggregate_target parameters (Oracle Automatic Shared Memory Management (ASMM)) then oracle will automatically allocated size for Buffer cache,Shared pool, large pool etc.

Oracle 11g introduced new parameter called memory_target parameter which is used for sga and pga configuration.

when we specify memory_target parameter then we don’t need to specify sga_target,pga_aggregate_target parameters for memory configuration.

Enabling automatic memory management(AMM):

on linux system make sure you have configure /dev/shm before enabling automatic memory management.

/dev/shm is nothing but implementation of traditional shared memory concept. It is an efficient means of passing data between programs.

The shared memory file system should be big enough to accommodate the MEMORY_TARGET and MEMORY_MAX_TARGET values, or Oracle will throw the following error.

ORA-00845: MEMORY_TARGET not supported on this system

We can not use hugepage settings on linux system if we want to use automatic memory management feature in oracle 11g.

dbsl9002<oracle:oradv11>:/home/oracle=>df -h | grep tmpfs
tmpfs                1011M  192M  820M  19% /dev/shm

SQL> alter system set memory_target=512M scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2228200 bytes
Variable Size             377487384 bytes
Database Buffers          150994944 bytes
Redo Buffers                3751936 bytes
Database mounted.
Database opened.
SQL> show parameter sga

NAME                                 TYPE        VALUE
———————————— ———– ——————————
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 512M
sga_target                           big integer 384M
SQL> show parameter memory

NAME                                 TYPE        VALUE
———————————— ———– ——————————
hi_shared_memory_address             integer     0
memory_max_target                    big integer 512M
memory_target                        big integer 512M
shared_memory_address                integer     0
SQL> show parameter pga

NAME                                 TYPE        VALUE
———————————— ———– ——————————
pga_aggregate_target                 big integer 128M

In the above scenario the lower bounds of  PGA_AGGREGATE_TARGET and SGA_TARGET parameters are set to
128 MB and 384 MB, respectively.

Monitoring and Tuning automatic memory management:

Oracle 11g includes four new V$ views to support automatic memory management:

V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_RESIZE_OPS
V$MEMORY_TARGET_ADVICE

To display current status of the memory components, use the following query:

COLUMN component FORMAT A30

SQL> SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
FROM V$MEMORY_DYNAMIC_COMPONENTS;

COMPONENT                      CURRENT_SIZE        MIN        MAX
—————————— ———— ———- ———-
shared pool                             224        224        224
large pool                                4          4          4
java pool                                 4          4          4
streams pool                              0          0          0
SGA Target                              384        384        384
DEFAULT buffer cache                    144        144        144
KEEP buffer cache                         0          0          0
RECYCLE buffer cache                      0          0          0
DEFAULT 2K buffer cache                   0          0          0
DEFAULT 4K buffer cache                   0          0          0
DEFAULT 8K buffer cache                   0          0          0

COMPONENT                      CURRENT_SIZE        MIN        MAX
—————————— ———— ———- ———-
DEFAULT 16K buffer cache                  0          0          0
DEFAULT 32K buffer cache                  0          0          0
Shared IO Pool                            0          0          0
PGA Target                              128        128        128
ASM Buffer Cache                          0          0          0

16 rows selected.

SQL> SELECT * FROM v$memory_target_advice ORDER BY memory_size;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
———– —————— ———— ——————- ———-
256                 .5           42              1.0244          0
384                .75           41                   1          0
512                  1           41                   1          0
640               1.25           41                   1          0
768                1.5           41                   1          0
896               1.75           41                   1          0
1024                  2           41                   1          0

7 rows selected.

To know how Oracle has modified the memory area sizes by time, issue the following query:
select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME;

 

 

 

 

%d bloggers like this: