课程第42/43/44/45次（2017-08-18星期五和2017-08-20星期天上下午和2017-08-25星期五）

botang

第三阶段New Feature/OCM Exam Preparation（31-45）

1. CREATE TABLE sh.func_spec(
   
2. id number, doc CLOB ENCRYPT USING 'AES128' )
   
3. LOB(doc) STORE AS SECUREFILE
   
4. (DEDUPLICATE LOB CACHE NOLOGGING);
   
5. 
   
6. select  * from dba_lobs l where l.owner='SH' and l.table_name like 'FUNC_SPEC%';
   
7. 
   
8. CREATE TABLE sh.func_spec1(
   
9. id number, doc CLOB ) ;
   
10. 
    
11. CREATE TABLE sh.test_spec (
    
12. id number, doc  CLOB)
    
13. LOB(doc) STORE AS SECUREFILE
    
14. (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING);  
    
15. 
    
16. select  * from dba_lobs l where l.owner='SH' and
    
17.   l.table_name='TEST_SPEC';
    
18.   
    
19. CREATE TABLE sh.func_spec2(
    
20. id number, pic BLOB ENCRYPT USING 'AES128' )
    
21. LOB(pic) STORE AS SECUREFILE
    
22. (DEDUPLICATE LOB CACHE NOLOGGING);
    
23. 
    
24. ---SQL> alter system set encryption wallet c
    
25. create directory dir1 as '/home/oracle/dir1';
    
26. 
    
27. grant read,write on directory dir1 to sh;
    
28. 
    
29. 
    
30. CREATE OR REPLACE PROCEDURE  sh.proc_blob (  p_1 number,
    
31.                                                         p_dir  VARCHAR2,
    
32.                                                         p_file  VARCHAR2)
    
33. IS
    
34.    v_f  BFILE;
    
35.    v_b blob;
    
36. BEGIN
    
37.     INSERT INTO sh.func_spec2 values(p_1,   EMPTY_BLOB ()) RETURN   pic    into v_b;
    
38.     v_f := BFILENAME (p_dir, p_file);
    
39.    DBMS_LOB.FILEOPEN  (v_f, DBMS_LOB.FILE_READONLY);
    
40.    DBMS_LOB.LOADFROMFILE (v_b, v_f,   DBMS_LOB.GETLENGTH (v_f));
    
41.    DBMS_LOB.FILECLOSE (v_f);
    
42.    commit;
    
43. end;
    
44.    
    
45. 
    
46.   
    
47. select  * from dba_errors e where e.name='PROC_BLOB';
    
48. 
    
49. 
    
50. begin
    
51.     sh.proc_blob (1000,'DIR1','GreenMeadow.jpg');
    
52.   end;
    
53.   
    
54.   
    
55.   select  * from sh.func_spec2;
    
56.   
    
57.   

复制代码

1. alter  TABLE sh.func_spec move tablespace users
   
2.   LOB (doc)   store as  
   
3. ( tablespace example  );
   
4. 
   
5. alter table sh.func_spec1 enable row movement ;
   
6. 
   
7. alter table  sh.func_spec1  modify   lob (doc)  ( shrink space );
   
8. 
   
9. create table sh.tab1 (id number not null, c clob)
   
10. partition by range(id)
    
11. (partition p1 values less than (100) tablespace users lob(c) store as lobp1,
    
12. partition p2 values less than (200) tablespace users lob(c) store as lobp2,
    
13. partition p3 values less than (300) tablespace users lob(c) store as lobp3);
    
14. 
    
15. 
    
16. select  * from dba_LOB_PARTITIONS  lp where  
    
17.    lp.table_owner='SH' ;
    
18. 
    
19. select  * from dba_PART_LOBS  pl
    
20. where pl.table_owner='SH';
    

复制代码