| | Syntax to create materialized view log
Quick syntax reference for creating a materialized view log (snapshot log) in Oracle database:
create materialized view log on SCHEMA_NAME.TABLE_NAME
tablespace TABLESPACE_NAME
-- with rowid;
| | More...
10/24/06 | | | | |
|
| | Syntax to create an Oracle snapshot
A quick referense for creating a snapshot (materialized view) in Oracle database:
create materialized view SCHEMA_NAME.SNAPSHOT_NAME
tablespace TABLESPACE_NAME
using index tablespace TABLESPACE_NAME
refresh FAST start with sysdate next sysdate + 1/48
-- with rowid
as
select * from SCHEMA_NAME.TABLE_NAME@DATABASE_LINK;
| | More...
10/24/06 | | | | |
|
| | Generate syntax for snapshots with rtrim for char columns
Script to generate column list with rtrim function for char type columns for Oracle snapshot creation, these columns will be converted to varchar2 on the snapshot site. Run from sqlplus on the master site (where the snapshot logs are).
set head off pagesize 0
select case
when data_type = 'CHAR'
then 'rtrim (' ... | | More...
09/22/06 | | | | |
|
| | Purge snapshot log / delete rows
To delete rows from snapshotlog for at least one snapshot run this (replace 'MASTER' with the actual table name):
SQL> exec dbms_snapshot.purge_log('MASTER', 1, 'DELETE');
This will delete rows from the oldest snpashot.
| | More...
09/19/06 | | | | |
|
| | Generate drop schema indexes script
I use the below script to drop indexes before running complete refresh in Oracle database. Before dropping the indexes I run export without rows so that indexes can be re-created (get sql script from export dump) after snapshots have been refreshed.
--
-- Replace USERNAME with the actual username
--
set head off pagesize ... | | More...
05/10/06 | | | | |
|
| | List all database links
Show all db links in Oracle database.
--
-- I'm sick and tired of typing this almost every day, so here we go, from now on
-- I will just copy and paste. Hope this may be useful for others too.
--
col OWNER format a12
col DB_LINK format a12
col USERNAME format a12
col HOST format a12
select ... | | More...
05/10/06 | | | | |
|
| | Script to generate creation of snapshots
Generate Oracle snapshots using the script below. It will check tablespaces for materialized view logs and place the snapshots in the appropriate tablespaces (if you follow the Oracle's recommendation to use different tablespaces for differently sized segments). This script is to be run on spanshotlog site.
set head off verify off ... | | More...
05/04/06 | | | | |
|
| | Unregister snapshots
Script to generate sql to unregister snapshots in Oracle database
select 'exec DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT(snapowner => ' || '''' || owner ||
'''' || ', snapname => ' || '''' || name || '''' || ', snapsite => ' || '''' ||
snapshot_site || '''' || ');'
from dba_registered_snapshots
/ | | More...
02/13/06 | | | | |
|
| | Generate a script to run complete refresh for all tables in schema
-- Replace USERNAME with schema owner who's snapshots are to be refreshed
set echo off verify off feedback off head off pagesize 0
spool comp_refresh.sql
select 'set head on feedback on verify on echo on'
from dual
/
select 'spool comp_refresh.log'
from dual
/
select 'exec DBMS_SNAPSHOT.REFRESH (''' || 'USERNAME.' || name || '''' || ',' || '''' ... | | More...
10/03/05 | | | | |
|
|
