GaussDB 1.0.1升级到1.0.2及1.0.2相关新功能说明

2025-04-07 技术教程

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

FILETYPEOutputfiletype:(TXT),BINLOGLogfileofscreenoutputCOMPRESSCompressoutputfile(0),onlyforFILETYPE=BIN,valuesis0~9,litterforfastercompressspeed,0isnotcompressed.CONTENTSpecifiesdatatounloadwherethevalidkeyword,valuesare:(ALL),DATA_ONLY,andMETADATA_ONLY.QUERYPredicateclauseusedtoexportasubsetofatable,eg."whererownum<=10"SKIP_COMMENTSDonotaddcommentstodumpfile.(N)FORCEContinueevenifanSQLerroroccursduringatabledump.(N)SKIP_ADD_DROP_TABLEDonotaddaDROPTABLEstatementbeforeeachCREATETABLEstatement.(N)SKIP_TRIGGERSDonotdumptriggers.(N)QUOTE_NAMESQuoteidentifiers.(Y)TABLESPACEDefaulttransportalltablespacesexceptforsystemreserved.(N)COMMIT_BATCHBatchcommitrows,commitonceifset0.(1000)INSERT_BATCHBatchinsertrows.(1)FEEDBACKFeedbackrowcount,feedbackonceifset0(10000)PARALLELTabledataexportparallelismsettings,range2~16,Thedefaultvalueis0CONSISTENTCross-tableconsistency(N)CREATE_USERExportuserdefinition(N),UsedinconjunctionwithUSERS.ROLEExportuserrolesexpectsystempresetroles(N),UsedinconjunctionwithUSERS.GRANTGrantroleandpemissiontoUSER(N),UsedinconjunctionwithUSERSandROLE.WITH_CR_MODEExporttablesandindexeswithCR_MODEoptions(N)ENCRYPTExportfileswillbeencrypted.REMAP_TABLESTable'snamewillremappedtoanothertablename.PARTITIONSExporttables'sdatawithintheinputpartition.

++++++ 新增加的函数：

1)current_local_ScnSQL>SELECTCURRENT_LOCAL_SCN()FROMSYS_DUMMY;CURRENT_LOCAL_SCN()--------------------67551163231682571rowsfetched.

2)DBA_FBDR_2PC(从undo表空间中查询已完成的两阶段事务信息)

SQL>select*FROMTABLE(DBA_FBDR_2PC(6755116323168257,1));GLOBAL_TRAN_IDLOCAL_TRAN_IDTLOCK_LOBSTLOCK_LOBS_EXTFORMAT_IDBRANCH_IDOWNERPREPARE_SCNCOMMIT_SCN--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0rowsfetched.

3）DBA_PAGE_CORRUPTION
这个函数功能非常强大和实用。

SQL>select*fromtable(dba_page_corruption('DATABASE'));FILE_IDFILE_NAMEINFO_TYPEEXAMINED_NUMSUCCEED_NUMCORRUPT_NUMPAGE_IDPAGE_TYPEMARKED_CHECKSUMCALC_CHECKSUM---------------------------------------------------------------------------------------------------------------------------------------------------------------0/opt/gauss/gaussdata/systemFILESUMMARY2778277803/opt/gauss/gaussdata/undoFILESUMMARY664906649004/opt/gauss/gaussdata/user1FILESUMMARY255462554605/opt/gauss/gaussdata/user2FILESUMMARY1106/opt/gauss/gaussdata/user3FILESUMMARY1107/opt/gauss/gaussdata/user4FILESUMMARY1108/opt/gauss/gaussdata/user5FILESUMMARY1109/opt/gauss/gaussdata/temp2_01FILESUMMARY22010/opt/gauss/gaussdata/temp2_02FILESUMMARY11011/opt/gauss/gaussdata/temp2_undoFILESUMMARY22012/opt/gauss/gaussdata/sysauxFILESUMMARY1379813798011rowsfetched.SQL>select*fromtable(dba_page_corruption('TABLESPACE',3));FILE_IDFILE_NAMEINFO_TYPEEXAMINED_NUMSUCCEED_NUMCORRUPT_NUMPAGE_IDPAGE_TYPEMARKED_CHECKSUMCALC_CHECKSUM---------------------------------------------------------------------------------------------------------------------------------------------------------------4/opt/gauss/gaussdata/user1FILESUMMARY255462554605/opt/gauss/gaussdata/user2FILESUMMARY1106/opt/gauss/gaussdata/user3FILESUMMARY1107/opt/gauss/gaussdata/user4FILESUMMARY1108/opt/gauss/gaussdata/user5FILESUMMARY1105rowsfetched.SQL>select*fromtable(dba_page_corruption('DATAFILE',3));FILE_IDFILE_NAMEINFO_TYPEEXAMINED_NUMSUCCEED_NUMCORRUPT_NUMPAGE_IDPAGE_TYPEMARKED_CHECKSUMCALC_CHECKSUM---------------------------------------------------------------------------------------------------------------------------------------------------------------3/opt/gauss/gaussdata/undoFILESUMMARY664906649001rowsfetched.SQL>select*fromtable(dba_page_corruption('PAGE',4,10));FILE_IDFILE_NAMEINFO_TYPEEXAMINED_NUMSUCCEED_NUMCORRUPT_NUMPAGE_IDPAGE_TYPEMARKED_CHECKSUMCALC_CHECKSUM---------------------------------------------------------------------------------------------------------------------------------------------------------------4/opt/gauss/gaussdata/user1PAGE11010btree_segment36019360191rowsfetched.LSCN2GSCN(将本地SCN转换为GTS SCN)

SQL>selectcurrent_local_Scn()fromsys_dummy;CURRENT_LOCAL_SCN()--------------------67587681406689291rowsfetched.SQL>selectLSCN2GSCN(6758768140668929)fromsys_dummy;LSCN2GSCN(6758768140668929)---------------------------1582983139938017291rowsfetched.

PENDING_TRANS_SESSION(查询正在执行的两阶段事务信息)

rank(聚合、分析函数)

SQL>selectRANK(2)WITHINGROUP(ORDERBYa)as"rank"FROMroger.test;rank------------21rowsfetched.

7）TO_BIGINT(将数据转换成BIGINT类型)

SQL>selectto_bigint(12341)fromsys_dummy;TO_BIGINT(12341)--------------------123411rowsfetched.

8)TO_INT(将数据转换成INT类型)

SQL>selectto_int(99999)fromsys_dummy;TO_INT(99999)-------------999991rowsfetched.

9)TRY_GET_SHARED_LOCK(为一个会话尝试获取一把锁名为name_expr的共享咨询锁)

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

SQL>connroger/Roger007@127.0.0.1:1611connected.SQL>createtabletest_2asselect*fromtestlimit5;Succeed.SQL>selectafromtestintersectselectafromtest_2;A----------------------------------------26.531219482421875605.14545440673828125645.55263519287109375710.174560546875757.1773529052734375