DUMP_CVS: Procedure to dump table data into csv

Here is a procedure which can be used to dump table data into csv format.

CREATE OR REPLACE PROCEDURE MYSCHEMA.dump_csv( p_query in varchar2, p_separator in varchar2 default ',', p_dir in varchar2 , p_filename in varchar2 )
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );

dbms_sql.parse( l_theCursor, p_query,
dbms_sql.native );

for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;

dbms_sql.define_column( l_theCursor, 1,
l_columnValue, 2000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
utl_file.put( l_output,
l_separator || l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
dbms_output.put_line( to_char(l_cnt) ||' rows exported into '||p_filename||' file...' );
end dump_csv;
/

ORA-39126 and ORA-01017 while executing dbms_datapump import over database link

I hit this problem when executing DBMS_DATAPUMP IMPORT operation over database link. Here is the full error message:

Starting "SYS"."import_MYTASK":
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.POST_MT_INIT [SELECT SYS.KUPM$MCP.GET_ENDIANNESS@MYDBLINK FROM SYS.DUAL]
ORA-04052: error occurred when looking up remote object SYS.KUPM$MCP@MYDBLINK
ORA-00604: error occurred at recursive SQL level 3
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from MYDBLINK
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164
----- PL/SQL Call Stack -----
object line object
handle number name
70000058f38e5b8 19028 package body SYS.KUPW$WORKER
70000058f38e5b8 8191 package body SYS.KUPW$WORKER
70000058f38e5b8 2072 package body SYS.KUPW$WORKER
70000058f38e5b8 1438 package body SYS.KUPW$WORKER
70000058e31ea78 2 anonymous block
Job "SYS"."import_MYTASK" stopped due to fatal error at 09:33:35

Database link was working fine when i query manually. Restarting database solved the problem however i dont know what was the cause...

How to generate move/rebuild script to migrate partitioned tables from one tablespace to other

declare
cflag number;
oldDataTablespaceName varchar2(100);
newDataTablespaceName varchar2(100);
oldIndexTablespaceName varchar2(100);
newIndexTablespaceName varchar2(100);
parallel_level varchar2(2);
begin
DBMS_OUTPUT.ENABLE(1000000);
-------------------- SET VARIABLES ----------------------------
oldDataTablespaceName:=OLD_TABLESPACE';
newDataTablespaceName:='NEW_TABLESPACE';
newIndexTablespaceName:='NEW_INDEX_TABLESPACE';
parallel_level:='8';
---------------------------------------------------------------
dbms_output.put_line('alter session enable parallel ddl;');
for l in ( select table_owner, table_name, partition_name, partition_position from dba_tab_partitions where tablespace_name=oldDataTablespaceName order by table_owner, table_name, partition_position )
loop
cflag := 0;
dbms_output.put_line('alter table '||l.table_owner||'.'||l.table_name||' move partition "'||l.partition_name||'" tablespace '||newDataTablespaceName||' parallel '||parallel_level||';');
for k in (select dip.index_owner, dip.index_name, dip.partition_name from dba_ind_partitions dip, dba_indexes di where di.table_owner=l.table_owner and di.table_name=l.table_name and di.owner=dip.index_owner and di.index_name=dip.index_name and dip.partition_position=l.partition_position)
loop
dbms_output.put_line('alter index '||k.index_owner||'.'||k.index_name||' rebuild partition "'||k.partition_name||'" tablespace '||newIndexTablespaceName||' parallel '||parallel_level||';');
end loop;
end loop;
END;
/

Here is non-partitioned version:

declare
cflag number;
oldDataTablespaceName varchar2(100);
newDataTablespaceName varchar2(100);
oldIndexTablespaceName varchar2(100);
newIndexTablespaceName varchar2(100);
parallel_level varchar2(2);
begin
DBMS_OUTPUT.ENABLE(1000000);
-------------------- SET VARIABLES ----------------------------
oldDataTablespaceName:='OLD_TABLESPACE';
newDataTablespaceName:='NEW_TABLESPACE';
newIndexTablespaceName:='NEW_TABLESPACE';
parallel_level:='8';
---------------------------------------------------------------
dbms_output.put_line('alter session enable parallel ddl;');
for l in ( select owner, table_name from dba_tables where tablespace_name=oldDataTablespaceName order by 1,2 )
loop
cflag := 0;
dbms_output.put_line('alter table '||l.owner||'.'||l.table_name||' move tablespace '||newDataTablespaceName||' parallel '||parallel_level||';');
for k in (select di.owner, di.index_name from dba_indexes di where di.owner=l.owner and di.table_name=l.table_name)
loop
dbms_output.put_line('alter index '||k.owner||'.'||k.index_name||' rebuild tablespace '||newIndexTablespaceName||' parallel '||parallel_level||';');
end loop;
end loop;
END;
/

 

Recover Database with allow corruption

Here is an example of block corruption faced during database recovery:

recover database;

RA-00600: internal error code, arguments: [3020], [48], [41103361], [41103361], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 48, block# 41103361)
ORA-10564: tablespace MYTS
ORA-01110: data file 48: '+MYDISC/mydb/datafile/myts.14714.699899641'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 323837
Errors in file /mytracepath/mydb_pr06_4728.trc:

You can skip recovery of this corrupted block by running following command:

recover database datafile '+MYDISC/mydb/datafile/myts.14714.699899641' allow 1 corruption;

If there are more then 1 corruption, you may repeat this step or run command with allow many corruption. However, dont forget to note corrupted file and block numbers. After opening database, we will use following query to find out to which segment corrupted block belogns to:

SELECT tablespace_name, segment_type, owner, segment_name

FROM dba_extents WHERE file_id = 48 and 41103361 between block_id AND block_id + blocks - 1;