Fetching Multiple Rows with EXECUTE IMMEDIATE

execute immediate is very handy for executing dynamic SQL statements inside PLSQL block. It can accept IN and OUT parameters and can also return output values to PLSQL variables by into clause. But returning values can be tricky because of the multiple returning rows. For single row returns into clause works fine but for multiple rows, hitting the following exception is quite easy.

declare  
  
  v_cmd varchar2(100);
  v_table_owner varchar2(30) := 'SCH_OLTP';  
  v_table_name varchar2(30) := 'T_VISIT_STATS_DETAILED';
  
  v_column_name varchar2(30);
  
begin 

  v_cmd := 'select column_name from dba_tab_columns where owner=:v_table_owner and table_name=:v_table_name';
   
  execute immediate v_cmd into v_column_name
    using v_table_owner, v_table_name;
  
end;
/*
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 13
*/

So it is important to check the returning row count before using execute immediate is important. Or you can use the bulk collect clause to fetch multiple returning rows into an array.

declare 
  type typ_varcharlist is table of varchar2(30);
  
  col_list typ_varcharlist;  
  
  v_cmd varchar2(100);
  v_table_owner varchar2(30) := 'SCH_OLTP';  
  v_table_name varchar2(30) := 'T_VISIT_STATS_DETAILED';
  
begin 
  v_cmd := 'select column_name from dba_tab_columns where owner=:v_table_owner and table_name=:v_table_name';
  
  
  execute immediate v_cmd bulk collect into col_list 
    using v_table_owner, v_table_name;
  
  for n_ctl in 1 .. col_list.count() loop
    dbms_output.put_line (col_list(n_ctl));
  end loop;
  
end;
/*
DUMMY
SUCCESS
VISITED_USER_ID
FROM_IP
VISIT_DATE
PAGE_ID
*/

Reference:
Oracle PLSQL Users Guide
Thesis and Papers > Oracle 11g Execute Immediate

Compressing LOB segments with securefiles

After Oracle Database 11G we have a new storage option for the LOB segments which is securefiles. By enabling securefiles LOB segments can be compressed significantly. After I have faced an unusually extending tablespace (approximately 30G per day) which may halt the database because of this unplanned extend, I have start digging into the problem and find out this LOB column which hold the unstructured HTML data inside it, its size was more than 350Gb lying inside a total of 600Gb database which means more than half of the database is only this one column. This is just unacceptible. In the following example you can find the same scenerio on the test system. Here the uncomressed LOB column holds 22Gb of space.

select 
  OWNER, 
  TABLE_NAME, 
  COLUMN_NAME, 
  SEGMENT_NAME, 
  INDEX_NAME 
from 
  DBA_LOBS 
where 
  TABLE_NAME='AUD_PRODUCT_BIG_DATA';

/*
OWNER       TABLE_NAME            COLUMN_NAME   SEGMENT_NAME                INDEX_NAME
--------    --------------------  -----------   -------------------------   ------------------------
SCH_DMALL   AUD_PRODUCT_BIG_DATA  DESCRIPTION   AUD_PRODUCT_BIG_DATA_CLOB   SYS_IL0000126287C00004$$
*/

select bytes/1024/1024 from dba_segments where segment_name='SYS_LOB0000126287C00004$$';  
--22GB

After moving the LOB column with the storage option of "SECUREFILE" and "MEDIUM COMPRESS" option the size of the LOB segment decreased to approx. 833Mb. More than 1/25 compression which is as valuable as gold in this production database.

ALTER TABLE sch_dmall.aud_product_big_data move lob (description) store
AS
  SECUREFILE aud_product_big_data_clob (TABLESPACE TS_AUD_LOB_SEGMENT COMPRESS MEDIUM) parallel 4;

select bytes/1024/1024 from dba_segments where segment_name='AUD_PRODUCT_BIG_DATA_CLOB';  
--833.125

There are three levels of compression which is LOW MEDIUM and HIGH which defines the compression level. Of course the higher the level gets, the more CPU power you need to spend. here I used MEDIUM to see the result and it is pretty convincing to not to change the setting.


References:

http://blog.trivadis.com/b/mathiaszarick/archive/2012/03/05/lob-compression-with-oracle-strange-multiple-physical-reads.aspx

http://www.oracle-base.com/articles/11g/secure-files-11gr1.php

Oracle Restart hands on

Starting from Oracle Database 11g, a new product (or functionality) called Oracle Restart comes with the part of the Grid Infrastructure installation. It seems Oracle decided to use crsctl, crs_stat, and srvctl like RAC commands for also managing the processes of the single instance databases. This standardization seems handy to me as i have already get used to manage RAC databases day by day.

After upgraded one of the development databases in our data center from 10.2.0.4 to 11.2.0.2 as well as the ASM instance, i decided to spend some of my time to play with this new functionality.

As on the RAC installations status of the services can be investigated with the crs_stat -t command. I think it is understandable that there is not vip, ons, gsd services here as this is not a RAC database.

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    OFFLINE               
ora....ER.lsnr ora....er.type OFFLINE   OFFLINE               
ora.asm        ora.asm.type   OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6 

[oracle@rhel6]:/oracle > crs_stat
NAME=ora.DG_DB_ASM.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
STATE=OFFLINE

NAME=ora.LISTENER.lsnr
TYPE=ora.listener.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.asm
TYPE=ora.asm.type
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=ONLINE
STATE=ONLINE on rhel6

NAME=ora.diskmon
TYPE=ora.diskmon.type
TARGET=ONLINE
STATE=ONLINE on rhel6 

The processes of the CRS (it is "HAS" for single instance) is again controlled by crsctl as it is in the RAC installations. You can use check, start, stop options to manage the processes as usual. A small note; CRS processes in the RAC installation is not installed for the single instance installations. For the single instance installations, there is the HAS processes stands for "High Availability Services" and covers the cssd and diskmon processes.

[oracle@rhel6]:/oracle > crsctl check has
CRS-4638: Oracle High Availability Services is online
[oracle@rhel6]:/oracle > crsctl check css
CRS-4529: Cluster Synchronization Services is online
[oracle@rhel6]:/oracle > crsctl check resource ora.cssd

[oracle@rhel6]:/oracle > crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rhel6'
CRS-2673: Attempting to stop 'ora.cssd' on 'rhel6'
CRS-2677: Stop of 'ora.cssd' on 'rhel6' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'rhel6'
CRS-2677: Stop of 'ora.diskmon' on 'rhel6' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rhel6' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[oracle@rhel6]:/oracle > crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.

After my upgrade process Oracle Restart could not be able to manage the upgraded database. By using srvctl i added the database resource to the repository so that i can manage the database services by using srvctl command line tool. One of the nicest option is, by using the "-a" option and supplying dependent diskgroups of the database makes Oracle Restart to start the ASM and mount the related diskgroups before starting up the database.

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   ONLINE    ONLINE    rhel6   
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6   

[oracle@rhel6]:/oracle > srvctl add database -h          

Adds a database configuration to be managed by Oracle Restart.

Usage: srvctl add database -d db_unique_name -o oracle_home
  [-m domain_name] 
  [-p spfile] 
  [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] 
  [-s start_options] 
  [-t stop_options] 
  [-n db_name] 
  [-y {AUTOMATIC | MANUAL}] 
  [-a "diskgroup_list"]
-d db_unique_name      Unique name for the database
-o oracle_home         ORACLE_HOME path
-m domain              Domain for database. Must be set if database has DB_DOMAIN set.
-p spfile              Server parameter file path
-r role                Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
-s start_options       Startup options for the database. Examples of startup options are open, mount, or nomount.
-t stop_options        Stop options for the database. Examples of shutdown options are normal, transactional, immediate, or abort.
-n db_name             Database name (DB_NAME), if different from the unique name given by the -d option
-y dbpolicy            Management policy for the database (AUTOMATIC or MANUAL)
-a "diskgroup_list"    Comma separated list of disk groups
-h                     Print usage

[oracle@rhel6]:/oracle > srvctl add database -d ORCLT -o /oracle/orahome1
[oracle@rhel6]:/oracle >
[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   ONLINE    ONLINE    rhel6   
ora.ORCLT.db   ora....se.type OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6   

[oracle@rhel6]:/oracle > srvctl start database -d ORCLT
[oracle@rhel6]:/oracle > ps -ef | grep smon
oracle  9109530        1   0 15:46:08      -  0:00 ora_smon_ORCLT
oracle 11075806        1   0 15:43:50      -  0:00 asm_smon_+ASM
[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle >                                                                                                     
[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle > srvctl status database -d ORCLT
Database is running.

[oracle@rhel6]:/oracle >            
                    
[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   ONLINE    ONLINE    rhel6   
ora.ORCLT.db   ora....se.type ONLINE    ONLINE    rhel6   
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6   

[oracle@rhel6]:/oracle >

[oracle@rhel6]:/oracle > srvctl stop database -d ORCLT

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type ONLINE    ONLINE    rhel6   
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rhel6   
ora.asm        ora.asm.type   OFFLINE   ONLINE    rhel6   
ora.ORCLT.db   ora....se.type OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6

The second handy feature is "enable" and "disable" of the srvctl which configures the related objects restart options on host restart or restart of the process on failure.

[oracle@rhel6]:/oracle > srvctl enable -h 

The SRVCTL enable command enables the named object so that it can run under 
  Oracle Restart for automatic startup, failover, or restart.

Usage: srvctl enable database -d db_unique_name
Usage: srvctl enable service -d db_unique_name -s "service_name_list"
Usage: srvctl enable asm
Usage: srvctl enable listener [-l lsnr_name]
Usage: srvctl enable diskgroup -g dg_name
Usage: srvctl enable ons [-v]
Usage: srvctl enable eons [-v]

Shutting down everything nicely with Oracle Restart.

[oracle@rhel6]:/oracle > srvctl stop database -d ORCLT
[oracle@rhel6]:/oracle > srvctl stop diskgroup -g DG_DB_ASM
[oracle@rhel6]:/oracle > srvctl stop asm
[oracle@rhel6]:/oracle > srvctl stop listener

[oracle@rhel6]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora...._ASM.dg ora....up.type OFFLINE   OFFLINE               
ora....ER.lsnr ora....er.type OFFLINE   OFFLINE               
ora.asm        ora.asm.type   OFFLINE   OFFLINE               
ora.ORCLT.db   ora....se.type OFFLINE   OFFLINE               
ora.cssd       ora.cssd.type  ONLINE    ONLINE    rhel6   
ora.diskmon    ora....on.type ONLINE    ONLINE    rhel6  

resources:
http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/restart001.htm
$ srvctl -h