Square DBA – Oracle DBA Talk

Remove non-ASCII characters from a column

Do you need to remove special (non-ASCII) characters from a VarChar2 column in Oracle? Create this function:

CREATE OR REPLACE FUNCTION O1DW.RECTIFY_NON_ASCII(INPUT_STR IN VARCHAR2)
RETURN VARCHAR2
IS
str VARCHAR2(2000);
act number :=0;
cnt number :=0;
askey number :=0;
OUTPUT_STR VARCHAR2(2000);
begin
str:=’^'||TO_CHAR(INPUT_STR)||’^';
cnt:=length(str);

for i in 1 .. cnt loop
askey :=0;
select ascii(substr(str,i,1)) into askey
from dual;
if askey < 32 or askey >=127 then
str :=’^'||REPLACE(str, CHR(askey),”);
end if;
end loop;
OUTPUT_STR := trim(ltrim(rtrim(trim(str),’^'),’^'));
RETURN (OUTPUT_STR);
end;
/

More »

Finding Large Directories

Recently I was looking to clear some space on one of my Oracle servers. I looked for large files with this command:

find . -type f -size +100000k -exec ls -lh {} \; | awk ‘{ print $9 “: ” $5 }’

This looks for files over 100MB, but other than datafiles there were very few files found. I knew I needed to look for large directories. Directories that had a large amount of small files. This command worked nicely:

du -h / | grep ^[1-9][0-9][0-9.]*G | sort -rn

From there I was able to find the directories that I needed to clear out.

What are cvutrace.log.* files

I was looking to clear some space recently and ran across a 14G directory (/u01/app/grid/product/11.2.0/grid/cv/log). Inside there were a lot of files like “cvutrace.log.0_20100720015347″. These files are log files related to a RAC installaation. They can safely be deleted.

To stop the files from being generated is add the following at the top of the cluvfy script in the /bin directory.

SRVM_TRACE=”false”

Backing up your CRONTAB

If you are running a lot of scripts through CRON you may want to have a current backup.  Since your CRON is simply a text schedule I prefer a daily email backup.  It’s simple and does what I need.  I add this line to my CRON:

00 0 * * * crontab -l > crontablist.txt;
mail -s "DW CRON - Quality Control" email@domain.com < crontablist.txt

Everyday at midnight I get an email with the contents of my CRON.

ORA-30926: unable to get a stable set of rows in the source tables

I received the error “ORA-30926: unable to get a stable set of rows in the source tables”. It’s the first time I have seen this error. I was doing a merge statement affecting about 1K rows.

The problem was I was not fully matching on all the of the columns that would make the update portion of the merge statement unique. Duh! Added the additional column to my “WHEN MATCHED” criteria and everything ran fine.

ORA-00845: MEMORY_TARGET not supported on this system

Today I ran into the error “ORA-00845: MEMORY_TARGET not supported on this system”. I am still not sure exactly why.

I shutdown the instance on a Oracle 11g R2 RAC node. Then a short time later I issued the startup command. The other instance on the other node was running fine and made no changes. I exited SQLPlus and launched SQLPlus again. Issued the startup command and everything started up normally.

Off to do some research . . . . . .

Strip carriage return from varchar2 column

Have you ever needed to strip a carriage return from varchar2 column? Sometime when loading data from different sources I run into these non-printable characters. SQLLDR loaded them just fine, but I try to move the data into another table with specific datatypes it fails. Why? Because there are character that I cannot see and will not allow the varchar2 data to insert into a number column.

More »

Killing your own session

Ever wanted to kill your own session? You know commit suicide; This works nicely:

alter session set events 'immediate crash';

Nothing like a ORA-03113: end-of-file on communication channel to brighten your day.

Starting the Oracle Enterprise Manager agent in 11g

Do you need to start the Oracle Enterprise Manager agent in an 11g grid control environment? It’s pretty simple, here is the command.

emctl start agent

The biggest issue I see with this is people using the wrong emctl executable. Very often you will have a multiple executables. You may possibly have an emctl in your Oracle home for your database and a separate executable in another home that was setup for grid control. Make sure you are running the correct emctl executable.

Finding currently running SQL

Have you needed to find out what SQL was running in the database? Much of my time is spent on out data warehouse where long expensive queries may be running. When someone calls to ask why things are running slow one area to look is what SQL are they running. The database may not be running slow, but their SQL is.

When Oracle executes a query, it places it in memory. This allows Oracle to reuse the same SQL if needed by the executing session at a later date or by another user that may need the same SQL statement. Oracle assigns a unique SQL_HASH_VALUE and SQL_ADDRESS to each SQL statement. By Oracle doing this, it provides us a method to determine who is executing what SQL based on the join columns from the V$SESSION of SQL_ADDRESS & SQL_HASH_VALUE to the V$SQLAREA view and columns ADDRESS and HASH_VALUE.

Here is a small script to determine what SQL is currently executing.

elect sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null 

Often you may have an active session and actually show a valid SQL statement through the V$SESSION and V$SQLAREA views that seems to be taking very long. Users may be complaining that their query is “stuck” or not responsive. You as a DBA can validate that the SQL they are executing is actually doing something in the database and not “stuck” be simply querying the V$SESS_IO view to determine if the query is in fact “stuck” or is actually doing work within the database. Granted, this does not mean there isn’t a tuning opportunity but you can at least show the SQL is working.

Here you can see the I/O being done by an active SQL statement.

select sess_io.sid,
       sess_io.block_gets,
       sess_io.consistent_gets,
       sess_io.physical_reads,
       sess_io.block_changes,
       sess_io.consistent_changes
  from v$sess_io sess_io, v$session sesion
 where sesion.sid = sess_io.sid
   and sesion.username is not null 

If by chance the query shown earlier in the V$SQLAREA view did not show your full SQL text because it was larger than 1000 characters, this V$SQLTEXT view should be queried to extract the full SQL. It is a piece by piece of 64 characters by line, that needs to be ordered by the column PIECE.

Here is the SQL to show the full SQL executing for active sessions.

select sesion.sid,
       sql_text
  from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.username is not null
 order by sqltext.piece 

If you really just want to see what sessions are active this SQL will help.

elect sid,
       to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,
       username,
       type,
       status,
       process,
       sql_address,
       sql_hash_value
  from v$session
where username is not null