SAP BASIS,HANA AND ORACLE DBA BLOGS

                        when we have to do index rebuild ? Now,if u find table in which there is lot of insert and update on table which i believe you have to think to create the index and run query by login into sys schema .  query is given below COMMAND FOR CHECK INDEX REBUILD select a.*, round(index_leaf_estimate_if_rebuilt/current_leaf_blocks*100) percent, case when index_leaf_estimate_if_rebuilt/current_leaf_blocks < 0.5 then 'candidate for rebuild' end status from ( select table_name, index_name, current_leaf_blocks, round (100 / 90 * (ind_num_rows * (rowid_length + uniq_ind + 4) + sum((avg_col_len) * (tab_num_rows) ) ) / (8192 - 192) ) as index_leaf_estimate_if_rebuilt from ( select tab.table_name, tab.num_rows tab_num_rows , decode(tab.partitioned,'YES',10,6) rowid_length , ind.index_name, ind.index_type, ind.num_rows ind_num_rows, ind.leaf_blocks as current_leaf_blocks, decode(uniqueness,'UNIQU...

All about INITRANS The INITRANS setting controls Initial Transaction Slots (ITLs). A transaction slot is required for any session that needs to modify a block in an object. For tables INITRANS defaults to 1 for indexes, 2. The MAXTRANS setting controls the maximum number of ITLs that a block can allocate (usually defaults to 255). If a block is sparsely populated then Oracle will dynamically increase the number of ITLs up to MAXTRANS. ITL's, INITRANS and Block Waits However, if the block has little or no free space then transactions will serialize waiting on a free ITL. This is one cause for data base block waits. By setting INITRANS to the number of expected simultaneous DML (data manipulation language - insert, update and delete) transaction for a single block, you can avoid serialization for ITL slots. The maximum value suggested for INITRANS is 100 and settings over this size rarely improve performance. Therefore a setting of INITRANS to the average number of simul...

            SHELL SCRIPT FOR EXPDP AND IMPDP  #!/bin/bash echo " ============================================================ " echo " === Generic Export/Import of an Oracle Dump 11g      === " echo " === version 1.00                      === " echo " ============================================================ " # " DO NOT change these values below !!!!! " # " Please ask the administrator before " #imp INSTALL_DP=/u01/app/oracle/gen_ImportExport/importDumpFiles DATA_PUMP_I=DATA_PUMP_IDIR #exp EXPORT_DP=/u01/app/oracle/gen_ImportExport/exportDumpFiles DATA_PUMP_E=DATA_PUMP_EDIR #bkp DATA_PUMP_B=DATA_PUMP_BDIR # " You CAN change these values below" export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 export DATAFILE=/applications/conslife/oradata export ORACLE_SID=CONSLIFE HERE=$PWD SYS_PWD=password DFLT_ORACLE_HOME=$ORACLE_HO...

      sheel script - automatic tablespace addition in oracle 11g #!/bin/bash a=`sqlplus -s / as sysdba <<END set pagesize 0 feedback off verify off heading off echo off; select tablespace_name from dba_segments group by tablespace_name having SUM(BYTES)/1024/1024>700; END` f=${#a} c=0 for file in $a ; do eval "var$c=$file"; c=$((c+1)); done echo $var0 array1=$(df -kh | grep '/u0*'|awk '{ print $6}') array2=$(df -kh | grep '/u0*'|awk '{ print $5}') size=${#array1} size1=${#array2} if ["$size" -eq 4] && ["$size1" -eq 3] && ["$array2" -gt 85%] && ["$f" -eq 1] then d=${array1[0]:0:4} sqlplus /nolog <<END connect /as sysdba alter tablespace $a add datafile '$d/$a01.dbf' size 10m autoextend on maxsize 28G; END` fi if ["$size" -eq 9] && ["$size1" -eq 3] && ["$array2 -gt...

ORA-12505: TNS: LISTENER DOES NOT CURRENTLY KNOW OF SID GIVEN IN CONNECT DESCRIPTOR TIPS  This is new issue introduce in 12c versions  A user will often see error ORA-12505 in cases where the listener receives a request to establish a database connection or some other service. The error may be caused by one of the following reasons: The connect descriptor received by the listened specifies a SID for an instance that has not been dynamically registered with the listener. The connect descriptor received by the listened specifies a SID for an instance that has not been statically configured for the listener. Oracle states that this could possibly be a temporary condition, such as after the listener has started but before the database instance has been registered with the listener. There are several methods of correcting error ORA-12505. Your first action to resolve this issue should be to wait a few seconds, then try to re-connect to the database again. If th...

In-Memory Column Store in Oracle Database 12c The In-Memory Column Store (IM column store) was the headline feature of the 12.1.0.2 patchset. This features allows you to store columns, tables, partitions and materialized views in memory in a columnar format, rather than the typical row format. The advantage of having data in memory is obvious, but the columnar storage lends itself extremely well to analytic queries found in business intelligence products.  The IM column store is a separately licensed option of Oracle Database Enterprise Edition. Introduction Enable In-Memory Column Store Disable In-Memory Column Store Managing Tables Managing Columns Managing Materialized Views Managing Tablespaces Views Introduction The In-Memory column store is a new section of the SGA, sized using the  INMEMORY_SIZE  initialization parameter. You can choose to store specific groups of columns, whole tables, materialized views or table partitions in the store. Alte...

Q.How to Check/find if Oracle version is Standard or Enterprise edition ? Sometimes you need to find the version and Edition of Oracle database installed on your server. There are few ways to find the Oracle version. You can find Oracle version complete Information from SQLPlus, Registry,query, etc.. Recognize Oracle Stardard Editions (SE)   Login into SQL Plus via command prompt and after typing username and password there will be a banner message like below. And it not specified as any Editions ? It must me a Standard Edition. Connected to: Oracle Database 11g Release 11.1.0.6.0 - 64bit Production. Above connected to is a Standard Edition(SE) of Oracle. (Not specified any Editions particularly) Find Oracle Version details from SQLPlus SQL> select * from v$version; Recognize Oracle Enterprise Editions (EE)   Login into SQL Plus via command prompt and after typing username and password there will be a message like below. It must particularly spe...