REPAIR DATAFILE WORKSAPCE JOBS

13) REPAIR DATAFILE WORKSAPCE JOBS

--select owner, segment_name, segment_type
--from dba_extents
--where file_id = 15 and
--66660 between block_id and block_id + blocks - 1;
-- MDSYS SYS_LOB0000046123C00006$$  LOBSEGMENT

--select *
--from dba_free_space
--where file_id = 15 and
--66660 between block_id and block_id + blocks - 1;
--select COUNT(*) from aud$
--  select uniqueness from dba_indexes where index_name = 'I_AUD1';
--select column_name from dba_indexes where index_name = 'PK_TIME_CARD'

--  select * from dba_indexes where index_name = 'I_AUD1' order by column_position;
--select * from dba_indexes where index_name = 'I_AUD1';
--DROP INDEX  I_AUD1
--alter table AUD$ drop INDEX  I_AUD1
--------------------------------------------------
--select * from v$session   where STATUS = 'ACTIVE'
--ALTER SYSTEM KILL SESSION '123,    16453'
alter system kill session '123,16453'
----------------------------------------------------------
select
b.username, a.sql_text
from
v$sqltext_with_newlines a, v$session b, v$process c
where
c.spid = to_number('300', 'xxx')
and
c.addr = b.paddr
and
b.sql_address = a.address;
--------------------------------------------------------------
SELECT table_name
FROM dba_tables d
WHERE d.owner='SYSTEM';
--------------------------------------------------------------
DECLARE num_corrupt INT;
CURSOR cur_table IS
SELECT table_name
FROM dba_tables d
WHERE d.owner='SYSTEM'
AND d.table_name NOT IN ('QUEST_TEMP_EXPLAIN')
;
BEGIN
FOR rec_table IN cur_Table
LOOP
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SYSTEM',
OBJECT_NAME => rec_Table.table_name,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
IF num_corrupt > 0 THEN
DBMS_OUTPUT.PUT_LINE('Table Name : '||rec_table.table_name||' number corrupt: ' || TO_CHAR (num_corrupt));
END IF;
END LOOP;

END;
--------------------------------------------------------------------
select owner, segment_name, segment_type from dba_extents where file_id = 15 and 66660 between block_id and block_id + blocks -1;
--------------------------------------------------------------------------
--select *
--from dba_free_space
--where file_id = 15 and
--66660 between block_id and block_id + blocks - 1;
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'AUD$'
----------------------------------------------------------
--BEGIN
--DBMS_REPAIR.ADMIN_TABLES (
--     TABLE_NAME => 'REPAIR_TABLE',
--     TABLE_TYPE => dbms_repair.repair_table,
--     ACTION     => dbms_repair.create_action,
--     TABLESPACE => 'USERS');
--END;
--/
--DESC  REPAIR_TABLE
SELECT * FROM  DBA_ORPHAN_KEY_TABLE WHERE ROWNUM < 6
----------------------------------------------------------------
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
     TABLE_NAME => 'ORPHAN_KEY_TABLE',
     TABLE_TYPE => dbms_repair.orphan_table,
     ACTION     => dbms_repair.create_action,
     TABLESPACE => 'USERS');
END;
--------------------------------------------------------------------
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
  SCHEMA_NAME => 'SYS',
     OBJECT_NAME => 'AUD$',
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     CORRUPT_COUNT =>  num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;

--------------------------------------------------------------------
--SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
--       CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
--     FROM REPAIR_TABLE;
--SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES
--    WHERE OWNER = 'SYS';
SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
       CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
     FROM REPAIR_TABLE;
------------------------------------------------------------------
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
     SCHEMA_NAME => 'SYS',
     OBJECT_NAME=> 'AUD$',
     OBJECT_TYPE => dbms_repair.table_object,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
--------------------------------------------------------------------
--SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
--     FROM REPAIR_TABLE;
--SELECT COUNT(*) FROM AUD$
--DECLARE num_orphans INT;
--BEGIN
--num_orphans := 0;
--DBMS_REPAIR.DUMP_ORPHAN_KEYS (
--     SCHEMA_NAME => 'SYS',
--     OBJECT_NAME => 'I_AUD1',
--     OBJECT_TYPE => dbms_repair.index_object,
--     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
--     ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
--     KEY_COUNT => num_orphans);
--DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
--END;

--alter table time_cards drop primary key;
--ALTER SYSTEM SET audit_trail=false SCOPE=SPFILE;
--shutdown immediate
--drop user CONTRACTREPO cascade
----------------------------------------------------------------------------------
select * from v$parameter where NAME like '%audit%'
--------------------------------------------------------------------------------
--SELECT tablespace_name, largest_free_chunk
--     , nr_free_chunks, sum_alloc_blocks, sum_free_blocks
--     , to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') || '%'
--       AS pct_free
--FROM ( SELECT tablespace_name
--            , sum(blocks) AS sum_alloc_blocks
--       FROM dba_data_files
--       GROUP BY tablespace_name
--     )
--   , ( SELECT tablespace_name AS fs_ts_name
--            , max(blocks) AS largest_free_chunk
--            , count(blocks) AS nr_free_chunks
--            , sum(blocks) AS sum_free_blocks
--               FROM dba_free_space
--               GROUP BY tablespace_name )
--WHERE tablespace_name = fs_ts_name
--SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS
--drop user GOLD_STAGING5 cascade ;

--drop user GOLD_STAGING5 cascade ;
--create user contractrepo identified by contractrepo default tablespace  cleanser32k temporary tablespace temp
--grant connect, resource to contractrepo
--grant dba to gold_staging
------------------------------------------------------------------------------------------------
--select * from v$session
--alter system kill session '126,    159'
------------------------------------------------------------------------------------------------