第65和66次： 053第18章，和17章

botang

2014-11-15-COMPRESS.sql：

1. 
   
2. drop table hr.t_nocompression;
   
3. 
   
4. 
   
5. create table hr.t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
   
6. 
   
7. 
   
8. begin
   
9.    for i in 1..400
   
10.    loop
    
11.       insert into hr.t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
    
12.    end loop;
    
13.    commit;
    
14. end;
    
15. 
    
16. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );
    
17. 
    
18. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
    
19.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
    
20. 
    
21. 
    
22. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
    
23. from dba_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP',  'T_BASIC2','T_OLTP2' );
    
24. 
    
25. 
    
26. alter table hr.t_nocompression compress for oltp;
    
27. 
    
28. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
    
29. from dba_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP',  'T_BASIC2','T_OLTP2' );
    
30. 
    
31. 
    
32. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
    
33.    from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
    
34.    
    
35.    select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );
    
36.    
    
37.    
    
38.    
    
39.    --hr--
    
40. begin
    
41.    for i in 1..400
    
42.    loop
    
43.       insert into hr.t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
    
44.    end loop;
    
45.    commit;
    
46. end;
    
47. 
    
48. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
    
49.    from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
    
50.    
    
51.    select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );
    
52.    
    
53.    
    
54.    alter table hr.t_nocompression move tablespace tbs_nocompression;
    
55.    
    
56.    select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );   
    
57. --sys--
    
58. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
    
59.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
    
60.    
    
61.    begin
    
62.    for i in 1..400
    
63.    loop
    
64.       insert into hr.t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
    
65.    end loop;
    
66.    commit;
    
67. end;
    
68.    
    
69.     select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );   
    
70. --sys--
    
71. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
    
72.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
    
73.    
    
74. ----------------------------------------------------------
    
75. 
    
76. drop table hr.t_basic;
    
77. 
    
78. 
    
79. create table hr.t_basic (a varchar2(200)) tablespace tbs_basic;
    
80. 
    
81. begin
    
82.    for i in 1..400
    
83.    loop
    
84.       insert into hr.t_basic values('AAAAAAAAAAAAAAAAAAAA');
    
85.    end loop;
    
86.    commit;
    
87. end;
    
88. --hr--
    
89. 
    
90. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
    
91. from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP');
    
92. 
    
93. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );
    
94. 
    
95. 
    
96. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
    
97.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
    
98.       
    
99.       insert /*+ append */ into hr.t_basic select  * from  hr.t_basic;
    
100. 
     
101.   
     
102. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );
     
103. 
     
104. 
     
105. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
     
106.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
     
107.       
     
108.       alter table hr.t_basic move tablespace  TBS_NOCOMPRESSION;
     
109.       
     
110.        select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );
     
111. 
     
112. 
     
113. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
     
114.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
     
115.       
     
116.       
     
117.       
     
118.       create table hr.t_basic_big compress as select  * from dba_source;
     
119.       
     
120.       
     
121.   declare
     
122.   v_blkcnt_cmp number;
     
123.   v_blkcnt_uncmp  number;
     
124.   v_row_cmp number;
     
125.   v_row_uncmp number;
     
126.   v_cmp_ratio number;
     
127.   v_comptype_str  varchar2(200);
     
128. BEGIN
     
129. DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'USERS',
     
130.                                                                                              ownname =>'HR',
     
131.                                                                                              tabname =>'T_BASIC_BIG',
     
132.                                                                                              partname =>null,
     
133.                                                                                              comptype => 2,
     
134.                                                                                              blkcnt_cmp => v_blkcnt_cmp,
     
135.                                                                                              blkcnt_uncmp =>  v_blkcnt_uncmp,
     
136.                                                                                              row_cmp =>v_row_cmp,
     
137.                                                                                              row_uncmp => v_row_uncmp,
     
138.                                                                                              cmp_ratio =>  v_cmp_ratio,
     
139.                                                                                              comptype_str =>v_comptype_str);                                                  
     
140. DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
     
141. DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
     
142. DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
     
143. DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || v_row_uncmp);
     
144. DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
     
145. DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
     
146. end;
     
147. ----
     
148. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid)
     
149.       from hr.T_BASIC_BIG group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid);  
     

复制代码

2014-11-15-B.sql：

1. select   s.bytes/1024/1024
   
2. from dba_segments  s where s.owner='HR' and s.segment_name='TBIG';
   
3. 
   
4. alter table hr.tbig shrink space compact;
   
5. 
   
6. alter table  hr.tbig  enable row movement;
   
7. 
   
8.   alter table hr.tbig shrink space compact;
   
9.   
   
10.   select   s.bytes/1024/1024
    
11. from dba_segments  s where s.owner='HR' and s.segment_name='TBIG';
    
12. 
    
13.    alter table hr.tbig shrink space;
    
14.    
    
15.      select   s.bytes/1024/1024
    
16. from dba_segments  s where s.owner='HR' and s.segment_name='TBIG';
    
17. 
    
18. ----
    
19. 
    
20. select  * from dba_tables t where t.owner='HR' and t.table_name='IOT';
    
21. 
    
22. select  * from dba_segments s where s.owner='HR' and s.segment_name='MYIOT';
    
23. 
    
24. select  * from dba_objects o where o.object_name='IOT';
    
25. 
    
26. select  * from dba_tables t where t.table_name like '%80736%';
    
27. 
    
28. select  * from hr.SYS_IOT_MAP_80736;
    
29. 
    
30. insert into hr.iot values (1,'A');
    
31. 
    
32. select  * from dba_indexes i where i.index_name='MYIOT';
    
33. 
    
34. ----
    
35. 
    
36. alter table hr.iot shrink space ;
    
37. 
    
38. alter  table hr.iot  move nomapping;
    
39. 
    
40.   select  * from hr.SYS_IOT_MAP_80736;
    
41.   
    
42.    alter  table hr.iot  move mapping table;
    
43.    
    
44.    -----
    
45.    
    
46.    
    
47.    
    
48.      select  * from hr.SYS_IOT_MAP_80736;
    
49.      
    
50.      ----
    
51.      select   s.bytes/1024/1024
    
52. from dba_segments  s where s.owner='HR' and s.segment_name='TBIG';
    
53.      
    
54. alter table "HR"."TBIG" shrink space;
    
55. 
    
56. ----
    
57.       select   s.bytes/1024/1024, s.segment_name
    
58. from dba_segments  s where s.owner='HR' and s.segment_name  in ('TBIG','IBIG') ;
    
59. alter table hr.tbig enable row movement;
    
60.   alter table "HR"."TBIG" shrink space;
    
61. 
    
62. 
    
63.        select   s.bytes/1024/1024, s.segment_name
    
64. from dba_segments  s where s.owner='HR' and s.segment_name  in ('TBIG','IBIG') ;
    
65. 
    
66.    alter table "HR"."TBIG" shrink space  cascade;
    
67.    
    
68.           select   s.bytes/1024/1024, s.segment_name
    
69. from dba_segments  s where s.owner='HR' and s.segment_name  in ('TBIG','IBIG') ;
    
70. ---
    
71. 
    
72. select  * from dba_objects o where o.object_name='IOTOVER';
    
73. 
    
74. select  * from dba_tables t where t.table_name like '%80768%';
    
75. 
    
76. select  * from hr.SYS_IOT_OVER_80768;
    
77. 
    
78. select  * from dba_segments s where s.segment_name='SYS_IOT_OVER_80768';
    
79. ---
    
80. create  tablespace tbs05318 datafile size 5M autoextend off;
    
81. 
    
82. grant resumable to hr;

复制代码

2014-11-15-C.sql：

1. select   s.bytes/1024/1024
   
2. from dba_segments  s where s.owner='HR' and s.segment_name='TBIG';
   
3. 
   
4. alter table hr.tbig shrink space compact;
   
5. 
   
6. alter table  hr.tbig  enable row movement;
   
7. 
   
8.   alter table hr.tbig shrink space compact;
   
9.   
   
10.   select   s.bytes/1024/1024
    
11. from dba_segments  s where s.owner='HR' and s.segment_name='TBIG';
    
12. 
    
13.    alter table hr.tbig shrink space;
    
14.    
    
15.      select   s.bytes/1024/1024
    
16. from dba_segments  s where s.owner='HR' and s.segment_name='TBIG';
    
17. 
    
18. ----
    
19. 
    
20. select  * from dba_tables t where t.owner='HR' and t.table_name='IOT';
    
21. 
    
22. select  * from dba_segments s where s.owner='HR' and s.segment_name='MYIOT';
    
23. 
    
24. select  * from dba_objects o where o.object_name='IOT';
    
25. 
    
26. select  * from dba_tables t where t.table_name like '%80736%';
    
27. 
    
28. select  * from hr.SYS_IOT_MAP_80736;
    
29. 
    
30. insert into hr.iot values (1,'A');
    
31. 
    
32. select  * from dba_indexes i where i.index_name='MYIOT';
    
33. 
    
34. ----
    
35. 
    
36. alter table hr.iot shrink space ;
    
37. 
    
38. alter  table hr.iot  move nomapping;
    
39. 
    
40.   select  * from hr.SYS_IOT_MAP_80736;
    
41.   
    
42.    alter  table hr.iot  move mapping table;
    
43.    
    
44.    -----
    
45.    
    
46.    
    
47.    
    
48.      select  * from hr.SYS_IOT_MAP_80736;
    
49.      
    
50.      ----
    
51.      select   s.bytes/1024/1024
    
52. from dba_segments  s where s.owner='HR' and s.segment_name='TBIG';
    
53.      
    
54. alter table "HR"."TBIG" shrink space;
    
55. 
    
56. ----
    
57.       select   s.bytes/1024/1024, s.segment_name
    
58. from dba_segments  s where s.owner='HR' and s.segment_name  in ('TBIG','IBIG') ;
    
59. alter table hr.tbig enable row movement;
    
60.   alter table "HR"."TBIG" shrink space;
    
61. 
    
62. 
    
63.        select   s.bytes/1024/1024, s.segment_name
    
64. from dba_segments  s where s.owner='HR' and s.segment_name  in ('TBIG','IBIG') ;
    
65. 
    
66.    alter table "HR"."TBIG" shrink space  cascade;
    
67.    
    
68.           select   s.bytes/1024/1024, s.segment_name
    
69. from dba_segments  s where s.owner='HR' and s.segment_name  in ('TBIG','IBIG') ;
    
70. ---
    
71. 
    
72. select  * from dba_objects o where o.object_name='IOTOVER';
    
73. 
    
74. select  * from dba_tables t where t.table_name like '%80768%';
    
75. 
    
76. select  * from hr.SYS_IOT_OVER_80768;
    
77. 
    
78. select  * from dba_segments s where s.segment_name='SYS_IOT_OVER_80768';
    
79. ---
    
80. create  tablespace tbs05318 datafile size 5M autoextend off;
    
81. 
    
82. grant resumable to hr;
    
83. 
    
84. select   * from dba_data_files ;
    
85. 
    
86. alter database datafile '+DATA/orcl/datafile/tbs05318.271.863699365' resize 10M;
    
87. 
    
88. select   bytes/1024/1024  ,df.autoextensible  from dba_data_files   df  where file_name='+DATA/orcl/datafile/tbs05318.271.863699365' ;
    
89. 
    
90. CREATE OR REPLACE TRIGGER SYS.TRG_SUSPEND
    
91. AFTER SUSPEND
    
92. ON DATABASE
    
93. declare
    
94. v_size number;
    
95.   pragma AUTONOMOUS_TRANSACTION;
    
96. begin
    
97.   select  bytes into v_size from dba_data_files where  file_name='+DATA/orcl/datafile/tbs05318.271.863699365';
    
98.   v_size := v_size +5242880 ;
    
99.   execute immediate 'alter database datafile  ''+DATA/orcl/datafile/tbs05318.271.863699365''  resize '||v_size;
    
100. commit;
     
101. end;
     
102. 
     
103. select  * from dba_objects o where o.object_name='TRG_SUSPEND' ;
     
104. 
     
105. select  * from dba_errors e where e.name='TRG_SUSPEND' ;
     
106. 
     
107. 
     
108. select   bytes/1024/1024  ,df.autoextensible  from dba_data_files   df  where file_name='+DATA/orcl/datafile/tbs05318.271.863699365' ;

复制代码

2014-11-15-time-based.sql：

1. select  * from dba_scheduler_programs;
   
2. 
   
3. select  * from dba_scheduler_schedules;
   
4. 
   
5. select  * from dba_scheduler_credentials;
   
6. 
   
7. begin
   
8.    dbms_scheduler.create_credential(credential_name => 'credential1',
   
9.    username => 'oracle',password => 'oracle');
   
10.   end;
    
11.   
    
12.   select  * from dba_scheduler_credentials;
    
13.   
    
14.   select  * from dba_tab_privs tp where tp.grantee='HR';
    
15.   
    
16.   grant execute on credential1 to hr;
    
17.   
    
18.     select  * from dba_tab_privs tp where tp.grantee='HR';
    
19.    
    
20.     grant create external job to hr;
    
21.    
    
22.     select * from dba_sys_privs sp where sp.grantee='HR';
    
23.    
    
24.     revoke execute on credential1  from hr;
    
25.    
    
26.     BEGIN
    
27. sys.dbms_scheduler.create_job(
    
28. job_name => '"HR"."JOB3"',
    
29. job_type => 'EXECUTABLE',
    
30. job_action => '/home/oracle/date.sh',
    
31. schedule_name => '"HR"."SCHEDULE1"',
    
32. job_class => '"DEFAULT_JOB_CLASS"',
    
33. comments => 'job3',
    
34. auto_drop => FALSE,
    
35. enabled => TRUE);
    
36. END;
    
37.   
    
38.     select  * from dba_tab_privs tp where tp.grantee='HR';
    
39. 
    
40.   grant execute on credential1 to hr;

复制代码

2014-11-15-event.sql：

1. select  * from dba_queues  where name like 'EVENT_QUEUE%';
   
2. 
   
3. select  * from dba_queue_tables;
   
4. 
   
5. 
   
6. CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
   
7. 
   
8.   event_name  VARCHAR2(30)
   
9.   
   
10. 
    
11. );
    
12. 
    
13. 
    
14. select * from dba_types t where t.type_name=upper('t_event_queue_payload');
    
15. 
    
16. 
    
17. 
    
18. BEGIN
    
19. 
    
20.   -- Create a queue table to hold the event queue.
    
21. 
    
22.   DBMS_AQADM.create_queue_table(
    
23. 
    
24.     queue_table        => 'event_queue_tab',
    
25. 
    
26.     queue_payload_type => 't_event_queue_payload',
    
27. 
    
28.     multiple_consumers => TRUE,
    
29. 
    
30.     comment            => 'Queue Table For Event Messages');
    
31. 
    
32. 
    
33. 
    
34.   -- Create the event queue.
    
35. 
    
36.   DBMS_AQADM.create_queue (
    
37. 
    
38.     queue_name  => 'event_queue',
    
39. 
    
40.     queue_table => 'event_queue_tab');
    
41. 
    
42. 
    
43. 
    
44.   -- Start the event queue.
    
45. 
    
46.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
    
47. 
    
48. END;
    
49. 
    
50. 
    
51. select  * from dba_queues  ;
    
52. 
    
53. select  * from dba_queue_tables  ;
    
54. 
    
55. --hr
    
56. CREATE TABLE scheduler_test (
    
57. 
    
58.   id            NUMBER(10)    NOT NULL,
    
59. 
    
60.   created_date  DATE          NOT NULL,
    
61. 
    
62.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
    
63. 
    
64. );
    
65. 
    
66. CREATE SEQUENCE scheduler_test_seq;
    
67. 
    
68. select * from dba_sequences s where s.sequence_owner='HR';
    
69. 
    
70. 
    
71. BEGIN
    
72. 
    
73.    DBMS_SCHEDULER.create_job (
    
74. 
    
75.       job_name        => 'event_based_job',
    
76. 
    
77.       job_type        => 'PLSQL_BLOCK',
    
78. 
    
79.       job_action      => 'BEGIN
    
80. 
    
81.                             INSERT INTO hr.scheduler_test (id, created_date)
    
82. 
    
83.                             VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);
    
84. 
    
85.                             COMMIT;
    
86. 
    
87.                           END;',
    
88. 
    
89.       start_date      => SYSTIMESTAMP,
    
90. 
    
91.       event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
    
92. 
    
93.       queue_spec      => 'event_queue',
    
94. 
    
95.       enabled         => TRUE);
    
96. 
    
97. END;
    
98. 
    
99. /
    
100. 
     
101. 
     
102. BEGIN
     
103. sys.dbms_scheduler.create_event_schedule(
     
104. event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
     
105. queue_spec => '"SYS"."EVENT_QUEUE"',
     
106. start_date => systimestamp at time zone 'Asia/Shanghai',
     
107. comments => 'schedule2',
     
108. schedule_name => '"HR"."SCHEDULE2"');
     
109. END;
     
110. 
     
111. select  * from scheduler_test;
     
112. select  * from dba_tab_privs tp where tp.grantee='HR';
     
113. 
     
114. begin
     
115.       DBMS_AQADM.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'EVENT_QUEUE',grantee => 'HR');
     
116.      end;
     
117. 
     
118. begin
     
119.       DBMS_AQADM.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'EVENT_QUEUE',grantee => 'HR');
     
120.      end;
     
121. 
     
122. 
     
123. 
     
124. 
     
125. ---application
     
126. 
     
127. 
     
128. 
     
129. --SYS
     
130. DECLARE
     
131. 
     
132.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
     
133. 
     
134.   l_message_properties  DBMS_AQ.message_properties_t;
     
135. 
     
136.   l_message_handle      RAW(16);
     
137. 
     
138.   l_queue_msg           t_event_queue_payload;
     
139. 
     
140. BEGIN
     
141. 
     
142.   l_queue_msg := t_event_queue_payload('give_me_a_prod');
     
143. 
     
144. 
     
145. 
     
146.   DBMS_AQ.enqueue(queue_name          => 'event_queue',
     
147. 
     
148.                   enqueue_options     => l_enqueue_options,
     
149. 
     
150.                   message_properties  => l_message_properties,
     
151. 
     
152.                   payload             => l_queue_msg,
     
153. 
     
154.                   msgid               => l_message_handle);
     
155. 
     
156.   COMMIT;
     
157. 
     
158. END;
     
159. 
     
160. 
     
161. select  * from hr.scheduler_test;
     
162. 
     
163. begin
     
164.    dbms_scheduler.drop_job(job_name => 'HR.JOB2',force => true);
     
165.    end;
     

复制代码

date.sh：

1. #!/bin/sh
   
2. 
   
3. echo $(date)  >> /home/oracle/date.txt

复制代码