Bo's Oracle Station

【博客文章2021】数据仓库常用操作

2021-11-1 15:12| 发布者: admin| 查看: 1| 评论: 0|原作者: Bo Tang

摘要: 【博客文章2021】数据仓库常用操作Author: Bo Tang查看一条不久之前执行过SQL语句是否并行执行,如果就在执行者的会话之中:$ sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 15 09:41:51 2018Copyright (c) 1982, 2009, Oracle.All rights reserved.SQL conn sh/shConnected.SQL SELECT /*+ FULL(s) FUL

【博客文章2021】数据仓库常用操作

Author: Bo Tang


查看一条不久之前执行过SQL语句是否并行执行,如果就在执行者的会话之中:
  1. [oracle@station36 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 15 09:41:51 2018

  3. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  4. SQL> conn sh/sh
  5. Connected.
  6. SQL> SELECT /*+ FULL(s) FULL(t) */ count(*)
  7. FROM
  8.   3  sales s, times t
  9.   4  WHERE s.time_id = t.time_id;

  10.   COUNT(*)
  11. ----------
  12.     918843

  13. SQL> select  * from v$pq_sesstat;

  14. STATISTIC                       LAST_QUERY SESSION_TOTAL
  15. ------------------------------ ---------- -------------
  16. Queries Parallelized                        0              0
  17. DML Parallelized                        0              0
  18. DDL Parallelized                        0              0
  19. DFO Trees                                0              0
  20. Server Threads                                0              0
  21. Allocation Height                        0              0
  22. Allocation Width                        0              0
  23. Local Msgs Sent                         0              0
  24. Distr Msgs Sent                         0              0
  25. Local Msgs Recv'd                        0              0
  26. Distr Msgs Recv'd                        0              0

  27. 11 rows selected.

  28. SQL>
复制代码
如果是DBA:
  1. select  s.SQL_FULLTEXT,s.HASH_VALUE,s.SQL_ID,s.ADDRESS
  2.   from v_$sql s
  3.   where s.SQL_FULLTEXT like '%FULL(s)%';
  4.   
  5.   ---1653309287
  6.   ---1c96q25j8qzv7
  7.   ---2CF4324C
  8.   
  9.   ---------------------------------
  10.   
  11.   select  sp.OPERATION,  sp.OTHER_TAG   from v_$sql_plan  sp
  12.     where sp.SQL_ID='1c96q25j8qzv7';
复制代码

在一个会话之中,强制并行查询:
  1. SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 5;

  2. Session altered.
复制代码
作为DBA验证:
  1. SELECT pq_status
  2. FROM  v$session
  3. WHERE username='SH';
复制代码
   PQ_STATUS
1FORCED

  1. SQL> SELECT /*+ FULL(s) FULL(t) */ count(*)
  2.   2  FROM
  3.   3  sales s, times t
  4.   4  WHERE s.time_id = t.time_id;

  5.   COUNT(*)
  6. ----------
  7.     918843

  8. SQL> select  * from v$pq_sesstat;

  9. STATISTIC                       LAST_QUERY SESSION_TOTAL
  10. ------------------------------ ---------- -------------
  11. Queries Parallelized                        1              1
  12. DML Parallelized                        0              0
  13. DDL Parallelized                        0              0
  14. DFO Trees                                1              1
  15. Server Threads                               10              0
  16. Allocation Height                        5              0
  17. Allocation Width                        1              0
  18. Local Msgs Sent                       311            311
  19. Distr Msgs Sent                         0              0
  20. Local Msgs Recv'd                      311            311
  21. Distr Msgs Recv'd                        0              0

  22. 11 rows selected.

  23. SQL>
复制代码
  1. select  s.SQL_FULLTEXT,s.HASH_VALUE,s.SQL_ID,s.ADDRESS
  2.   from v_$sql s
  3.   where s.SQL_FULLTEXT like '%FULL(s)%';


  4. --1c96q25j8qzv7

  5. select  sp.OPERATION,  sp.OTHER_TAG   from v_$sql_plan  sp
  6.     where sp.SQL_ID='1c96q25j8qzv7';
复制代码
   OPERATIONOTHER_TAG
1SELECT STATEMENT
2SORT
3PX COORDINATOR
4PX SENDPARALLEL_TO_SERIAL
5SORTPARALLEL_COMBINED_WITH_PARENT
6HASH JOINPARALLEL_COMBINED_WITH_PARENT
7PART JOIN FILTERPARALLEL_COMBINED_WITH_PARENT
8PX RECEIVEPARALLEL_COMBINED_WITH_PARENT
9PX SENDPARALLEL_TO_PARALLEL
10PX BLOCKPARALLEL_COMBINED_WITH_CHILD
11TABLE ACCESSPARALLEL_COMBINED_WITH_PARENT
12PX BLOCKPARALLEL_COMBINED_WITH_CHILD
13TABLE ACCESSPARALLEL_COMBINED_WITH_PARENT
14SELECT STATEMENT
15SORT
16HASH JOIN
17PART JOIN FILTER
18TABLE ACCESS
19PARTITION RANGE
20TABLE ACCESS

--------------------------------并行简单的原理和查询:

  1. Last login: Sun Jul 15 09:43:14 2018 from station90.example.com
  2. [root@station36 ~]# ps aux | grep ora_p0
  3. oracle   11720  0.0  0.6 570260 25680 ?        Ss   10:13   0:00 ora_p000_orcl
  4. oracle   11722  0.0  0.5 570264 24156 ?        Ss   10:13   0:00 ora_p001_orcl
  5. oracle   11724  0.0  0.5 570252 21232 ?        Ss   10:13   0:00 ora_p002_orcl
  6. oracle   11726  0.0  0.5 570252 20956 ?        Ss   10:13   0:00 ora_p003_orcl
  7. oracle   11731  0.0  0.5 570252 21624 ?        Ss   10:13   0:00 ora_p004_orcl
  8. root     14929  0.0  0.0   3920   688 pts/6    S+   10:20   0:00 grep ora_p0
  9. [root@station36 ~]#
复制代码

定位主会话的终端:
  1. SQL> select * from little_sales;
  2. select * from little_sales
  3.               *
  4. ERROR at line 1:
  5. ORA-12838: cannot read/modify an object after modifying it in parallel


  6. SQL> !ps
  7.   PID TTY          TIME CMD
  8. 5241 pts/3    00:00:00 sqlplus
  9. 14888 pts/3    00:00:00 ps

复制代码
  1.   select  * from v_$lock  l
  2.   where sid  
  3.   in (select  sid from v$session where username='SH')
  4. and l.TYPE='TM' ;

  5. select  sid from v$session
  6.   where terminal='pts/3'
  7.   minus  
  8.   select  s.sid    from v_$session  s  , v_$process p
  9.    where s.PADDR=p.ADDR
  10.    and p.SPID  in (  11720, 11724,11731,  11722, 11726   );
复制代码
------------------------------------------------BASIC LOB
建带LOB的表,建议在图形界面上操作:


  1. CREATE TABLE HR.T_PIC ( PIC_NAME VARCHAR2(30) NOT NULL , PIC BLOB NOT NULL )
  2.     LOB (PIC) STORE AS BASICFILE
  3.     ( TABLESPACE EXAMPLE
  4.       DISABLE STORAGE IN ROW
  5.       CHUNK  32K
  6.     STORAGE ( INITIAL 2M next 2M)
  7.     PCTVERSION 20
  8.     CACHE );
复制代码
  1.     select  * from dba_lobs l where l.owner='HR'
  2.     and l.table_name='T_PIC';
复制代码
   OWNERTABLE_NAMECOLUMN_NAMESEGMENT_NAMETABLESPACE_NAMEINDEX_NAMECHUNKPCTVERSIONRETENTIONFREEPOOLSCACHELOGGINGENCRYPTCOMPRESSIONDEDUPLICATIONIN_ROWFORMATPARTITIONEDSECUREFILESEGMENT_CREATEDRETENTION_TYPERETENTION_VALUE
1HRT_PICPICSYS_LOB0000080631C00002$$EXAMPLESYS_IL0000080631C00002$$3276820YESYESNONENONENONENONOT APPLICABLENONONONO

  1. select  * from dba_directories;

  2. grant read,write on directory picdir to hr;

  3. CREATE OR REPLACE PROCEDURE  hr.proc_pic (p_dir  varchar2, p_name varchar2)
  4. IS
  5.    v_f  BFILE;
  6.    v_b blob;
  7. BEGIN
  8.     INSERT INTO hr.t_pic values(p_name, EMPTY_BLOB ())
  9.       RETURN   pic  into v_b;
  10.     v_f := BFILENAME (p_dir, p_name);
  11.    DBMS_LOB.FILEOPEN  (v_f, DBMS_LOB.FILE_READONLY);
  12.    DBMS_LOB.LOADFROMFILE (v_b, v_f,  DBMS_LOB.GETLENGTH (v_f));
  13.    DBMS_LOB.FILECLOSE (v_f);
  14.    commit;
  15. end;

  16. begin
  17.   hr.proc_pic('PICDIR','origin.png');
  18. end;

  19. select  * from hr.t_pic;

  20. select  * from dba_segments s
  21. where s.segment_name='SYS_LOB0000080631C00002‘
  22. and s.owner='HR';
复制代码
   OWNERSEGMENT_NAMEPARTITION_NAMESEGMENT_TYPESEGMENT_SUBTYPETABLESPACE_NAMEHEADER_FILEHEADER_BLOCKBYTESBLOCKSEXTENTSINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSMAX_SIZERETENTIONMINRETENTIONPCT_INCREASEFREELISTSFREELIST_GROUPSRELATIVE_FNOBUFFER_POOLFLASH_CACHECELL_FLASH_CACHE
1HRSYS_LOB0000080631C00002$$LOBSEGMENTASSMEXAMPLE551420971522562209715220971521214748364521474836455DEFAULTDEFAULTDEFAULT

改成SecureFile:
先检查参数db_securefile,绝对不能是never:
  1. CREATE TABLE HR.T_PIC ( PIC_NAME VARCHAR2(40) NOT NULL ,
  2.                                          PIC BLOB NOT NULL )
  3.                         LOB (PIC) STORE AS SECUREFILE ( TABLESPACE EXAMPLE
  4.                                                                                 DISABLE STORAGE IN ROW
  5.                                                                                 RETENTION MAX
  6.                                                                                 DEDUPLICATE
  7.                                                                                 COMPRESS MEDIUM
  8.                                                                                 STORAGE ( INITIAL 2M   next 2M     MAXSIZE 10M )
  9.                                                                                 CACHE );
复制代码

加密的SecureFile:
1. 检查一下TDE的钱包有没有打开:
  1. SQL> set linesize 2000
  2. SQL> select  * from v$encryption_wallet;

  3. WRL_TYPE
  4. --------------------
  5. WRL_PARAMETER
  6. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  7. STATUS
  8. ------------------
  9. file
  10. /u01/app/oracle/admin/orcl/wallet
  11. CLOSED


  12. SQL>
复制代码
建这个目录:/u01/app/oracle/admin/orcl/wallet
原理是以上目录里的master key来加密enc$的表(列)密钥:
  1. [oracle@station90 wallet]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 15 17:02:48 2018

  3. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> desc enc$
  7. Name                                           Null?    Type
  8. ----------------------------------------- -------- ----------------------------
  9. OBJ#                                                    NUMBER
  10. OWNER#                                             NUMBER
  11. MKEYID                                             VARCHAR2(64)
  12. ENCALG                                             NUMBER
  13. INTALG                                             NUMBER
  14. COLKLC                                             RAW(2000)
  15. KLCLEN                                             NUMBER
  16. FLAG                                                    NUMBER

  17. SQL> desc dba_encrypted_columns
  18. Name                                           Null?    Type
  19. ----------------------------------------- -------- ----------------------------
  20. OWNER                                           NOT NULL VARCHAR2(30)
  21. TABLE_NAME                                   NOT NULL VARCHAR2(30)
  22. COLUMN_NAME                                   NOT NULL VARCHAR2(30)
  23. ENCRYPTION_ALG                                     VARCHAR2(29)
  24. SALT                                                    VARCHAR2(3)
  25. INTEGRITY_ALG                                            VARCHAR2(12)

  26. SQL> select  OWNER, TABLE_NAME  from  dba_encrypted_columns;

  27. no rows selected

  28. SQL> select  OBJ#,OWNER#  from enc$
  29.   2  ;

  30. no rows selected

  31. SQL>
复制代码
下面创建主密钥:
  1. SQL> alter system set encryption key identified by "oracle123";

  2. System altered.

  3. SQL> select  * from v$encryption_wallet;

  4. WRL_TYPE
  5. --------------------
  6. WRL_PARAMETER
  7. --------------------------------------------------------------------------------
  8. STATUS
  9. ------------------
  10. file
  11. /u01/app/oracle/admin/orcl/wallet
  12. OPEN


  13. SQL> alter system set encryption wallet close  identified by "oracle123";

  14. System altered.

  15. SQL> alter system set encryption wallet open  identified by "oracle123";

  16. System altered.

  17. SQL>
复制代码
  1. SQL> select  OBJ#,OWNER#  from enc$;

  2. no rows selected

  3. SQL> select  OWNER, TABLE_NAME  from  dba_encrypted_columns;

  4. no rows selected

  5. SQL> conn hr/oracle_4U
  6. Connected.
  7. CREATE TABLE func_spec(
  8. id number, doc CLOB ENCRYPT USING '3DES168' )
  9. LOB(doc) STORE AS SECUREFILE
  10.   4   (DEDUPLICATE LOB CACHE NOLOGGING);

  11. Table created.

  12. SQL> select  OBJ#,OWNER#  from enc$
  13.   2  ;
  14. select        OBJ#,OWNER#  from enc$
  15.                           *
  16. ERROR at line 1:
  17. ORA-00942: table or view does not exist


  18. SQL> conn / as sysdba
  19. Connected.
  20. SQL> select  OBJ#,OWNER#  from enc$;

  21.       OBJ#     OWNER#
  22. ---------- ----------
  23.      80644            1

  24. SQL> select  OWNER, TABLE_NAME  from  dba_encrypted_columns;

  25. OWNER                               TABLE_NAME
  26. ------------------------------ ------------------------------
  27. HR                               FUNC_SPEC


  28. SQL> select object_id from dba_objects where object_name='FUNC_SPEC';

  29. OBJECT_ID
  30. ----------
  31.      80644

  32. SQL> conn hr/hr
  33. ERROR:
  34. ORA-01017: invalid username/password; logon denied


  35. Warning: You are no longer connected to ORACLE.
  36. SQL> conn hr/oracle_4U
  37. Connected.
  38. SQL> insert into FUNC_SPEC  values ( 1, 'AAAAAAAA' )  ;

  39. 1 row created.

  40. SQL> commit;

  41. Commit complete.

  42. SQL> select  * from FUNC_SPEC;

  43.         ID
  44. ----------
  45. DOC
  46. --------------------------------------------------------------------------------
  47.          1
  48. AAAAAAAA


  49. SQL> conn / as sysdba
  50. Connected.
  51. SQL> alter system set encryption wallet close identified by "oracle123";

  52. System altered.

  53. SQL> conn hr/oracle_4U
  54. Connected.
  55. SQL> select  * from FUNC_SPEC;
  56. ERROR:
  57. ORA-28365: wallet is not open
  58. SQL>
复制代码
  1. alter system set encryption wallet open identified by "oracle123";

  2. alter table hr.t_pic modify ( pic  encrypt  using 'AES192' );

  3. select  * from dba_encrypted_columns;

  4. select  * from hr.t_pic;
复制代码

分区LOB:
  1. create table hr.tab1 (id number not null, c clob)
  2. partition by range(id)
  3. (partition p1 values less than (100) tablespace users lob(c) store as lobp1,
  4. partition p2 values less than (200) tablespace  users lob(c) store as lobp2,
  5. partition p3 values less than (300) tablespace  users lob(c) store as lobp3);

  6. select  * from dba_lob_partitions;
复制代码
   TABLE_OWNERTABLE_NAMECOLUMN_NAMELOB_NAMEPARTITION_NAMELOB_PARTITION_NAMELOB_INDPART_NAMEPARTITION_POSITIONCOMPOSITECHUNKPCTVERSIONCACHEIN_ROWTABLESPACE_NAMEINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSMAX_SIZERETENTIONMINRETENTIONPCT_INCREASEFREELISTSFREELIST_GROUPSLOGGINGBUFFER_POOLFLASH_CACHECELL_FLASH_CACHEENCRYPTCOMPRESSIONDEDUPLICATIONSECUREFILESEGMENT_CREATED
1HRTAB1CSYS_LOB0000080647C00002$$P1LOBP1SYS_IL_P611NO8192NOYESUSERSYESYESDEFAULTDEFAULTDEFAULTNONENONENONENONO
2HRTAB1CSYS_LOB0000080647C00002$$P2LOBP2SYS_IL_P622NO8192NOYESUSERSYESYESDEFAULTDEFAULTDEFAULTNONENONENONENONO
3HRTAB1CSYS_LOB0000080647C00002$$P3LOBP3SYS_IL_P633NO8192NOYESUSERSYESYESDEFAULTDEFAULTDEFAULTNONENONENONENONO
4SYSSTREAMS$_APPLY_SPILL_MSGS_PARTMESSAGESYS_LOB0000010937C00008$$P0SYS_LOB_P1SYS_IL_P21NO819210YESYESSYSAUX655361048576121474836452147483645NO11YESDEFAULTDEFAULTDEFAULTNONENONENONENOYES


DBMS_REDEFINITION.START_REDEF_TABLE
('sf_demo', 'resumes', 'resumes_interim',
'id id, first_name first_name, last_name last_name,
resume resume', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);    加红部分表示“在线重定义“的时候,orgin表没有primary key使用。






  1. create table hr.tab1 (id number not null, c clob)
  2. partition by range(id)
  3. (partition p1 values less than (100) tablespace users lob(c) store as lobp1,
  4. partition p2 values less than (200) tablespace  users lob(c) store as lobp2,
  5. partition p3 values less than (maxvalue) tablespace  users lob(c) store as lobp3);

  6. select  * from dba_lob_partitions;


  7. select  * from dba_lobs l where l.owner='HR' and l.table_name='TAB1';


  8. create table hr.tab2_tmp (id number not null, c clob)
  9. partition by range(id)
  10. (partition p1 values less than (100) tablespace users lob(c) store as securefile,
  11. partition p2 values less than (200) tablespace users lob(c) store as securefile,
  12. partition p3 values less than (maxvalue) tablespace users lob(c) store as securefile);


  13. alter table hr.tab1 add  constraint  c1  primary key ( id);



  14. begin
  15. for i in 1..10000000
  16. loop
  17.   insert into hr.tab1 values (i,'A');
  18.   commit;
  19.   end loop;
  20. end;  



  21. declare
  22.   error_count  number;
  23. begin
  24. dbms_redefinition.start_redef_table('HR','tab1','tab2_tmp');
  25. dbms_redefinition.copy_table_dependents('HR','tab1','tab2_tmp',1, true,true,true,false,error_count);
  26. dbms_redefinition.finish_redef_table('HR','tab1','tab2_tmp');
  27. end;



复制代码




路过

雷人

握手

鲜花

鸡蛋

QQ|手机版|Bo's Oracle Station   

GMT+8, 2022-4-11 15:16 , Processed in 0.047232 second(s), 21 queries .

返回顶部