课程第39/40次（2017-01-15星期日上下午）

botang

上完1Z0-053第19章（传送数据库，传送表空间）
（052共19章，053共21章，063多租户共9章，49-25）
为了实验传送表空间得效果，而做得小插曲-插入图片：

1. select  * from dba_tablespaces;
   
2. 
   
3. select  * from dba_tables t where t.tablespace_name='TBSUTF8_11G';
   
4. 
   
5. select  * from database_properties;
   
6. 
   
7. 
   
8. select  * from sys.tutf8_11g_sys;
   
9. 
   
10. select   * from hr.tutf8_11g;
    
11. 
    
12. insert into sys.tutf8_11g_sys values(1);
    
13. 
    
14. insert into hr.tutf8_11g values (1) ;
    
15. 
    
16. create directory dirdata as 'c:\data';
    
17. 
    
18. grant all on directory  dirdata to hr;
    
19. 
    
20. -------------------------------
    
21. 
    
22. create table
    
23. t05319 ( pic_name   varchar(30) ,   pic  blob)   ;
    
24. 
    
25. 
    
26. select  * from t05319;
    
27. 
    
28. 
    
29. CREATE OR REPLACE PROCEDURE  proc05319(  
    
30.                      p_pic_name varchar2
    
31.                      )
    
32. IS
    
33.    v_f  BFILE;
    
34.    v_b blob;
    
35. BEGIN
    
36.     INSERT INTO t05319 values(p_pic_name, EMPTY_BLOB ()) RETURN   pic    into v_b;
    
37.     v_f := BFILENAME ('DIRDATA', p_pic_name);
    
38.     DBMS_LOB.FILEOPEN  (v_f, DBMS_LOB.FILE_READONLY);
    
39.     DBMS_LOB.LOADFROMFILE (v_b, v_f,   DBMS_LOB.GETLENGTH (v_f));
    
40.     DBMS_LOB.FILECLOSE (v_f);
    
41.     commit;
    
42. end;
    
43.    
    
44. 
    
45. 
    
46. 
    
47. 
    
48. begin
    
49.     proc05319 ('show.jpg');
    
50.   end;
    
51.   
    
52.   
    
53.   select  * from t05319;
    
54.   
    
55.   alter table t05319  move tablespace TBSUTF8_11G;
    
56. 
    
57. 
    

复制代码

control8c.sql：

1. CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
   
2.     MAXLOGFILES 16
   
3.     MAXLOGMEMBERS 3
   
4.     MAXDATAFILES 100
   
5.     MAXINSTANCES 8
   
6.     MAXLOGHISTORY 292
   
7. LOGFILE
   
8.   GROUP 1 (
   
9.     '/u01/app/oracle/oradata/orcl/redo01a.dbf',
   
10.     '/u01/app/oracle/oradata/orcl/redo01b.dbf'
    
11.   ) SIZE 100M BLOCKSIZE 512,
    
12.   GROUP 2 (
    
13.     '/u01/app/oracle/oradata/orcl/redo02a.dbf',
    
14.     '/u01/app/oracle/oradata/orcl/redo02b.dbf'
    
15.   ) SIZE 100M BLOCKSIZE 512,
    
16.   GROUP 3 (
    
17.     '/u01/app/oracle/oradata/orcl/redo03a.dbf',
    
18.     '/u01/app/oracle/oradata/orcl/redo03b.dbf'
    
19.   ) SIZE 100M BLOCKSIZE 512
    
20. DATAFILE
    
21.   '/u01/app/oracle/oradata/orcl/system01.dbf',
    
22.   '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
    
23.   '/u01/app/oracle/oradata/orcl/undotbs1.dbf',
    
24.   '/u01/app/oracle/oradata/orcl/users01.dbf',
    
25.   '/u01/app/oracle/oradata/orcl/example01.dbf',
    
26.   '/u01/app/oracle/oradata/orcl/tbsutf8_11g01_b.dbf'
    
27. CHARACTER SET AL32UTF8
    
28. ;
    
29. 
    
30. -- Configure RMAN configuration record 1
    
31. VARIABLE RECNO NUMBER;
    
32. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 1 DAYS');
    
33. -- Configure RMAN configuration record 2
    
34. VARIABLE RECNO NUMBER;
    
35. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
    
36. -- Configure RMAN configuration record 3
    
37. VARIABLE RECNO NUMBER;
    
38. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','''SBT_TAPE''');
    
39. -- Configure RMAN configuration record 4
    
40. VARIABLE RECNO NUMBER;
    
41. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');
    
42. -- Configure RMAN configuration record 5
    
43. VARIABLE RECNO NUMBER;
    
44. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET');
    
45. -- Configure RMAN configuration record 6
    
46. VARIABLE RECNO NUMBER;
    
47. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO BACKUPSET');
    
48. -- Configure RMAN configuration record 7
    
49. VARIABLE RECNO NUMBER;
    
50. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','1 DEVICE TYPE ''SBT_TAPE'' PARMS  ''ENV=(OB_MEDIA_FAMILY=station90)''');
    
51. -- Configure RMAN configuration record 8
    
52. VARIABLE RECNO NUMBER;
    
53. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','2 DEVICE TYPE ''SBT_TAPE'' PARMS  ''ENV=(OB_MEDIA_FAMILY=station90)''');

复制代码

control11c.sql：

1. CREATE CONTROLFILE REUSE SET DATABASE "utforcl" RESETLOGS  NOARCHIVELOG
   
2.     MAXLOGFILES 16
   
3.     MAXLOGMEMBERS 3
   
4.     MAXDATAFILES 100
   
5.     MAXINSTANCES 8
   
6.     MAXLOGHISTORY 292
   
7. LOGFILE
   
8.   GROUP 1 '/u01/app/oracle/oradata/winorcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
   
9.   GROUP 2 '/u01/app/oracle/oradata/winorcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
   
10.   GROUP 3 '/u01/app/oracle/oradata/winorcl/redo03.log'  SIZE 50M BLOCKSIZE 512
    
11. DATAFILE
    
12.   '/u01/app/oracle/oradata/winorcl/system01.dbf',
    
13.   '/u01/app/oracle/oradata/winorcl/sysaux01.dbf',
    
14.   '/u01/app/oracle/oradata/winorcl/undotbs1.dbf',
    
15.   '/u01/app/oracle/oradata/winorcl/users01.dbf',
    
16.   '/u01/app/oracle/oradata/winorcl/example01.dbf',
    
17.   '/u01/app/oracle/oradata/winorcl/tbsutf8_11g.dbf'
    
18. CHARACTER SET AL32UTF8
    
19. ;
    

复制代码

control8c-2.sql：

1. CREATE CONTROLFILE REUSE DATABASE "WINORCL" NORESETLOGS  NOARCHIVELOG
   
2.     MAXLOGFILES 16
   
3.     MAXLOGMEMBERS 3
   
4.     MAXDATAFILES 100
   
5.     MAXINSTANCES 8
   
6.     MAXLOGHISTORY 292
   
7. LOGFILE
   
8.   GROUP 1 (
   
9.     '+DATA/winorcl/onlinelog/group_1.260.933358859',
   
10.     '+FRA/winorcl/onlinelog/group_1.259.933358859'
    
11.   ) SIZE 100M BLOCKSIZE 512,
    
12.   GROUP 2 (
    
13.     '+DATA/winorcl/onlinelog/group_2.266.933358765',
    
14.     '+FRA/winorcl/onlinelog/group_2.260.933358767'
    
15.   ) SIZE 100M BLOCKSIZE 512,
    
16.   GROUP 3 (
    
17.     '+DATA/winorcl/onlinelog/group_3.264.933358813',
    
18.     '+FRA/winorcl/onlinelog/group_3.256.933358815'
    
19.   ) SIZE 100M BLOCKSIZE 512
    
20. DATAFILE
    
21.   '+DATA/winorcl/datafile/system.265.933359019',
    
22.   '+DATA/winorcl/datafile/sysaux.259.933359019',
    
23.   '+DATA/winorcl/datafile/undotbs1.258.933359019',
    
24.   '+DATA/winorcl/datafile/users.257.933359019',
    
25.   '+DATA/winorcl/datafile/example.256.933359019',
    
26.   '+DATA/winorcl/datafile/tbsutf8_11g.262.933359035'
    
27. CHARACTER SET AL32UTF8
    
28. ;

复制代码