Oracle: merge tables
merge combined insert and update into one command
merge into EMP e1
using EXTERNAL_TABLE e2
on ( e2.empno = e1.empno )
when matched then
update set e1.sal = e2.sal
when not matched then
insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
values ( e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm,
e2.deptno )
Doing a direct path load would simply be:
insert /*+ append */ into emp select * from external_table;
create external table from a flat text file
create or replace directory data_dir as 'c:\temp\'
create table external_table
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by ',' )
location ('emp.dat')
)
Oracle: fetch across commit
for x in ( select rowid rid, t.* from T ) loop
update T set x = x+1 where rowid = x.rid;
commit;
end loop;
That implicit cursor is fetched from "across a commit". It is the practice of
keeping a cursor open after committing. It is a bad practice and is a common
cause of ORA-1555 (the above looping construct in particular)
Oracle: Histogram-2
Remember, analyzing for histograms is time-consuming, and histograms are used under two conditions:
*
Table join order – The CBO must know the size of the intermediate result sets (cardinality) to properly determine the correct join order the multi-table joins.
*
Table access method – The CBO needs to know about columns in SQL where clauses, where the column value is skewed such that a full-table scan might be faster than an index range scan. Oracle uses this skew information in conjunction with the clustering_factor columns of the dba_indexes view.
Hence, this is the proper order for using the dbms_stats package to locate proper columns for histograms:
1. Skewonly option - You want to use skewonly to do histograms for skewed columns, for cases where the value will make a difference between a full-table scan and an index scan.
2. Monitor - Next, turn-on monitoring. Issue an “alter table xx monitoring” and “alter index yyy monitoring” command for all segments in your schema. This will monitor workload against
3. Auto option - Once monitoring is in-place, you need to re-analyze with the "auto" option to create histograms for join columns within tables. This is critical for the CBO to determine the proper join order for finding the driving table in multi-table joins.
4. Repeat option - Finally, use the "repeat" option to re-analyze only the existing histograms.
Oracle: Histogram-1
A new feature of the dbms_stats package is the ability to look for columns that should have histograms, and then automatically create the histograms. Oracle introduced some new method_opt parameter options for the dbms_stats package. These new options are auto, repeat and skewonly and are coded as follows:
method_opt=>'for all columns size auto'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size skewonly'
The auto option is used only when monitoring has been invoked via the alter table…monitoring command. Histograms are created based upon both the data distribution (see Figure A) and the workload on the column as determined by monitoring, like this.
execute dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size auto',
degree => DBMS_STATS.DEFAULT_DEGREE);
Using the repeat option, histograms are collected only on the columns that already have histograms. Histograms are static, like any other CBO statistic, and need to be refreshed when column value distributions change. The repeat option would be used when refreshing statistics, as in this example:
execute dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.DEFAULT_DEGREE);
The skewonly option introduces a very time-consuming build process because it examines the data distribution of values for every column within every index. When the dbms_stats package finds an index whose column values are distributed unevenly, it creates histograms to help the CBO make a table access decision (i.e., index versus a full-table scan). From the earlier vehicle_type example, if an index has one column value (e.g., CAR) that exists in 65 percent of the rows, a full-table scan will be faster than an index scan to access those rows, as in this example:
execute dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size skewonly',
degree => DBMS_STATS.DEFAULT_DEGREE);
Oracle: PL/SQL interval datatype
Old fashion way:
declare
begin_2000 date := to_date('1-Jan-2000','dd-Mon-yyyy');
begin_2001 date := to_date('1-Jan-2001','dd-Mon-yyyy');
days_in_2000 number;
begin
days_in_2000 := begin_2001 - begin_2000;
dbms_output.put_line(days_in_2000);
end;
To advance a DATE to the next day, you add 1; to advance a DATE by 1 hour, you add 1/24; and so forth.
New interval datatype:
DECLARE
begin_2000 TIMESTAMP := TO_TIMESTAMP('1-Jan-2000','dd-Mon-yyyy');
begin_2001 TIMESTAMP := TO_TIMESTAMP('1-Jan-2001','dd-Mon-yyyy');
days_in_2000 INTERVAL DAY(3) TO SECOND;
BEGIN
days_in_2000 := begin_2001 Ð begin_2000;
END;
/
DECLARE
x TIMESTAMP WITH TIME ZONE;
y INTERVAL YEAR TO MONTH;
BEGIN
x := TIMESTAMP '2002-07-14 21:15:00 -07:00';
y := INTERVAL '0-1' YEAR TO MONTH;
x := x + y;
x := x + INTERVAL '1 00:00:00' DAY TO SECOND;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(
x, 'yyyy-mm-dd hh24:mi:ss tzh:tzm'));
END;
/
Oracle: Locking
---------------- showsql.sql --------------------------
column status format a10
set feedback off
set serveroutput on
select username, sid, serial#, process, status
from v$session
where username is not null
/
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped
select username||'('||sid||','||serial#||')' username,
module,
action,
client_info
from v$session
where module||action||client_info is not null;
Shell: Korn: rename file in subdirectory
# rename directory first
for f in `find . -type d`; do
g=`echo $f | tr '[A-Z]' '[a-z]'`
mv "$f" "$g"
done
# then rename all files in subdirectory
for f in `find . ! -type d`; do
g=`echo $f | tr '[A-Z]' '[a-z]'`
mv "$f" "$g"
done
Oracle: Fine-grained audit
http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/nanda_fga.html
begin
dbms_fga.add_policy (
object_schema=>'BANK',
object_name=>'ACCOUNTS',
policy_name=>'ACCOUNTS_ACCESS'
);
end;
select timestamp, db_user, os_user, object_schema, sql_text
from dba_fga_audit_trail;
begin
dbms_fga.add_policy (
object_schema=>'BANK',
object_name=>'ACCOUNTS',
policy_name=>'ACCOUNTS_ACCESS',
audit_column => 'BALANCE',
audit_condition => 'BALANCE >= 11000'
);
end;
FGA requires cost-based optimization(CBO) in order to work correctly
begin
dbms_fga.drop_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS'
);
end;
begin
dbms_fga.enable_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS',
enable => FALSE
);
end;
Oracle: Wait Events
1. DB File Scattered Read
- This generally indicates waits related to full table scans. As full table scans
are pulled into memory, they rarely fll into contiguous buffers but instead are
scattered throughout the buffer cache.
- A large number here indicates that your table may have missing or suppressed index
check to ensure that full table scan are necessary when you see these waits.
try to cache small tables to avoid reading them in over and over again, since
full table scan is put at cold end of LRU list.
2. DB File Sequential Read
- This event generally indicates a single block read(an index read, for example).
A large number of waits here could indicate poor joining orders of tables or
unselective indexing.
- You should correlate this wait statistic with other known issues within the
statspack report, such as inefficient SQL. check to ensure that index scans are
necessary and join orders for multiple table joins.
3. Free Buffer
- This indicates your system is waiting for a buffer in memory because none is
available. Waits in this category may indicate that you need to increase the
DB_BUFFER_CACHE. It could also indicate that unselective SQL is causing data
to flood the buffer cache with index blocks.
- This normally indicate that there's a substantial amount of DML being done and
that the DBWR is not writing quickly enough.
To address this, you may want to consider accelerating incremental checkpointing
using more DBWR processes or increasing the number of physical disks.
4. Buffer Busy
5. Latch Free
6. Enqueue
- An enqueue is a lock that protects a shared resource.
7. Log Buffer space
Oracle: Rman: Duplicate database on remote site
1) create a password file for duplicate instance(since you will connect as sysdba)
orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwDUP password=password entries=10
2) edit listener.ora and tnsnames.ora to make sure dup instance can be accessed from network.
3) create pfile with minimum requirement.
# Minimum Requirement.
DB_NAME=DUP
CONTROL_FILES=(/u02/oradata/DUP/control01.ctl,
/u02/oradata/DUP/control02.ctl,
/u02/oradata/DUP/control03.ctl)
# Convert file names to allow for different directory structure.
DB_FILE_NAME_CONVERT=(/u02/oradata/TSH1/,/u02/oradata/DUP/)
LOG_FILE_NAME_CONVERT=(/u01/oradata/TSH1/,/u01/oradata/DUP/)
# make sure block_size and compatible parameters
# match if you are not using the default.
DB_BLOCK_SIZE=8192
COMPATIBLE=9.2.0.0.0
4) create spfile from pfile and start instance
sqlplus /nolog
conn / as sysdba
CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/DUP/pfile/init.ora';
STARTUP FORCE NOMOUNT;
5) Backup original database
rman target / catalog rman/cat@oem
rman> backup database
rman> backup archivelog all delete input
6) duplicate database
man TARGET sys/password@tsh1 CATALOG rman/rman@tshadm AUXILIARY /
DUPLICATE TARGET DATABASE TO DUP;
or
# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO DUP UNTIL TIME 'SYSDATE-4'
Oracle: Data Guard
http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96653/standbycon.htm
Oracle: create package
CREATE or REPLACE PACKAGE test_actions AS
PROCEDURE add_entry(c1 number, c2 varchar2);
PROCEDURE del_entry(c1 number);
END test_actions;
CREATE or REPLACE PACKAGE BODY test_actions AS
PROCEDURE add_entry(c1 number, c2 varchar2);
BEGIN
INSERT INTO test2(c1,c2) VALUES(c1,c2);
END add_entry;
PROCEDURE del_entry(c1 number)
BEGIN
DELETE FROM test2 WHERE c1 = c1;
END del_entry;
END test_actions;
Oracle: bind variables and ksh
#!/bin/ksh
function f
{
sqlplus -s <
login/passwd@db
var v1 varchar2(10);
var v2 varchar2(10);
exec :v1 := '$1';
exec :v2 := '$2';
exec pkg.prc(:v1, :v2);
EOF
}
f A B
Unix: Search text in subdirectories
find . -print -name *.sh |xargs grep ECProductUpdate
Oracle: perf views
1. v$session_wait : current state view. It lists either the event currently
being waited for or the event last waited for on each session
2. v$session_event: lists the cumulative history of events waited for on each
session. After a session exits, the wait event statistics for that session are
removed from this view.
3. v$system_event: lists the events and times waited for by the whole instance
(that is, all session wait events data rolled up) since instance startup.
v$session_wait is current state view. it also contains a finer-granularity of
information than v$session_event or v$system_event.
it includes additional identifying data for the current event in three parameter
columns: P1, P2 and P3.
