甜橘子，专注成都网站制作网站设计与营销型网站建设与优化
QQ :532337155
电话：028-86922220
原文链接： /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.
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.
PENDING_TRANS_SESSION(查询正在执行的两阶段事务信息)
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