Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to execute SQL statement for table backup". In daily operation, I believe many people have doubts about how to execute SQL statement for table backup. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubt of "how to execute SQL statement for table backup". Next, please follow the editor to study!

Unlike backing up databases and tablespaces, backing up tables does not require server configuration archiving, and you can back up user tables by entering the following in DIsql:

SQL > backup table T1 backupset 'tab_t1_bak_01';executed successfullyused time: 0000 backupset 14.215. Execute id is 77.SQL > select * from v$backupset where backup_path like'+ DMDATA/data/rac/bak/tab%' LINEID DEVICE_TYPE BACKUP_ID PARENT_ID BACKUP_NAME BACKUP_PATH TYPE LEVEL RANGE# OBJECT_NAME OBJECT_ID BASE_NAME BACKUP_TIME DESC# ENCRYPT_TYPE COMPRESS_LEVEL WITHOUT_LOG USE_PWR PKG_SIZE BEGIN_LSN END_LSN BKP_NUM DBF_NUM PARALLEL_NUM DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE - -1 DISK-1366932633-1 TAB_T1_20200529_144738_ 000092 + DMDATA/data/rac/bak/tab_t1_bak_01 20 3 SYSDBA.T1-1 2020-05-29 14 purl 47 purl 45.000864 0000 33554432 50908 51025 10 1 00 49398 117507596 0used time: 00:00:01.064. Execute id is 79.

The backup set "tab_t1_bak_01" is generated to the default backup path. If you want to set other backup options, refer to the backup table syntax below.

The syntax is as follows:

BACKUP TABLE

[TO

] BACKUPSET ['

'] [DEVICE TYPE

[PARMS'

']] [BACKUPINFO'

'] [MAXPIECESIZE

] [IDENTIFIED BY

[WITH ENCRYPTION] [ENCRYPT WITH

]] [COMPRESSED [LEVEL

] [TRACE FILE'] [TRACE LEVEL]

TABLE: specifies the table to be backed up, and only user tables can be backed up.

TO: specifies the name of the generated backup. If not specified, the system generates randomly. The default backup name format is: DB_ backup type _ table name _ backup time. Where the backup time is the system time to start the backup.

BACKUPSET: specifies the current backup set generation path, or generates the backup set in the default backup path if specified as a relative path. If you do not specify a specific backup set path, the default table backup set directory is generated with convention rules under the default backup path. Table backup default backup set directory name generation rule: TAB_ table name _ BTREE_ time, such as

TAB_T1_BTREE_20160518_143057_123456 . Indicates that the backup set is a table backup set with the table name T1 generated at 14:30:57 123456 milliseconds on May 18, 2016. If the table name is too long so that the length of the full name is more than 128bytes, the table name field is removed and adjusted to TAB_BTREE_ time.

DEVICE TYPE: the type of media on which the backup set is stored. Table backup only supports DISK for the time being, which means that the backup set is stored to disk.

PARMS: valid only if the media type is TAPE.

BACKUPINFO: description of the backup. The maximum is 256 bytes.

MAXPIECESIZE: maximum backup slice file size limit, in M units, the minimum 128MMaget 32-bit system, the maximum 2GMAG 64-bit system, the maximum 128G.

IDENTIFIED BY: specifies the encrypted password for backup. Passwords are enclosed in double quotation marks to prevent some special characters from failing syntax checking. The password setting rules follow the password policy specified by the ini parameter pwd_policy.

WITH ENCRYPTION: specify the encryption type. 0 means no encryption and no encryption of the backup file; 1 means simple encryption and sets a password for the backup file, but the contents of the file are still stored in plaintext; 2 means full data encryption, complete encryption of the backup file, and backup file is stored in ciphertext.

ENCRYPT WITH: encryption algorithm. By default, the algorithm is AES256_CFB.

Encryption algorithms include:

DES_ECB 、 DES_CBC 、 DES_CFB 、 DES_OFB 、 DESEDE_ECB 、 DESEDE_CBC 、 DESEDE_CFB 、 DESEDE_OFB 、 AES128_ECB 、 AES128_CBC 、 AES128_CFB 、 AES128_OFB 、 AES192_ECB 、 AES192_CBC 、 AES192_CFB 、 AES192_OFB 、 AES256_ECB 、 AES256_CBC 、 AES256_CFB 、 AES256_OFB 、 RC4

COMPRESSED: value range: 0-9. 0 means no compression, 1 means level 1 compression, and 9 represents level 9 compression. The higher the compression level, the slower the compression, but the higher the compression ratio. If not specified, but COMPRESSED is specified, the default is 1; otherwise, the default is 0.

TRACE FILE: specifies the generated TRACE file. When TRACE is enabled, but TRACE FILE is not specified, the DM_SBTTRACE_ year and month .log file is generated by default in the log directory of the DM database system; if the relative path is used, it is generated in the same level directory of the execution code. If specified by the user, the specified file cannot be an existing file, otherwise an error will be reported, nor can it be an ASM file.

TRACE LEVEL: valid values 1, 2. Default 1 means that TRACE is not enabled. If TRACE FILE is specified, a TRACE file will be generated, but TRACE information will not be written. Enable TRACE for 2 and write TRACE-related content.

Instructions for use:

1. Backup of users' non-partitioned row storage tables and heap tables is supported. Among them, system tables, temporary tables, materialized view charts, materialized view subsidiary tables and log tables, and tables under specific mode (DBG_PKG/INFORMATION_SCHEMA/INFO_SCHEM/SYSREP/SYSGEO/SYSJOB/SYSCPT/SYS) do not support backup. Table backups are not supported for tables whose table column type is object type. The table backup is not a comment on the table, and the function definition in the default expression, so you need to confirm it when you restore it.

two。 When the backup data exceeds the limit, a new backup file is generated, and the new backup file name is followed by the initial file name followed by the file number.

3. When a table is backed up, its owning tablespace must be online.

4. Currently, table backup does not support backup to TAPE media.

two。 Set backup options

The common backup options for table backup are setting backup name, setting backup set path, specifying media parameters, adding backup description, and so on, in the same way as database backup.

3. Backup tabl

The table backup copy specifies that all the data pages used by the table are put into the backup set and records the logical relationship between the individual data pages to restore the table data structure. Table backups are all online full backups, there is no need to back up archive logs, and there is no such thing as incremental backup. You can choose table backup when a table in the database is important and there is no need to back up the entire database or tablespace. The steps to complete the backup table are as follows:

1) make sure that the database is in OPEN state.

2) create a table to be backed up TAB_01:

SQL > create table tab_01 (C1 int); executed successfullyused time: 32.117 (ms). Execute id is 80.

3) enter the backup table statement in DIsql. The simple backup statement is as follows:

SQL > backup table tab_01 to tab_tab_01_backup_20200529 backupset 'table_tab_01_backup_20200529';executed successfullyused time: 00, 00, 00, 13. 981. Execute id is 81.

The above statement generates a backup set in the default backup directory + DMDATA/data/rac/bak (specified by the BAK_PATH parameter)

SQL > select * from v$backupset where backup_path='+DMDATA/data/rac/bak/table_tab_01_backup_20200529' LINEID DEVICE_TYPE BACKUP_ID PARENT_ID BACKUP_NAME BACKUP_PATH TYPE LEVEL RANGE# OBJECT_NAME OBJECT_ID BASE_NAME BACKUP_TIME DESC# ENCRYPT_TYPE COMPRESS_LEVEL WITHOUT_LOG USE_PWR PKG_SIZE BEGIN_LSN END_LSN BKP_ NUM DBF_NUM PARALLEL_NUM DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE -1 DISK 2025284018-1 TAB_TAB_01_BACKUP_20200529 + DMDATA/data/rac/bak/table_tab_01_backup_20200529 20 3 SYSDBA.TAB_01-1 2020-05-29 15 purl 08purl 24.000485000 033554432 50908 51055 10 10 0 49398 117507596 0used time: 00:00:01.063. Execute id is 82. At this point, the study on "how to execute SQL statements for table backup" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.