High_Water_Mark.sql
SET SERVEROUTPUT ON
SET VERIFY OFF
undefine 1
undefine 2
DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2');
op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN
Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;
END;
/
SET VERIFY ON
undefine 1
undefine 2
SQL> /
Enter value for 1: EMP
Enter value for 2: SCOTT
TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK
------------------------------ --------------- --------------- ---------------
EMP 4 8 3
PL/SQL procedure successfully completed.
Tuesday 22 November 2011
Oracle High Water Mark for the specified table ( HWM )
Oracle User constraints info insert into a table
SQL> CREATE TABLE cons_def (ID NUMBER,table_name VARCHAR2(30),DDL VARCHAR2(4000));
Table created.
SQL> CREATE SEQUENCE seq_cons START WITH 1 INCREMENT BY 1 NOCYCLE NOCACHE NOMAXVALUE;
Sequence created.
DECLARE
my_stmt VARCHAR2 (4000);
BEGIN
FOR x IN (SELECT UPPER (table_name) NAME
FROM user_tables
ORDER BY 1)
LOOP
my_stmt := NULL;
for y in (select 'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' ||
chr(10) ||'foreign key ( ' || child_columns || ' ) ' ||
chr(10) || 'references "' || parent_tname || '" ( ' || parent_columns || ') '|| decode(DEFERRED ,'DEFERRED','DEFERRABLE INITIALLY DEFERRED',' ') fkey ,
x.name
from ( select a.table_name child_tname, a.constraint_name child_cons_name,
b.r_constraint_name parent_cons_name, b.deferred,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL)) child_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name,b.r_constraint_name,b.deferred ) child,
( select a.constraint_name parent_cons_name,a.table_name parent_tname,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
parent_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = x.name)
loop
INSERT INTO cons_def
(ID,
table_name,
DDL
)
VALUES (seq_cons.NEXTVAL,
y.NAME,
y.fkey
);
end loop;
END LOOP;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error in creating DDL: ' || SQLERRM);
RAISE;
END;
/
SQL> select * from cons_def;
ID TABLE_NAME
---------- ------------------------------
DDL
------------------------------------------------------------------------
1 ANUJ1
alter table "ANUJ2"
add constraint "ANUJ2_FK"
foreign key ( "N" )
references "ANUJ1" ( "X")
2 DAM_PARAM_TAB$
alter table "DAM_CONFIG_PARAM$"
add constraint "DAM_CONFIG_PARAM_FK1"
foreign key ( "PARAM_ID" )
references "DAM_PARAM_TAB$" ( "PARAMETER#")
Oracle 11gr2 compression
procedure example works only in 11gR2
you will get this message in 11gR2
Please use DBMS_COMPRESSION package for estimating compression ratio
go to rdbms dir
oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/admin> ls -ltr dbmscomp.sql
-rw-r--r-- 1 oracle oinstall 5180 2009-07-02 13:48 dbmscomp.sql
oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/admin> ls -ltr prvtcomp.plb
-rw-r--r-- 1 oracle oinstall 1226 2009-08-14 22:33 prvtcomp.plb
oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/admin> !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 22 14:48:28 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @dbmscomp.sql
Package created.
Synonym created.
Grant succeeded.
No errors.
SQL> @prvtcomp.plb
Library created.
Package body created.
No errors.
SQL> set serveroutput on
SQL>
DECLARE
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('USERS', 'SCOTT', 'EMP', '',
DBMS_COMPRESSION.COMP_FOR_OLTP,
blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '||blkcnt_uncmp/blkcnt_cmp||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
END;
/
Block count compressed = 1
Block count uncompressed = 1
Row count per block compressed = 14
Row count per block uncompressed = 14
Compression type = "Compress For OLTP"
Compression ratio = 1 to 1
Compression ratio org= 1
PL/SQL procedure successfully completed.
===
DECLARE
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('USERS', 'SCOTT', 'ANUJ_BIG_TABLE', '',
DBMS_COMPRESSION.COMP_FOR_OLTP,
blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '||blkcnt_uncmp/blkcnt_cmp||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
END;
/
Block count compressed = 12
Block count uncompressed = 194
Row count per block compressed = 550
Row count per block uncompressed = 34
Compression type = "Compress For OLTP"
Compression ratio = 16.16666666666666666666666666666666666667 to 1
Compression ratio org= 16
PL/SQL procedure successfully completed.
col SEGMENT_NAME format a20
select segment_name, bytes, a.blocks, compression, compress_for from dba_segments a, dba_tables b
where a.segment_name = b.table_name
and a.segment_name='ANUJ_BIG_TABLE';
SEGMENT_NAME BYTES BLOCKS COMPRESS COMPRESS_FOR
-------------------- ---------- ---------- -------- ------------
ANUJ_BIG_TABLE 26673152 3256 DISABLED
declare
lv_cmp_ratio number;
lv_comptype_str varchar2(300);
lv_BLKCNT_CMP number;
lv_BLKCNT_UNCMP number;
lv_ROW_CMP number;
lv_ROW_UNCMP number;
begin
dbms_compression.GET_COMPRESSION_RATIO(
SCRATCHTBSNAME=>'USERS', ---- TableSpace
OWNNAME=>'SCOTT', ---- User Name
TABNAME=>'ANUJ_BIG_TABLE', --- Table Name
PARTNAME =>null,
COMPTYPE =>2, ---2 means OLTP
BLKCNT_CMP=>lv_BLKCNT_CMP,
BLKCNT_UNCMP=>lv_BLKCNT_UNCMP,
ROW_CMP =>lv_ROW_CMP,
ROW_UNCMP =>lv_ROW_UNCMP,
CMP_RATIO=>lv_cmp_ratio,
COMPTYPE_STR=>lv_COMPTYPE_STR);
dbms_output.put_line('====================================================');
dbms_output.put_line('1. Compression Ratio :'||lv_cmp_ratio);
dbms_output.put_line('2. Block Count :'||lv_blkcnt_cmp);
dbms_output.put_line('3. Compression Type :'||lv_comptype_str);
dbms_output.put_line('4. Blk Count Compressed :'||lv_BLKCNT_CMP);
dbms_output.put_line('5. Blk Count Un-compressed:'||lv_BLKCNT_UNCMP);
dbms_output.put_line('6. Row Count Compressed :'||lv_row_cmp);
dbms_output.put_line('4. Row Count Un-Compressed:'||lv_row_uncmp);
dbms_output.put_line('====================================================');
end;
/
====================================================
1. Compression Ratio :17.25
2. Block Count :12
3. Compression Type :"Compress For OLTP"
4. Blk Count Compressed :12
5. Blk Count Un-compressed:207
6. Row Count Compressed :584
4. Row Count Un-Compressed:33
====================================================
PL/SQL procedure successfully completed.
Oracle Export ( Data pump ) Shell script
Oracle Export ( Data pump ) Shell script
cat oraexpdb.sh
# !/bin/bash
TODAY=`date`
ORACLE_SID=db ; export ORACLE_SID
ORACLE_BASE=/opt/oracle ; export ORACLE_BASE
ORACLE_HOME=/opt/oracle/product/10.2 ; export ORACLE_HOME
PATH=${ORACLE_HOME}/bin:$PATH ; export PATH
EXPFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.dmp; export EXPFILE
ERRFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.err; export ERRFILE
LOGFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.log ; export LOGFILE
SUCC=/opt/oracle/backup/DataPump/Backup/SUCC.txt ; export SUCC
# echo $EXPFILE
ps -ef | grep -i smon|grep -v grep|wc -l> oracle.exp
if [ `cat oracle.exp|grep -v grep|grep -i "1"|wc -l` -eq 1 ]; then
[ -f $ERRFILE ]; rm $ERRFILE
expdp "'/ as sysdba'" directory=datapump DUMPFILE=$EXPFILE SCHEMAS=PROD1,UAT2,ESEARCH logfile=$LOGFILE EXCLUDE=STATISTICS parallel=4
fi
echo "-------------- Deleting old File ----------------------"
find /opt/oracle/backup/DataPump/Backup -name "*.err" -ctime +2 -exec rm {} \;
find /opt/oracle/backup/DataPump/Backup -name "*.dmp*" -ctime +2 -exec rm {} \;
# find /opt/oracle/backup/DataPump/Backup -name "$ORACLE_SID.export.*" -mtime +2 -exec rm {} \;
echo "Done........ "
exit 0
Subscribe to:
Posts (Atom)
