Posts Tagged Oracle

Clearing the cache on Oracle and SQL Server

To clear the cache on Oracle:

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

To clear the cache on SQL Server:

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

Advertisements

,

Leave a comment

Indexing only null column values in Oracle

We have a table that can have tens or hundreds of millions of rows in it, and in this table there is a certain column. This column usually—typically—generally—has non-null hash values in it.

In unusual cases though, that column can be NULL. And the thing is, there are times when the interesting rows, the rows we want to gather together and do something with, are exactly the rows where that column is null.

Table scans to find the rows where the column is NULL are expensive, so naturally we want to index this column. But since non-null values in this column are a hash and we only ever care about the NULL values, we’d like to avoid the time and space overhead of indexing the non-null values. We’d like a partial index of only the rows where the column is NULL… Read the rest of this entry »

,

Leave a comment

How to change to a different schema in Oracle SQL Developer

ALTER SESSION SET CURRENT_SCHEMA = I3_IC;

Leave a comment

How to put a delay in an Oracle sproc, for testing

To simulate your Oracle stored procedure taking 15 minutes to execute, put this in your sproc:

dbms_lock.sleep(15 * 60);

But first you’ll need to give your user EXECUTE permissions on the dbms_lock module:

C:\>sqlplus sys@clayoracle3 as sysdba
...
SQL> GRANT EXECUTE ON dbms_lock TO my_user;

Grant succeeded.

If you’re trying this out outside a sproc, put an exec on the beginning, thus:

exec dbms_lock.sleep(15 * 60);

Note: dbms_lock.sleep() is known to be unreliable past about a 10 minute nap.

Leave a comment

How to put a delay in a SQL Server sproc, for testing

To simulate your SQL Server stored procedure taking 15 minutes to execute, put this in your sproc:

WAITFOR DELAY '00:15'

Leave a comment

How to spit out a result set returned by an Oracle sproc

When a sproc returns a result set, in Oracle it takes a little more work to view the contents of that result set. But it can be done.

Simple example

As an example, say you want to call the spir_get_fooids_by_bazidkey sproc which returns a result set of foo ids, and you want to see what foo ids it returned. Here’s how that would look:
Read the rest of this entry »

Leave a comment

Oracle stuff


--To disable ampersand-variables:
SET DEFINE OFF

--To enable debug output:
SET SERVEROUTPUT ON

--To list data tablespaces:
SELECT DISTINCT TABLESPACE_NAME FROM ALL_TABLES;

--To list index tablespaces:
SELECT DISTINCT TABLESPACE_NAME FROM ALL_INDEXES;

Leave a comment