Square DBA – Oracle DBA Talk

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 

Killing Oracle Sessions

Do you need to kill an Oracle session? There are several way to do this. In SQLPlus simply run:

ALTER SYSTEM KILL SESSION 'sid,serial#';

You can also run:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

You can also kill sessions from the UNIX level with this command:

kill -9 spid

This is dangerous and should not normally be done. Pick the wrong OS process and you could crash your instance. There may also be time you need to bulk kill Oracle sessions. Simply selecting again v$session will get you the data needed.

select 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||'''
IMMEDIATE;' from v$session where username = 'DWUSER' and
STATUS = 'ACTIVE';

Then just run the output in SQLPlus. Again be sure you are only killing the sessions you want. Killing the wrong session could terminate your database instance.

Update data in one table with data from another table

Have you ever need to update data in one table with data from another table? I do all the time. One way I perform the update is with functions that I built. I pass a column into the function and it returns a result that use in my update statement. This works well if I am not updating a large numbers rows. If I am updating a large number of rows this can be pretty slow. So instead I run a statement like this:

UPDATE o1dw.stg_acme a
SET (switch_id) = (SELECT switch_id from o1dw.switch_info b
WHERE b.SWITCH_IP = a.client_ip and b.SWITCH_GROUP = 'SAC')
WHERE EXISTS (SELECT 1 from o1dw.switch_info b
WHERE b.SWITCH_IP = a.client_ip and b.SWITCH_GROUP = 'SAC') 

This method is much more efficient. This update takes about 12 minutes on 3.5 million rows.

ORA-12518 TNS listener could not hand off client connection

Have you ever seen the error “ORA-12518 TNS listener could not hand off client connection”? I did yesterday and it was a pretty simple fix.

It turns out the processes for a particular instance was set too low. You can see how many processes are running via this command:

select count(*) from v$process;

If you want to see what your threshold is set to run:

show parameter processes;

To change the number of processe run this command:

ALTER SYSTEM SET processes=250 SCOPE=spfile;

Unfortunately this requires you to restart the database for the new value takes affect. To see the new value in the spfile you can run this command:

SELECT value FROM v$spparameter WHERE name='processes';

ORA-06564 When Creating a Materialized View

Today I needed to create a materialized view and received an error. My syntax was correct. Here is the create statement.

CREATE MATERIALIZED VIEW SCOTT.STUPID_TABLE
BUILD IMMEDIATE
REFRESH FORCE
START WITH SYSDATE
NEXT trunc(sysdate+1) + 22/24
AS
select * from BILL.STUPID_TABLE@remote1;

and I received this error.

ERROR at line 1:
ORA-06564: object EXTDIR does not exist
ORA-02063: preceding line from remote1

This error was generated because I did not have read/write privileges on the directory EXTDIR. Yes STUPID_TABLE is an external table. To correct the problem simply grant the correct permissions.

GRANT READ ON DIRECTORY EXTDIR to MYJOBUSER;
GRANT WRITE ON DIRECTORY EXTDIR to MYJOBUSER;

ORA-29701 Unable to Connect to Cluster Manager

I received an error when I was setting up a new test database. The new database was being set up via DBCA on a RedHat server. The error occurred during the ASM creation. Because this is a test server there are multiple Oracle products installed. I changed the environment variable to new point to the proper $ORACLE_HOME. From $ORACLE_HOME/bin i have deleted and recreated cluster services.

localconfig delete

localconfig add

Issue solved