GaussDB1.0.1升级到1.0.2及1.0.2相关新功能说明-创新互联

原文链接： /tupian/20230522/22102

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