GaussDB 1.0.1升級到1.0.2及1.0.2相關新功能說明

原文鏈接： https://www.modb.pro/db/22102?xy 

FILETYPE                Output file type: (TXT), BIN
LOG                     Log file of screen output
COMPRESS                Compress output file (0), only for FILETYPE=BIN, values is 0~9, litter for faster compress speed, 0 is not compressed.
CONTENT                 Specifies data to unload where the valid keyword, values are: (ALL), DATA_ONLY, and METADATA_ONLY. 
QUERY                   Predicate clause used to export a subset of a table, eg. "where rownum <= 10" 
SKIP_COMMENTS           Do not add comments to dump file. (N)FORCE                   Continue even if an SQL error occurs during a table dump. (N)
SKIP_ADD_DROP_TABLE     Do not add a DROP TABLE statement before each CREATE TABLE statement. (N)
SKIP_TRIGGERS           Do not dump triggers. (N)
QUOTE_NAMES             Quote identifiers. (Y)TABLESPACE              Default transport all tablespaces except for system reserved. (N)
COMMIT_BATCH            Batch commit rows, commit once if set 0. (1000)
INSERT_BATCH            Batch insert rows. (1)
FEEDBACK                Feedback row count, feedback once if set 0 (10000)PARALLEL                Table data export parallelism settings, range 2~16, The default value is 0CONSISTENT              Cross - table consistency(N)
CREATE_USER             Export user definition(N),Used in conjunction with USERS.ROLE                    Export user roles expect system preset roles (N),Used in conjunction with USERS.GRANT                   Grant role and pemission to USER (N),Used in conjunction with USERS and ROLE.
WITH_CR_MODE            Export tables and indexes with CR_MODE options (N)ENCRYPT                 Export files will be encrypted.
REMAP_TABLES            Table's name will remapped to another tablename.
PARTITIONS              Export tables's data within the input partition.

++++++ 新增加的函數：

1) current_local_Scn
SQL> SELECT CURRENT_LOCAL_SCN() FROM SYS_DUMMY;
CURRENT_LOCAL_SCN() 
--------------------6755116323168257    
1 rows fetched.

2)DBA_FBDR_2PC(從undo表空間中查詢已完成的兩階段事務信息)

SQL> select * FROM TABLE(DBA_FBDR_2PC(6755116323168257,1)) ;              
GLOBAL_TRAN_ID                                                   LOCAL_TRAN_ID        TLOCK_LOBS                                                       TLOCK_LOBS_EXT                                                   FORMAT_ID            BRANCH_ID                                                        OWNER                PREPARE_SCN          COMMIT_SCN          
---------------------------------------------------------------- -------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------- ---------------------------------------------------------------- -------------------- -------------------- --------------------0 rows fetched.

3）DBA_PAGE_CORRUPTION
這個函數功能非常強大和實用。

SQL> select * from table(dba_page_corruption('DATABASE'));
FILE_ID      FILE_NAME                                INFO_TYPE     EXAMINED_NUM SUCCEED_NUM  CORRUPT_NUM  PAGE_ID      PAGE_TYPE          MARKED_CHECKSUM CALC_CHECKSUM------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------0            /opt/gauss/gaussdata/system              FILE SUMMARY  2778         2778         0                                                                         
3            /opt/gauss/gaussdata/undo                FILE SUMMARY  66490        66490        0                                                                         
4            /opt/gauss/gaussdata/user1               FILE SUMMARY  25546        25546        0                                                                         
5            /opt/gauss/gaussdata/user2               FILE SUMMARY  1            1            0                                                                         
6            /opt/gauss/gaussdata/user3               FILE SUMMARY  1            1            0                                                                         
7            /opt/gauss/gaussdata/user4               FILE SUMMARY  1            1            0                                                                         
8            /opt/gauss/gaussdata/user5               FILE SUMMARY  1            1            0                                                                         
9            /opt/gauss/gaussdata/temp2_01            FILE SUMMARY  2            2            0                                                                         
10           /opt/gauss/gaussdata/temp2_02            FILE SUMMARY  1            1            0                                                                         
11           /opt/gauss/gaussdata/temp2_undo          FILE SUMMARY  2            2            0                                                                         
12           /opt/gauss/gaussdata/sysaux              FILE SUMMARY  13798        13798        0                                                                         
11 rows fetched.
SQL> select * from table(dba_page_corruption('TABLESPACE',3));
FILE_ID      FILE_NAME                                INFO_TYPE     EXAMINED_NUM SUCCEED_NUM  CORRUPT_NUM  PAGE_ID      PAGE_TYPE          MARKED_CHECKSUM CALC_CHECKSUM------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------4            /opt/gauss/gaussdata/user1               FILE SUMMARY  25546        25546        0                                                                         
5            /opt/gauss/gaussdata/user2               FILE SUMMARY  1            1            0                                                                         
6            /opt/gauss/gaussdata/user3               FILE SUMMARY  1            1            0                                                                         
7            /opt/gauss/gaussdata/user4               FILE SUMMARY  1            1            0                                                                         
8            /opt/gauss/gaussdata/user5               FILE SUMMARY  1            1            0                                                                         
5 rows fetched.
SQL> select * from table(dba_page_corruption('DATAFILE',3));
FILE_ID      FILE_NAME                                INFO_TYPE     EXAMINED_NUM SUCCEED_NUM  CORRUPT_NUM  PAGE_ID      PAGE_TYPE          MARKED_CHECKSUM CALC_CHECKSUM------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------3            /opt/gauss/gaussdata/undo                FILE SUMMARY  66490        66490        0                                                                         
1 rows fetched.
SQL>  select * from table(dba_page_corruption('PAGE',4,10));
FILE_ID      FILE_NAME                                INFO_TYPE     EXAMINED_NUM SUCCEED_NUM  CORRUPT_NUM  PAGE_ID      PAGE_TYPE          MARKED_CHECKSUM CALC_CHECKSUM------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------4            /opt/gauss/gaussdata/user1               PAGE          1            1            0            10           btree_segment      36019           36019        
1 rows fetched.

1. LSCN2GSCN(將本地SCN轉換為GTS SCN)

SQL> select current_local_Scn() from sys_dummy;
CURRENT_LOCAL_SCN() 
--------------------6758768140668929    
1 rows fetched.
SQL> select LSCN2GSCN(6758768140668929) from sys_dummy;
LSCN2GSCN(6758768140668929)---------------------------158298313993801729         
1 rows fetched.

1. PENDING_TRANS_SESSION(查詢正在執行的兩階段事務信息)

2. rank(聚合、分析函數)

SQL> select RANK(2) WITHIN GROUP (ORDER BY a) as "rank" FROM roger.test;
rank        
------------2           
1 rows fetched.

7）TO_BIGINT(將數據轉換成BIGINT類型)

SQL> select to_bigint(12341) from sys_dummy;
TO_BIGINT(12341)    
--------------------12341               
1 rows fetched.

8)TO_INT(將數據轉換成INT類型)

SQL> select to_int(99999) from sys_dummy;
TO_INT(99999)-------------99999        
1 rows fetched.

9)TRY_GET_SHARED_LOCK(為一個會話嘗試獲取一把鎖名為name_expr的共享咨詢鎖)

++++SQL 操作 （支持交集查詢)

SQL> conn roger/Roger007@127.0.0.1:1611
connected.
SQL> create table test_2 as select * from test limit 5;
Succeed.
SQL> select a from test intersect select a from test_2;
A                                       
----------------------------------------26.531219482421875                      
605.14545440673828125                   
645.55263519287109375                   
710.174560546875                        
757.1773529052734375