create user scott identified by tiger;
alter user scott identified by tiger account unlock;
grant dba to scott;
create tablespace rec_tbs datafile '/oracle/app/oradata/zydb/rec_tbs01.dbf' size 100m autoextend on next 100m maxsize 10G;
drop table scott.trunctab;
create table scott.trunctab as select * from dba_tables;
create table sys.TRUNCTAB tablespace rec_tbs as select * from SCOTT.TRUNCTAB where 1=2
SQL> select count(*) from sys.TRUNCTAB;
COUNT(*)
----------
0
SQL> select count(*) from scott.trunctab;
COUNT(*)
----------
2794
SQL> select sysdate from dual;
SYSDATE
-------------------
2023-06-16 22:02:56
SQL> select count(*) from scott.trunctab;
COUNT(*)
----------
2794
SQL> truncate table scott.trunctab;
Table truncated.
SQL> select count(*) from scott.trunctab;
COUNT(*)
----------
0
SQL> select sysdate from dual;
SYSDATE
-------------------
2023-06-16 22:03:21
SQL> select OWNER#,name,obj#,dataobj# from obj$ where name like 'TRUNCTAB%';
SQL> select OWNER#,name,obj#,dataobj# from obj$ where name like 'TRUNCTAB%';
OWNER# NAME OBJ# DATAOBJ#
---------- ------------------------------ ---------- ----------
0 TRUNCTAB 87361 87361
83 TRUNCTAB 87360 87362
set serveroutput on
col USR for a10
col name for a100
col SQL_UNDO for a50
select name
from v$archived_log
where first_time between to_date('2023-06-16 22:00:00', 'YYYY-MM-DD HH24:MI:SS') and
to_date('2023-06-16 22:30:00', 'YYYY-MM-DD HH24:MI:SS');
no rows selected
begin
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/app/oradata/zydb/redo01.log', OPTIONS => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/app/oradata/zydb/redo02.log', OPTIONS => DBMS_LOGMNR.addfile);
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/app/oradata/zydb/redo03.log', OPTIONS => DBMS_LOGMNR.addfile);
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
end;
/
begin
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/app/archlog/1_8_1139310824.arc', OPTIONS => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/app/archlog/1_9_1139310824.arc', OPTIONS => DBMS_LOGMNR.addfile);
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
end;
/
set linesize 200 pagesize 9999
col USR for a10
col sql_redo for a30
col SQL_UNDO for a30
col SEG_OWNER for a10
col SEG_NAME for a10
SELECT to_char(TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') t_time,
SEG_OWNER,
SEG_NAME,
USERNAME AS usr,
SQL_REDO,
--SQL_UNDO,
OPERATION
FROM V$LOGMNR_CONTENTS
WHERE 1=1
and TABLE_NAME = 'OBJ$'
and OPERATION in ('UPDATE')
and sql_redo like '%87360%';
update obj$ set dataobj#=87360 where obj#=87360;
commit;
declare
v_fno number;
v_s_bno number;
v_e_bno number;
v_rowid rowid;
nrows number;
v_owner varchar2(100):='SCOTT';
v_table varchar2(100):='TRUNCTAB';
v_o_owner varchar2(100):='SYS';
v_o_table varchar2(100):='TRUNCTAB';
v_dataobj number;
v_sql varchar2(4000);
v_tablespace varchar2(100);
begin
select data_object_id into v_dataobj from dba_objects where owner=v_owner and object_name=v_table;
select tablespace_name into v_tablespace from dba_tables where owner=v_owner and table_name=v_table;
for i in (select relative_fno,block_id,blocks
from dba_extents
where owner=v_owner and segment_name=v_table and extent_id=0
union all
select relative_fno,block_id,blocks
from dba_free_space
where tablespace_name=v_tablespace
union all
select relative_fno,block_id,blocks from (
select relative_fno,block_id,blocks,row_number()over(partition by owner,segment_name,partition_name order by extent_id desc) rn
from dba_extents
where tablespace_name=v_tablespace and extent_id>0) where rn=1) loop
v_fno:=i.relative_fno;
v_s_bno:=i.block_id;
v_e_bno:=i.block_id+i.blocks-1;
for j in v_s_bno .. v_e_bno loop
begin
for x in 0 .. 999 loop
v_rowid:=dbms_rowid.rowid_create(1,v_dataobj,v_fno,j,x);
v_sql:='insert into '||v_o_owner||'.'||v_o_table||' select * from '||v_owner||'.'||v_table||' where rowid=:1';
execute immediate v_sql using v_rowid;
end loop;
exception
when others then
null;
end;
commit;
end loop;
end loop;
end;
/
SQL> select count(*) from sys.trunctab;
COUNT(*)
----------
2794
