【博客文章2021】数据仓库常用操作
Author: Bo Tang
查看一条不久之前执行过SQL语句是否并行执行,如果就在执行者的会话之中:
- [oracle@station36 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 15 09:41:51 2018
- Copyright (c) 1982, 2009, Oracle. All rights reserved.
- SQL> conn sh/sh
- Connected.
- SQL> SELECT /*+ FULL(s) FULL(t) */ count(*)
- FROM
- 3 sales s, times t
- 4 WHERE s.time_id = t.time_id;
- COUNT(*)
- ----------
- 918843
- SQL> select * from v$pq_sesstat;
- STATISTIC LAST_QUERY SESSION_TOTAL
- ------------------------------ ---------- -------------
- Queries Parallelized 0 0
- DML Parallelized 0 0
- DDL Parallelized 0 0
- DFO Trees 0 0
- Server Threads 0 0
- Allocation Height 0 0
- Allocation Width 0 0
- Local Msgs Sent 0 0
- Distr Msgs Sent 0 0
- Local Msgs Recv'd 0 0
- Distr Msgs Recv'd 0 0
- 11 rows selected.
- SQL>
复制代码 如果是DBA:
- select s.SQL_FULLTEXT,s.HASH_VALUE,s.SQL_ID,s.ADDRESS
- from v_$sql s
- where s.SQL_FULLTEXT like '%FULL(s)%';
-
- ---1653309287
- ---1c96q25j8qzv7
- ---2CF4324C
-
- ---------------------------------
-
- select sp.OPERATION, sp.OTHER_TAG from v_$sql_plan sp
- where sp.SQL_ID='1c96q25j8qzv7';
复制代码 在一个会话之中,强制并行查询:
- SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 5;
- Session altered.
复制代码 作为DBA验证:
- SELECT pq_status
- FROM v$session
- WHERE username='SH';
复制代码
- SQL> SELECT /*+ FULL(s) FULL(t) */ count(*)
- 2 FROM
- 3 sales s, times t
- 4 WHERE s.time_id = t.time_id;
- COUNT(*)
- ----------
- 918843
- SQL> select * from v$pq_sesstat;
- STATISTIC LAST_QUERY SESSION_TOTAL
- ------------------------------ ---------- -------------
- Queries Parallelized 1 1
- DML Parallelized 0 0
- DDL Parallelized 0 0
- DFO Trees 1 1
- Server Threads 10 0
- Allocation Height 5 0
- Allocation Width 1 0
- Local Msgs Sent 311 311
- Distr Msgs Sent 0 0
- Local Msgs Recv'd 311 311
- Distr Msgs Recv'd 0 0
- 11 rows selected.
- SQL>
复制代码- select s.SQL_FULLTEXT,s.HASH_VALUE,s.SQL_ID,s.ADDRESS
- from v_$sql s
- where s.SQL_FULLTEXT like '%FULL(s)%';
- --1c96q25j8qzv7
- select sp.OPERATION, sp.OTHER_TAG from v_$sql_plan sp
- where sp.SQL_ID='1c96q25j8qzv7';
复制代码 | OPERATION | OTHER_TAG | 1 | SELECT STATEMENT | | 2 | SORT | | 3 | PX COORDINATOR | | 4 | PX SEND | PARALLEL_TO_SERIAL | 5 | SORT | PARALLEL_COMBINED_WITH_PARENT | 6 | HASH JOIN | PARALLEL_COMBINED_WITH_PARENT | 7 | PART JOIN FILTER | PARALLEL_COMBINED_WITH_PARENT | 8 | PX RECEIVE | PARALLEL_COMBINED_WITH_PARENT | 9 | PX SEND | PARALLEL_TO_PARALLEL | 10 | PX BLOCK | PARALLEL_COMBINED_WITH_CHILD | 11 | TABLE ACCESS | PARALLEL_COMBINED_WITH_PARENT | 12 | PX BLOCK | PARALLEL_COMBINED_WITH_CHILD | 13 | TABLE ACCESS | PARALLEL_COMBINED_WITH_PARENT | 14 | SELECT STATEMENT | | 15 | SORT | | 16 | HASH JOIN | | 17 | PART JOIN FILTER | | 18 | TABLE ACCESS | | 19 | PARTITION RANGE | | 20 | TABLE ACCESS | |
--------------------------------并行简单的原理和查询:
- Last login: Sun Jul 15 09:43:14 2018 from station90.example.com
- [root@station36 ~]# ps aux | grep ora_p0
- oracle 11720 0.0 0.6 570260 25680 ? Ss 10:13 0:00 ora_p000_orcl
- oracle 11722 0.0 0.5 570264 24156 ? Ss 10:13 0:00 ora_p001_orcl
- oracle 11724 0.0 0.5 570252 21232 ? Ss 10:13 0:00 ora_p002_orcl
- oracle 11726 0.0 0.5 570252 20956 ? Ss 10:13 0:00 ora_p003_orcl
- oracle 11731 0.0 0.5 570252 21624 ? Ss 10:13 0:00 ora_p004_orcl
- root 14929 0.0 0.0 3920 688 pts/6 S+ 10:20 0:00 grep ora_p0
- [root@station36 ~]#
复制代码 定位主会话的终端:
- SQL> select * from little_sales;
- select * from little_sales
- *
- ERROR at line 1:
- ORA-12838: cannot read/modify an object after modifying it in parallel
- SQL> !ps
- PID TTY TIME CMD
- 5241 pts/3 00:00:00 sqlplus
- 14888 pts/3 00:00:00 ps
复制代码- select * from v_$lock l
- where sid
- in (select sid from v$session where username='SH')
- and l.TYPE='TM' ;
- select sid from v$session
- where terminal='pts/3'
- minus
- select s.sid from v_$session s , v_$process p
- where s.PADDR=p.ADDR
- and p.SPID in ( 11720, 11724,11731, 11722, 11726 );
复制代码 ------------------------------------------------BASIC LOB 建带LOB的表,建议在图形界面上操作:
- CREATE TABLE HR.T_PIC ( PIC_NAME VARCHAR2(30) NOT NULL , PIC BLOB NOT NULL )
- LOB (PIC) STORE AS BASICFILE
- ( TABLESPACE EXAMPLE
- DISABLE STORAGE IN ROW
- CHUNK 32K
- STORAGE ( INITIAL 2M next 2M)
- PCTVERSION 20
- CACHE );
复制代码- select * from dba_lobs l where l.owner='HR'
- and l.table_name='T_PIC';
复制代码 | OWNER | TABLE_NAME | COLUMN_NAME | SEGMENT_NAME | TABLESPACE_NAME | INDEX_NAME | CHUNK | PCTVERSION | RETENTION | FREEPOOLS | CACHE | LOGGING | ENCRYPT | COMPRESSION | DEDUPLICATION | IN_ROW | FORMAT | PARTITIONED | SECUREFILE | SEGMENT_CREATED | RETENTION_TYPE | RETENTION_VALUE | 1 | HR | T_PIC | PIC | SYS_LOB0000080631C00002$$ | EXAMPLE | SYS_IL0000080631C00002$$ | 32768 | 20 | | | YES | YES | NONE | NONE | NONE | NO | NOT APPLICABLE | NO | NO | NO | NO | |
- select * from dba_directories;
- grant read,write on directory picdir to hr;
- CREATE OR REPLACE PROCEDURE hr.proc_pic (p_dir varchar2, p_name varchar2)
- IS
- v_f BFILE;
- v_b blob;
- BEGIN
- INSERT INTO hr.t_pic values(p_name, EMPTY_BLOB ())
- RETURN pic into v_b;
- v_f := BFILENAME (p_dir, p_name);
- DBMS_LOB.FILEOPEN (v_f, DBMS_LOB.FILE_READONLY);
- DBMS_LOB.LOADFROMFILE (v_b, v_f, DBMS_LOB.GETLENGTH (v_f));
- DBMS_LOB.FILECLOSE (v_f);
- commit;
- end;
- begin
- hr.proc_pic('PICDIR','origin.png');
- end;
- select * from hr.t_pic;
- select * from dba_segments s
- where s.segment_name='SYS_LOB0000080631C00002‘
- and s.owner='HR';
复制代码 | OWNER | SEGMENT_NAME | PARTITION_NAME | SEGMENT_TYPE | SEGMENT_SUBTYPE | TABLESPACE_NAME | HEADER_FILE | HEADER_BLOCK | BYTES | BLOCKS | EXTENTS | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | MAX_SIZE | RETENTION | MINRETENTION | PCT_INCREASE | FREELISTS | FREELIST_GROUPS | RELATIVE_FNO | BUFFER_POOL | FLASH_CACHE | CELL_FLASH_CACHE | 1 | HR | SYS_LOB0000080631C00002$$ | | LOBSEGMENT | ASSM | EXAMPLE | 5 | 514 | 2097152 | 256 | 2 | 2097152 | 2097152 | 1 | 2147483645 | 2147483645 | | | | | | 5 | DEFAULT | DEFAULT | DEFAULT |
改成SecureFile: 先检查参数db_securefile,绝对不能是never:
- CREATE TABLE HR.T_PIC ( PIC_NAME VARCHAR2(40) NOT NULL ,
- PIC BLOB NOT NULL )
- LOB (PIC) STORE AS SECUREFILE ( TABLESPACE EXAMPLE
- DISABLE STORAGE IN ROW
- RETENTION MAX
- DEDUPLICATE
- COMPRESS MEDIUM
- STORAGE ( INITIAL 2M next 2M MAXSIZE 10M )
- CACHE );
复制代码 加密的SecureFile: 1. 检查一下TDE的钱包有没有打开:
- SQL> set linesize 2000
- SQL> select * from v$encryption_wallet;
- WRL_TYPE
- --------------------
- WRL_PARAMETER
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- STATUS
- ------------------
- file
- /u01/app/oracle/admin/orcl/wallet
- CLOSED
- SQL>
复制代码 建这个目录:/u01/app/oracle/admin/orcl/wallet 原理是以上目录里的master key来加密enc$的表(列)密钥:
- [oracle@station90 wallet]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 15 17:02:48 2018
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> conn / as sysdba
- Connected.
- SQL> desc enc$
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- OBJ# NUMBER
- OWNER# NUMBER
- MKEYID VARCHAR2(64)
- ENCALG NUMBER
- INTALG NUMBER
- COLKLC RAW(2000)
- KLCLEN NUMBER
- FLAG NUMBER
- SQL> desc dba_encrypted_columns
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- OWNER NOT NULL VARCHAR2(30)
- TABLE_NAME NOT NULL VARCHAR2(30)
- COLUMN_NAME NOT NULL VARCHAR2(30)
- ENCRYPTION_ALG VARCHAR2(29)
- SALT VARCHAR2(3)
- INTEGRITY_ALG VARCHAR2(12)
- SQL> select OWNER, TABLE_NAME from dba_encrypted_columns;
- no rows selected
- SQL> select OBJ#,OWNER# from enc$
- 2 ;
- no rows selected
- SQL>
复制代码 下面创建主密钥:
- SQL> alter system set encryption key identified by "oracle123";
- System altered.
- SQL> select * from v$encryption_wallet;
- WRL_TYPE
- --------------------
- WRL_PARAMETER
- --------------------------------------------------------------------------------
- STATUS
- ------------------
- file
- /u01/app/oracle/admin/orcl/wallet
- OPEN
- SQL> alter system set encryption wallet close identified by "oracle123";
- System altered.
- SQL> alter system set encryption wallet open identified by "oracle123";
- System altered.
- SQL>
复制代码- SQL> select OBJ#,OWNER# from enc$;
- no rows selected
- SQL> select OWNER, TABLE_NAME from dba_encrypted_columns;
- no rows selected
- SQL> conn hr/oracle_4U
- Connected.
- CREATE TABLE func_spec(
- id number, doc CLOB ENCRYPT USING '3DES168' )
- LOB(doc) STORE AS SECUREFILE
- 4 (DEDUPLICATE LOB CACHE NOLOGGING);
- Table created.
- SQL> select OBJ#,OWNER# from enc$
- 2 ;
- select OBJ#,OWNER# from enc$
- *
- ERROR at line 1:
- ORA-00942: table or view does not exist
- SQL> conn / as sysdba
- Connected.
- SQL> select OBJ#,OWNER# from enc$;
- OBJ# OWNER#
- ---------- ----------
- 80644 1
- SQL> select OWNER, TABLE_NAME from dba_encrypted_columns;
- OWNER TABLE_NAME
- ------------------------------ ------------------------------
- HR FUNC_SPEC
- SQL> select object_id from dba_objects where object_name='FUNC_SPEC';
- OBJECT_ID
- ----------
- 80644
- SQL> conn hr/hr
- ERROR:
- ORA-01017: invalid username/password; logon denied
- Warning: You are no longer connected to ORACLE.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> insert into FUNC_SPEC values ( 1, 'AAAAAAAA' ) ;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select * from FUNC_SPEC;
- ID
- ----------
- DOC
- --------------------------------------------------------------------------------
- 1
- AAAAAAAA
- SQL> conn / as sysdba
- Connected.
- SQL> alter system set encryption wallet close identified by "oracle123";
- System altered.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> select * from FUNC_SPEC;
- ERROR:
- ORA-28365: wallet is not open
- SQL>
复制代码- alter system set encryption wallet open identified by "oracle123";
- alter table hr.t_pic modify ( pic encrypt using 'AES192' );
- select * from dba_encrypted_columns;
- select * from hr.t_pic;
复制代码 分区LOB:
- create table hr.tab1 (id number not null, c clob)
- partition by range(id)
- (partition p1 values less than (100) tablespace users lob(c) store as lobp1,
- partition p2 values less than (200) tablespace users lob(c) store as lobp2,
- partition p3 values less than (300) tablespace users lob(c) store as lobp3);
- select * from dba_lob_partitions;
复制代码 | TABLE_OWNER | TABLE_NAME | COLUMN_NAME | LOB_NAME | PARTITION_NAME | LOB_PARTITION_NAME | LOB_INDPART_NAME | PARTITION_POSITION | COMPOSITE | CHUNK | PCTVERSION | CACHE | IN_ROW | TABLESPACE_NAME | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | MAX_SIZE | RETENTION | MINRETENTION | PCT_INCREASE | FREELISTS | FREELIST_GROUPS | LOGGING | BUFFER_POOL | FLASH_CACHE | CELL_FLASH_CACHE | ENCRYPT | COMPRESSION | DEDUPLICATION | SECUREFILE | SEGMENT_CREATED | 1 | HR | TAB1 | C | SYS_LOB0000080647C00002$$ | P1 | LOBP1 | SYS_IL_P61 | 1 | NO | 8192 | | NO | YES | USERS | | | | | | YES | | | | | YES | DEFAULT | DEFAULT | DEFAULT | NONE | NONE | NONE | NO | NO | 2 | HR | TAB1 | C | SYS_LOB0000080647C00002$$ | P2 | LOBP2 | SYS_IL_P62 | 2 | NO | 8192 | | NO | YES | USERS | | | | | | YES | | | | | YES | DEFAULT | DEFAULT | DEFAULT | NONE | NONE | NONE | NO | NO | 3 | HR | TAB1 | C | SYS_LOB0000080647C00002$$ | P3 | LOBP3 | SYS_IL_P63 | 3 | NO | 8192 | | NO | YES | USERS | | | | | | YES | | | | | YES | DEFAULT | DEFAULT | DEFAULT | NONE | NONE | NONE | NO | NO | 4 | SYS | STREAMS$_APPLY_SPILL_MSGS_PART | MESSAGE | SYS_LOB0000010937C00008$$ | P0 | SYS_LOB_P1 | SYS_IL_P2 | 1 | NO | 8192 | 10 | YES | YES | SYSAUX | 65536 | 1048576 | 1 | 2147483645 | 2147483645 | NO | | | 1 | 1 | YES | DEFAULT | DEFAULT | DEFAULT | NONE | NONE | NONE | NO | YES |
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使用。
- create table hr.tab1 (id number not null, c clob)
- partition by range(id)
- (partition p1 values less than (100) tablespace users lob(c) store as lobp1,
- partition p2 values less than (200) tablespace users lob(c) store as lobp2,
- partition p3 values less than (maxvalue) tablespace users lob(c) store as lobp3);
- select * from dba_lob_partitions;
- select * from dba_lobs l where l.owner='HR' and l.table_name='TAB1';
- create table hr.tab2_tmp (id number not null, c clob)
- partition by range(id)
- (partition p1 values less than (100) tablespace users lob(c) store as securefile,
- partition p2 values less than (200) tablespace users lob(c) store as securefile,
- partition p3 values less than (maxvalue) tablespace users lob(c) store as securefile);
- alter table hr.tab1 add constraint c1 primary key ( id);
- begin
- for i in 1..10000000
- loop
- insert into hr.tab1 values (i,'A');
- commit;
- end loop;
- end;
- declare
- error_count number;
- begin
- dbms_redefinition.start_redef_table('HR','tab1','tab2_tmp');
- dbms_redefinition.copy_table_dependents('HR','tab1','tab2_tmp',1, true,true,true,false,error_count);
- dbms_redefinition.finish_redef_table('HR','tab1','tab2_tmp');
- end;
复制代码
|
|