Square DBA – Oracle DBA Talk

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

ORA-01555 Snapshot too old

Do you ever see the Oracle error “ORA-01555 Snapshot too old (Rollback has been overwritten)”? It is a pretty common error, but one I don’t see much anymore.

ORA-01555 error means the rollback segment that your long running query needed has been overwritten. Today I was helping out a friend who has been getting this error trying to execute a delete command. I directed him to a pretty good article on the subject over at orafaq.com. Take a look at the article, it has a lot of good information on properly dealing with the UNDO tablespace.

Flushing Oracle’s Cache

Have you ever wanted to test the timing on a SQL script? The first time you execute the script it executes in 5 minutes. The second time it executes it only takes 1 minute. Everything else being equal this is probably due to Oracle caching some or all of your data. This is a good thing, but not what you want when testing the speed of a script. You may want to make some changes and run the script again to test those changes. Caching the data will not give you a fair comparison. You need to flush Oracle’s cache. To flush the shared pool the command is simple.

alter system flush shared_pool;

Also as of Oracle 10G you can flush just the buffer cache with this command.

alter system flush buffer_cache;

To flush the buffer cache in 9i you can use the following (undocumented) command.

alter session set events = 'immediate trace name flush_cache';

Renaming an Oracle Table

Renaming a table in Oracle is a very simple process, but you do need to be aware of the results. When you rename a table objects like indexes do move with the rename. However be aware that references to the table from things like PL/SQL block do not. Objects that reference the table will probably be invalid after you rename the table. Here is the syntax for renaming the table:

alter table
   table_name
rename to
   new_table_name;

If you are renaming a table in another user’s schema be aware that you should not use the schema name in the new table name. If you do you will get an ORA-14047 error. This syntax is not correct.

alter table
   scott.table_name
rename to
   scott.new_table_name;

Running this will generate a “ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations” error.

However this is the correct syntax and will run just fine.

alter table
   scott.table_name
rename to
   new_table_name;

ORA-00001 Unique constraint violated

I am going to start posting information on common Oracle errors. These are error I see all the time in my daily work or errors I see from other’s working on my databases.

Th first error I see a lot is “ORA-00001 Unique constraint violated”. This error means that an attempt has been made to insert a record with a duplicate (unique) key. This error will also be generated if an existing record is updated to generate a duplicate (unique) key. Typically this is a duplicate primary key, but it need not be the primary key. On our data warehouse I see this error several times a day. Primary keys and unique constraints help keep the data clean.

You really only have a few option when dealing with this error:

1. Remove the unique restriction.
2. Change the restriction to allow duplicate keys. An index could be changed to be a non-unique index, but remember that the primary key must always be unique
3. Do not insert the duplicate key

Managing Oracle Partitioned Tables

I deal a lot with call detail records. Currently we process several million calls per day so that means I get several million rows or data daily. To deal with all that data I use Oracle’s partitioned tables. Oracle partitions addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes. Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data. Here are some common SQL statements I use, some of them I use daily.

SELECT * FROM schema.table PARTITION (part_name);

ALTER TABLE aaa MOVE PARTITION bbb
TABLESPACE rrr NOLOGGING;

ALTER TABLE edu
ADD PARTITION jan99 VALUES LESS THAN ( '990201' )
TABLESPACE tsjan99;

ALTER TABLE DROP PARTITION bbb

ALTER TABLE TRUNCATE PARTITION bbb