Bo's Oracle Station

查看: 2836|回复: 0

第75/76/77/78次:2016-01-30双休日

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2016-1-31 21:39:00 | 显示全部楼层 |阅读模式
2016-01-30.sql :
  1. select * from dba_advisor_templates;

  2. select  * from dba_views v where v.view_name='DBA_ADVISOR_TEMPLATES';

  3. select  * from wri$_adv_tasks  where owner_name='SH';

  4. select  * from v$temp_extent_pool;

  5. select  * from v$temp_extent_map;

  6. select  tf.file_name  , bytes/1024/1024,  tf.autoextensible    from dba_temp_files  tf ;

  7. alter database  tempfile '+NEWDATA/orcl/tempfile/temp2.285.902486201' autoextend on;

  8. select  * from v$sql where sql_text like '%sales%';

  9. select  * from v_$session  s where s.SQL_ID='6ut95d6thg2nu';

  10. alter tablespace temp shrink  space;


  11. alter system kill session '23,292' immediate ;
  12. ---

  13. select  * from dba_temp_files;

  14. create temporary  tablespace temp1  tempfile size 10M tablespace group tempgroup;

  15. create temporary  tablespace temp2 tempfile size 10M  tablespace  group tempgroup;

  16. select  * from database_properties;

  17. alter database  default temporary  tablespace tempgroup;

  18. select  *  from tab$  where obj# in (

  19. select  object_id from dba_objects t where t.object_name like 'GTEMP%');

  20. select  * from v$tablespace;




复制代码

2016-01-30b.sql:

  1. select * from dba_advisor_templates;

  2. select  * from dba_views v where v.view_name='DBA_ADVISOR_TEMPLATES';

  3. select  * from wri$_adv_tasks  where owner_name='SH';

  4. select  * from v$temp_extent_pool;

  5. select  * from v$temp_extent_map;

  6. select  tf.file_name  , bytes/1024/1024,  tf.autoextensible    from dba_temp_files  tf ;

  7. alter database  tempfile '+NEWDATA/orcl/tempfile/temp2.285.902486201' autoextend on;

  8. select  * from v$sql where sql_text like '%sales%';

  9. select  * from v_$session  s where s.SQL_ID='6ut95d6thg2nu';

  10. alter tablespace temp shrink  space;


  11. alter system kill session '23,292' immediate ;
  12. ---

  13. select  * from dba_temp_files;

  14. create temporary  tablespace temp1  tempfile size 10M tablespace group tempgroup;

  15. create temporary  tablespace temp2 tempfile size 10M  tablespace  group tempgroup;

  16. select  * from database_properties;

  17. alter database  default temporary  tablespace tempgroup;

  18. select  *  from tab$  where obj# in (

  19. select  object_id from dba_objects t where t.object_name like 'GTEMP%');

  20. select  * from v$tablespace;

  21. ------

  22. select  * from dba_indexes i where i.blevel = 3;


  23. ---
  24. CREATE TABLE "HR"."IOT1" ( "A" NUMBER, "B" VARCHAR2(20), "C" VARCHAR2(20),
  25.               CONSTRAINT "IOT1_PK" PRIMARY KEY ("A") VALIDATE )
  26.                  ORGANIZATION INDEX
  27.                  MAPPING TABLE
  28.                  PCTTHRESHOLD 10
  29.                  INCLUDING "B" OVERFLOW TABLESPACE "TBS_BASIC";
  30.                  
  31. select  * from dba_tables t where t.table_name='IOT1';   

  32. select  * from dba_segments s where s.segment_name='IOT1_PK';      

  33. select  * from dba_indexes i where i.owner='HR' and i.index_name='ICLUSTER1';      

  34. select * from dba_objects  o where o.object_name='IOT1';

  35. select  * from dba_tables t where t.table_name  like '%75157%';


  36. select  * from hr.SYS_IOT_MAP_75157;

  37. select * from dba_clusters c where c.owner <> 'SYS';

  38. select  * from dba_tables t where t.owner='HR' and t.table_name   like 'ORDER%';

  39. select  * from dba_segments s where s.owner='HR' and s.segment_name   like 'ORDER%';

  40. select  * from dba_segments s where s.owner='HR' and s.segment_name='CLUSTER1';
  41. ----
  42. select  * from  dba_indexes i where i.table_name='TBIG';

  43. ---
  44. select  * from dba_data_files where tablespace_name='USERS';
  45. ---

  46. select  * from dba_Segments s where s.segment_name='IPART1';

  47. -----
  48. create table  sh.PROD_MASTER
  49. (
  50.   PROD_ID number,
  51.   CUST_ID  number,
  52.   TIME_ID  date ,
  53.   CHANNEL_ID  char(1) ,
  54.   PROMO_ID  number ,
  55.   QUANTITY_SOLD   number,
  56.   AMOUNT_SOLD  number(8,2)
  57. );

  58. drop table sh.PROD_MASTER;

  59. create directory dir1 as '/home/oracle/dir1';

  60. create directory dir2 as '/home/oracle/dir2';

  61. grant read,write on directory dir1 to sh;

  62. grant all on directory dir2 to sh;

  63. select  * from dba_external_locations l where l.table_name='PROD_MASTER';

  64. select  t.table_name , t.degree
  65.   from dba_tables t where t.owner='SH' and t.table_name  in ('SALES','CUSTOMERS');
  66. SQL> select CUST_LAST_NAME, sum(AMOUNT_SOLD)
  67.   2  from sales s, customers c
  68.   3  where s.CUST_ID=c.CUST_ID
  69.   4  group by CUST_LAST_NAME ;


  70. select  * from v$io_calibration_status;









复制代码

2016-01-31.sql:

  1. CREATE TABLE sales3 ( prod_id NUMBER(6) NOT NULL,
  2.                                  cust_id NUMBER NOT NULL,
  3.                                      time_id DATE NOT NULL,
  4.                                       channel_id char(1) NOT NULL,
  5.                                          promo_id NUMBER (6) NOT NULL,
  6.                                           quantity_sold NUMBER(3) NOT NULL,
  7.                                             amount_sold NUMBER(10,2) NOT NULL )
  8. PARTITION BY RANGE (time_id)
  9. SUBPARTITION BY RANGE (cust_id)
  10. SUBPARTITION TEMPLATE
  11.    (  SUBPARTITION sp1 VALUES LESS THAN (50000),
  12.        SUBPARTITION sp2 VALUES LESS THAN (100000),   
  13.         SUBPARTITION sp3 VALUES LESS THAN (150000),
  14.              SUBPARTITION sp4 VALUES LESS THAN (MAXVALUE) )
  15. (
  16.   PARTITION p1 VALUES LESS THAN (TO_DATE('1-4-2007','DD-MM-YYYY')),
  17.   PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2007','DD-MM-YYYY')),
  18.   PARTITION p3 VALUES LESS THAN (TO_DATE('1-8-2007','DD-MM-YYYY')),
  19.   PARTITION p4 VALUES LESS THAN (TO_DATE('1-1-2008','DD-MM-YYYY'))
  20. );

  21. select * from user_segments s where s.segment_name='SALES3';
  22. ---
  23. ---

  24. select  * from dba_tablespaces;
  25. ---

  26. create table tbasic_s ( a varchar2(20)   encrypt  no salt ,
  27.                                  b blob )
  28.                      lob(b) store as    (tablespace example
  29.               disable storage in row
  30.          chunk 16384      storage (initial 2M ) nocache );            

  31. select   s.segment_name, bytes/1024/1024
  32.   from user_segments s where s.segment_name in ('SYS_LOB0000075344C00002


  33. ,
  34.   
  35.   'SYS_LOB0000075364C00002


  36. );

  37. select  * from tbasic;
  38. declare
  39.   v_f bfile;
  40.   v_b blob;
  41. begin
  42.    insert into tsecure2 values ('a.dat',empty_blob()) return b into v_b;
  43.    v_f := bfilename('DIR1','a.dat');
  44.    dbms_lob.fileopen(v_f,dbms_lob.file_readonly);
  45.    dbms_lob.loadfromfile(dest_lob => v_b,
  46.    src_lob => v_f,
  47.    amount => dbms_lob.getlength(v_f) );
  48.    dbms_lob.fileclose(v_f);
  49.    commit;
  50. end;
  51.      
  52. select  * from tbasic;

  53. select  * from user_lobs;

  54. select  * from tsecure;

  55. ----

  56. create table tsecure5 ( a varchar2(20),
  57.                                  b blob )
  58.                      lob(b) store as   securefile   (tablespace example
  59.               disable storage in row
  60.              nocache     compress    deduplicate      );   
  61.             
  62. ---
  63.                
  64. create table tsecure_s2 ( a varchar2(20),
  65.                                  b blob  encrypt  using '3des168'  )
  66.                      lob(b) store as   securefile   (tablespace example
  67.               disable storage in row
  68.              nocache     compress high   deduplicate      );  
  69.             
  70. select  * from user_encrypted_columns;            

  71. create table tsecure2 ( a varchar2(20),
  72.                                  b blob  )
  73.                      lob(b) store as   securefile   (tablespace example
  74.               disable storage in row
  75.              nocache     compress high   keep_duplicates      );  
  76.             
  77.          alter table tsecure2 modify  lob(b) (nocompress);

  78.    
复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 22:30 , Processed in 0.049190 second(s), 33 queries .

快速回复 返回顶部 返回列表