Posts Tagged Oracle
To clear the cache on Oracle:
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
To clear the cache on SQL Server:
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 »
ALTER SESSION SET CURRENT_SCHEMA = I3_IC;
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.
To simulate your SQL Server stored procedure taking 15 minutes to execute, put this in your sproc:
WAITFOR DELAY '00:15'
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.
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 »
--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;