(52-24)第38/39/40/41次 2016-01-02/03 星期六/日

botang · 发表于 2016-1-3 19:52:15

本帖最后由 botang 于 2016-1-3 19:52 编辑

（52-21）上完1Z0-052第18章
（52-22）上完1Z0-052第9章
（52-23）上完1Z0-052第6章
（52-24）上完1Z0-052第7章

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
table_name => 'REPAIR_TABLE',
table_type => DBMS_REPAIR.REPAIR_TABLE,
action => DBMS_REPAIR.CREATE_ACTION,
tablespace => 'USERS');
END;

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 => ‘HR',
         object_name => 'DEPARTMENTS',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
  dbms_output.put_line( 'Find  '|| num_corrupt||' Bolcks.'  );
END;

DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
  schema_name => 'SCOTT',
  object_name => 'PK_DEPT',
  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;

--drop tablespace blocktbs including contents and datafiles cascade constraints;
--drop user hr cascade;
--drop tablespace blocktbs including contents and datafiles cascade constraints;
create tablespace blocktbs nologging;
create table tocp7_a(n1 char(1)) tablespace blocktbs;
select  count(*) from tocp7_a;

select  * from v$backup_corruption;

select  * from v$database_block_corruption;
select  * from v$copy_corruption;

select  * from dba_tables t where t.table_name='HR_TUNDO';

select  * from sys_dba_segs s where s.owner='SYS' and s.segment_name='TOCP7_A';
select  * from dba_segments s where s.owner='SYS' and s.segment_name='TOCP7_A';

create table t1 ( a number)  tablespace example;

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
table_name => 'REPAIR_TABLE',
table_type => DBMS_REPAIR.REPAIR_TABLE,
action => DBMS_REPAIR.CREATE_ACTION,
tablespace => 'USERS');
END;

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
table_name => 'ORPHAN_KEY_TABLE',
table_type => DBMS_REPAIR.ORPHAN_TABLE,
action => DBMS_REPAIR.CREATE_ACTION,
tablespace => 'USERS');
END;

select  * from REPAIR_TABLE;
select  *  from ORPHAN_KEY_TABLE;

DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
schema_name => 'SYS',
object_name => 'TOCP7_A',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
  dbms_output.put_line( 'Find  '|| num_corrupt||' Bolcks.'  );
END;

DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
         schema_name => 'SYS',
         object_name => 'TOCP7_A',
         object_type => DBMS_REPAIR.TABLE_OBJECT,
         repair_table_name => 'REPAIR_TABLE',
fix_count => num_fix);
   dbms_output.put_line( 'Fix  '|| num_fix||' Bolcks.'  );
END;

select  count(*) from tocp7_a;
begin
DBMS_REPAIR.skip_corrupt_blocks ( schema_name => 'SYS',
                  object_name =>'TOCP7_A',
                  object_type => DBMS_REPAIR.TABLE_OBJECT);
                  end;

select  * from dba_tables ;
select s.skip_corrupt ,s.table_name from dba_tables s
where s.table_name='TOCP7_A' and s.owner='SYS';

DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
  schema_name => 'SYS',
  object_name => 'IOCP7_A',
  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;

select  * from REPAIR_TABLE;
select  * from ORPHAN_KEY_TABLE;
select  dump(key) from ORPHAN_KEY_TABLE;
select chr(65) from dual;
select  dump(100) from dual;

select * from v$database_block_corruption;
select * from v$hm_run;
select * from v$hm_check;
begin
dbms_hm.run_check('CF Block Integrity Check' , Run_Name=>'CF1',
INPUT_PARAMS=> 'CF_BL_NUM=100' );
end;

复制代码

select * from dba_tablespaces;
select * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
select * from dba_segments e where e.owner='HR' and e.segment_name='T04209_UNAME';
create tablespace tbs05207_a datafile '/u01/app/oracle/oradata/orcl/tbs05207_a.dbf'
size 10M autoextend on maxsize 1G uniform size 512K;
-- dba_data_files , dba_free_space
select sum(d.bytes)/1024/1024
from dba_temp_files d where d.tablespace_name='TEMP';
select sum(ue.bytes)/1024/1024
from dba_undo_extents ue where ue.tablespace_name='UNDOTBS1'
and ue.status <> 'EXPIRED';
select ep.BYTES_USED/1024/1024 , ep.BYTES_CACHED/1024/1024
from v_$temp_extent_pool ep;
----
create bigfile tablespace tbs05207_b datafile size 5M autoextend on
maxsize 4T ;
alter tablespace tbs05207_a add datafile size 10M;
select * from dba_tablespaces;
CREATE SMALLFILE TABLESPACE "TBS05205_C"
DATAFILE '+DATA' SIZE 10M LOGGING
DEFAULT STORAGE
( INITIAL 512K NEXT 512K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 50)
MINIMUM EXTENT 512K;
select * from dba_segments s where s.owner='HR' and s.segment_name='T05205_C';
select * from dba_extents e where e.owner='HR' and e.segment_name='T05205_D2';
select * from dba_data_files ;
create tablespace tbs05205_m datafile size 10M extent management local
segment space management manual ;
select * from dbA_tablespaces ;
alter table hr.t05205_d2 move tablespace tbs05205_m;
select * from dba_tables t where t.owner='HR' and t.table_name='T05205_F';
select * from dba_data_files;
select bytes/1024/1024 from dba_segments where segment_name='TBIG';
drop tablespace tbs05205_m including contents and datafiles;
alter database datafile '+DATA/orcl/datafile/tbs05207_b.272.900172891'
resize 10M ;
drop tablespace inventory including contents and datafiles;
create tablespace tbs05207_c datafile size 10M;
alter tablespace tbs05207_c drop datafile 12 ;
alter database datafile 10 autoextend on;
alter database datafile '+DATA/orcl/datafile/tbs05207_c.274.900177135' resize 90M;
alter database datafile '+DATA/orcl/datafile/tbs05207_c.275.900177123' resize 90M;
select distinct segment_name
from dba_extents e where e.tablespace_name='TBS05207_C' and e.file_id=12;
alter table hr.tbig move tablespace users;
drop tablespace tbs05207_c including contents and datafiles;

复制代码