Andrey U. Smirnov, Oracle blog

четверг, 9 июля 2009 г.

Win registry tips

How to get list of saved Putty sessions:
reg query HKEY_USERS\S-1-5-21-4126248137-1196961995-3114428227-500\Software\SimonTatham\PuTTY\Sessions

среда, 8 июля 2009 г.

Creating SR tips

Show type of file system (Solaris)
df -n

вторник, 7 июля 2009 г.

How to simulate block corruption

REM simulate block corruption for test 12.1
RMAN> alter database datafile 6 offline;

Statement processed

dd if=zero.txt of=C:\PROJECT\DB12CDB\DB12CDB\USERS01.DBF seek=30 bs=8192

RMAN> select * from v$database_block_corruption;

no rows selected

RMAN> validate datafile 6;

Starting validate at 10-NOV-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=C:\PROJECT\DB12CDB\DB12CDB\USERS01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    FAILED 0              29           641             1003639
File Name: C:\PROJECT\DB12CDB\DB12CDB\USERS01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              15
Index      0              3
Other      1              593

validate found one or more corrupt blocks
See trace file C:\APP\ORACLE\diag\rdbms\db12cdb\db12cdb\trace\db12cdb_ora_10992.trc for details
Finished validate at 10-NOV-15

RMAN> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
         6         30          1                  0 CORRUPT            0

RMAN> alter database datafile 6 online;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 11/10/2015 11:14:15
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: 'C:\PROJECT\DB12CDB\DB12CDB\USERS01.DBF'

RMAN> recover corruption list;

Starting recover at 10-NOV-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\FAST_RECOVERY_AREA\DB12CDB\BACKUPSET\2015_11_10\O1_MF_NNNDF_TAG20151110T101009_C43JQCC6_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\DB12CDB\BACKUPSET\2015_11_10\O1_MF_NNNDF_TAG20151110T101009_C43JQCC6_.BKP tag=TAG20151110T101009
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 10-NOV-15

RMAN> alter database datafile 6 online;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 11/10/2015 11:14:32
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: 'C:\PROJECT\DB12CDB\DB12CDB\USERS01.DBF'

RMAN> recover datafile 6;

Starting recover at 10-NOV-15
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 10-NOV-15

RMAN> alter database datafile 6 online;

Statement processed

четверг, 21 мая 2009 г.

SQL: How to substr by delimiter

SELECT REGEXP_SUBSTR('123-456-7890', '[^-]+',1, 3) "SUBSTR" FROM dual;

четверг, 2 апреля 2009 г.

How to compare 2 tables (data)

--create table tmp (sql_text long);
CREATE OR REPLACE PROCEDURE compareTabs
(
p_c OUT SYS_REFCURSOR --output
, p_tab1 VARCHAR2     --table name 1
, p_tab2 VARCHAR2     -- table name 2
, p_count NUMBER DEFAULT 300 --count of output rows
, p_round NUMBER DEFAULT 9 -- how to round numbers
, p_cols VARCHAR2 DEFAULT '#' --columns to NOT show separated by #, all by default #
, p_owner1 VARCHAR2 DEFAULT USER --owner of table 1
, p_owner2 VARCHAR2 DEFAULT USER --owner of table 2
, p_where1 VARCHAR2 DEFAULT NULL -- where clause for table 1
, p_where2 VARCHAR2 DEFAULT NULL -- where clause for table 2
, p_list1 VARCHAR2 DEFAULT NULL -- list of columns from table 1
, p_list2 VARCHAR2 DEFAULT NULL -- list of columns from table 2
, p_column VARCHAR2 DEFAULT NULL --first "key" column to show and sort by
)
IS
-- Original idea of Marco Stefanetti with Tom Kyte's minor addition
-- http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html
-- http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2151582681236
-- Created: 26.12.2006
-- Update: 17.01.2013
-- Autor: Andrey Smirnov
-- Using in sqlplus:
-- variable vc refcursor
-- set autoprint on
-- set linesize 32767
-- exec compareTabs(:vc, 'ts', 'td', p_owner1 => 'test', p_owner2 => 'testdst');

-- exclued several columns
-- exec compareTabs(:vc, 'ts', 'td', p_owner1 => 'test', p_owner2 => 'testdst', p_cols => 'created#data_object_id#edition_name);
-- compare only required columns
-- exec compareTabs(:vc, 'ts', 'td', p_owner1 => 'test', p_owner2 => 'testdst', p_list1 => 'object_id, object_name');
-- compare different set of columns with expressions
-- exec compareTabs(:vc, 'ts', 'td', p_owner1 => 'test', p_owner2 => 'testdst', p_list1 => 'owner, object_name, object_type, object_id', p_list2 => 'owner, object_name, object_type, NVL(data_object_id, object_id)');
-- compare starting and sorting by special columns
-- exec compareTabs(:vc, 'ts', 'td', p_owner1 => 'test', p_owner2 => 'testdst', p_column => 'object_id, data_object_id');  v_select_list1 VARCHAR2(32767);
v_select_list2 VARCHAR2(32767);
v_group_list1 VARCHAR2(32767);
v_where_clause1 VARCHAR2(32767);
v_where_clause2 VARCHAR2(32767);
v_table1 VARCHAR2(32767);
v_table2 VARCHAR2(32767);
v_sql VARCHAR2(32767);
FUNCTION get_cols
(
a_table VARCHAR2
, a_table_other VARCHAR2
, a_owner VARCHAR2
, a_owner_other VARCHAR2
, a_round NUMBER
, a_cols VARCHAR2
, a_group NUMBER DEFAULT 0
)
RETURN VARCHAR2
IS
v_select_list VARCHAR2(32767);
vComma VARCHAR2(32767);
BEGIN
FOR r IN
(
SELECT
cols
FROM
(
SELECT
(DECODE(a_group, 0, DECODE(NVL2(a_round, data_type, NULL), 'NUMBER', 'ROUND('||column_name||', '||a_round||')')||' '||column_name, column_name)) cols
FROM
all_tab_columns
WHERE
owner = UPPER(TRIM(a_owner))
AND table_name = UPPER(TRIM(a_table))
AND UPPER(TRIM(NVL(a_cols, '#')))
NOT LIKE '%#'||column_name||'#%'
AND (UPPER(TRIM(p_column)) NOT LIKE '%'||column_name||'%' OR p_column IS NULL)
AND column_name IN
(
SELECT t.column_name FROM all_tab_columns t WHERE t.owner = UPPER(TRIM(a_owner_other)) and t.table_name = UPPER(TRIM(a_table_other))
)
ORDER BY column_name
)
) LOOP
v_select_list := v_select_list || vComma || r.cols;
vComma := ',';
END LOOP;
IF p_column IS NOT NULL THEN
v_select_list := p_column||','||v_select_list;
END IF;
DBMS_OUTPUT.PUT_LINE(a_table||': '||v_select_list);
RETURN v_select_list;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, 'No table: '||a_table);
END get_cols;
BEGIN
--DBMS_OUTPUT.PUT_LINE('Start');
v_table1 := p_owner1||'.'||p_tab1;
v_table2 := p_owner2||'.'||p_tab2;
IF p_list1 IS NULL THEN
v_select_list1 := get_cols(p_tab1, p_tab2, p_owner1, p_owner2, p_round, p_cols);
v_group_list1 := get_cols(p_tab1, p_tab2, p_owner1, p_owner2, p_round, p_cols, 1);
ELSE
v_select_list1 := p_list1;
v_group_list1 := p_list1;
END IF;
IF p_list2 IS NULL THEN
v_select_list2 := /*get_cols(p_tab2, p_tab1, p_owner2, p_round, p_cols)*/v_select_list1;
ELSE
v_select_list2 := p_list2;
END IF;
IF p_where1 IS NULL THEN
v_where_clause1 := NULL;
ELSE
v_where_clause1 := ' AND ('||p_where1||')';
END IF;
IF p_where2 IS NULL THEN
v_where_clause2 := NULL;
ELSE
v_where_clause2 := ' AND ('||p_where2||')';
END IF;
BEGIN
v_sql :=
'
select *
from
(
select '||v_select_list1||',
count(src1) in_tab1,
count(src2) in_tab2
from
( select '||v_select_list1||',
1 src1,
to_number(null) src2
from '||v_table1||'
WHERE 1 = 1 '||v_where_clause1||'
union all
select '||v_select_list2||',
to_number(null) src1,
2 src2
from '||v_table2||'
WHERE 1 = 1 '||v_where_clause2||'
)
group by '||v_group_list1||'
having count(src1) <> count(src2)
order by '||v_group_list1||'
)
WHERE rownum <= '||p_count||'
'
;
--EXECUTE IMMEDIATE 'TRUNCATE TABLE tmp';
$IF $$SHOWOUTPUT $THEN
--DBMS_OUTPUT.PUT_LINE('v_sql = '||v_sql);
$END
-- INSERT INTO tmp VALUES (v_sql);
-- COMMIT;
OPEN p_c FOR v_sql;
/*EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, length(v_sql)||' test'||SQLERRM);*/
END;
END compareTabs;
/
show err

sample using in sqlplus here

Andrey U. Smirnov, Oracle blog

Страницы

четверг, 9 июля 2009 г.

Win registry tips

среда, 8 июля 2009 г.

Creating SR tips

вторник, 7 июля 2009 г.

How to simulate block corruption

четверг, 21 мая 2009 г.

SQL: How to substr by delimiter

четверг, 2 апреля 2009 г.

How to compare 2 tables (data)

пятница, 30 января 2009 г.

Oracle links

Update BLOB

Поиск по этому блогу

Страницы

четверг, 9 июля 2009 г.

среда, 8 июля 2009 г.

вторник, 7 июля 2009 г.

четверг, 21 мая 2009 г.

четверг, 2 апреля 2009 г.

пятница, 30 января 2009 г.

четверг, 9 июля 2009 г.

среда, 8 июля 2009 г.

вторник, 7 июля 2009 г.

четверг, 21 мая 2009 г.

четверг, 2 апреля 2009 г.

пятница, 30 января 2009 г.