Table of Contents
The following chapter describes the tbExport utility and its usage.
tbExport is the export utility provided by Tibero. This utility helps to export all or some part of a schema object saved in a Tibero database, and then saves it into a unique type of file. Therefore, it is useful when processing a database backup and transmitting a database between different machines.
When you export one schema object using the tbExport utility, the related schema objects will also be exported automatically. For example, if you export one table, the constraints and indexes that are created with the table will also be exported. If necessary, it is possible to stop some of the related schema objects from being exported.
tbExport has three modes: full database mode, user mode, and table mode. They can only be used by a DBA. If DBA privilege cannot be granted, it is recommended to grant the SELECT ANY DICTIONARY privilege.
The files created through the tbExport utility are OS files. Therefore, unlike Tibero database files, these files can be processed as normal files. For example, they can be transmitted using FTP or saved on a CD-ROM and then sent to a remote Tibero database.
Specify the log, which occurs while exporting, by using the LOG parameter.
The following table describes the Complete, Alert, and Error messages created as a result of executing the tbExport utility.
Item | Description |
---|---|
Complete Message | Displayed when the export has been successfully processed. |
Alert Message | Displayed when the export has finished, but something is wrong. It occurs when users try to export a table that does not exist. In this case, the tbExport utility outputs an Alert message, skips the corresponding table, and then continues with exporting the next object. |
Error Message | Displayed when an error occurs during export and the export cannot continue. It occurs when the export cannot be processed anymore (when there is not enough system memory the necessary view for the export is not created). After this error message is output, the export session will be terminated. |
The tbExport utility has the following features.
Logical Backup
Extracts the data and internal schema of Tibero.
Data with Different Point of Time
When exporting many tables at once, data in the tables will not be exported all at the same time. The data will be exported in order.
Saves the Table Definition
Saves the table definition (DDL script of a table) regardless of the data existence.
Reorganizing the Table
After creating a table, it removes all the fragmentations and migrated rows caused by a lot of DML work.
The tbExport utility will be installed or removed when Tibero is installed or removed. Also, the tbExport utility is implemented in the Java language and it can be run on any platform in which JVM (Java Virtual Machine) is installed.
Check the following requirements are met before executing the tbExport utility.
If JRE 1.4.2 or higher is installed.
Should be installed on the same platform as Tibero database server or be connected through a network.
Necessary class libraries (the default location: $TB_HOME/client/lib/jar directory) for execution.
tbExport class: expimp.jar
Common library of the utilities: toolcom.jar
Common Logger library of the utilities: msllogger-14.jar
JDBC driver: internal-jdbc-14.jar
The Class libraries required for execution will be installed automatically when installing Tibero. Therefore, there are no additional tasks required for this.
Export mode has three modes: full database mode, user mode, and table mode. Each mode can be specified by using a parameter.
The following figure shows the relationship of schema objects, which are to be exported, in each mode.
Full database mode exports the entire Tibero database. It exports objects of all users except the SYS user.
To use the full database mode, specify Y for the FULL parameter.
FULL=Y
User mode exports all schema objects of the specified users, except the SYS user. In other words, user mode is used to export all the objects that belong to the specified users. A DBA can allow more than one user to use this mode.
To use the user mode, specify the userlist for the USER parameter. (USER=userlist format).
USER=SCOTT, USER1, …
Table mode specifies one or more tables and exports all of their schema objects, such as index.
To use table mode, specify the tablelist for the TABLE parameter (TABLE=tablelist format). Make sure to enter the user who owns the table like SCOTT.EMP as shown below.
TABLE=SCOTT.EMP, USER1.TABLE1, …
To execute the tbExport utility, enter tbexport command in the $TB_HOME/client/bin directory.
The following is an example of how to execute tbExportin full database mode.
[Example 3.1] Executing the tbExport utility
$ tbexport username=tibero password=tmax sid=tibero file=export.dat full=y $ tbexport cfgfile=export.cfg
If a user executes tbExport without specifying any values for the parameters the user will see a list of parameters and their usage, which can be entered at the command prompt, as shown below.
tbExport 6.0 102665 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Usage: tbexport [PARAMETER1=VALUE] [PARAMETER2=VALUE] ... Parameters: CFGFILE Config file name COMPRESS Compress Mode: Y/N, default: N CONSTRAINT Export Constraint: Y/N, default: Y CONSISTENT Consistent Mode: Y/N, default: N EXCLUDE Limit the export to specific objects FILE Export dump file name, default: default.dat FULL Full Mode: Y/N, default: N GEOM_ASBYTES Export the geometry columns as bytes, default: N GRANT Export Grant: Y/N, default: Y INDEX Export Index: Y/N, default: Y INLINE_CONSTRAINT Use the Inline Constraint: Y/N, default: N (this option is only supported for the not null) IP IP address, default: localhost LOG Export script log file name LOGDIR Export log directory NO_PACK_DIR Export unpacked dump files to specified directory. If this option is specified, FILE parameter will be ignored. OVERWRITE Overwrite datafile if same file name exists: Y/N, default: N PASSWORD User password PORT PORT number, default: 8629 QUERY Where predicate: (Optional) to filter data to be exported (must be used with TABLE parameter.) REMAP_TABLESPACE Remaps the objects from the source tablespace to the target tablespace. ROWS Export Table Rows: Y/N, default: Y SAVE_CREDENTIAL Save your username and password to specified file SCRIPT LOG THE DDL SCRIPT: Y/N, default: N SID Database name TABLE Table Mode: table name list Append :<Partition Name> to select a single partition (Optional) TARGETDB Target Server, default: TIBERO TEMP_DIR Directory for the temporary raw dump files. THREAD_CNT Thread Count, default: 4 USER User Mode: user name list USERNAME Database user name
Parameter values do not have to be entered in order. Among the parameter values, CFGFILE can only be entered at the command prompt. Other parameter values can be specified in the configuration file. Parameter names specified in the configuration file must be in uppercase.
The parameters used at the command prompt can be saved in the configuration file, in the following two ways. The second type is used when specifying two or more parameter values.
PARAMETER=value PARAMETER=value1, ...
The following is an example of how to specify a number of parameters.
FULL=Y FILE=EXPORT.DAT GRANT=Y INDEX=Y CONSTRAINT=Y
The following table describes the tbExport utility parameters, that can be entered at the command prompt.
Item | Description |
---|---|
CFGFILE | Configuration file name. |
COMPRESS | Option to compress data during export.
If this is set to Y, a single thread is used. |
CONSISTENT | Exports data.
This is not applicable for targets that are not supported in flashback query. |
CONSTRAINT | Option to export constraints.
|
EXCLUDE | Option to exclude a specific user or table when exporting. Exclusion clauses are shown below.
|
FILE | Name of the file that is created during the export. (The default value: default.dat) It is created in the operating system as a binary file type. If the name is not specified the default value will be used. |
FULL | Option to export in full database mode.
|
GEOM_ASBYTES | Option to retrieve a geometry column as WKB or bytes. (Default value: N)
|
GRANT | Option to export permissions.
|
INDEX | Option to export the index information.
|
INLINE_CONSTRAINT | Option to display scripts as inline constraints.
|
IP | IP address of theTibero server, that is to be exported. (Default value: localhost) |
LOG | Name of the export log file. |
LOGDIR | Name of the directory that contains the file that records export logs. |
NO_PACK_DIR | Directory which stores the unpacked dump file. If this option is specified, the value set in the FILE parameter is ignored. |
OVERWRITE | Option to overwrite the name of the file if it already exists.
|
PASSWORD | Password of the user, who performs the Export. |
PACK_TYPE (hidden) | Packing algorithm.
|
PARALLEL_DEGREE (hidden) | Parallel hint to use in queries used to export table data. (Default value: 0 (NOT PARALLEL)) |
PORT | Port number of the Tibero server, that is to be exported. (Default value: 8629) |
QUERY | Filter condition for the data to be exported.
|
REMAP_TABLESPACE | Changes the tablespace name. Usage:
|
REMAP_TABLE | Changes the table name. Usage:
|
ROWS | Option to export the table data.
|
SAVE_CREDENTIAL | Option to save encrypted username and password.
|
SCRIPT | Option to show the DDL script that creates a schema object during export.
|
SERVER_VER (hidden) | Tibero version to export. Scripts
are generated according to the version. Export version constants:
|
SID | SID of theTibero server, that is to be exported. |
TABLE | Name of the table to be exported in table mode. The format is TABLE=tablelist. For details, refer to "Table Mode" |
TEMP_DIR | Directory that stores temporary dump files when exporting data. |
THREAD_CNT | Number of threads, which are used to export the table data. (The default value: 4) |
USER | Owner of the object to be exported in the user mode. The format is USER=userlist. For details, refer to "User Mode" |
USERNAME | User account that will perform the export. |
The following is an example of exporting using the tbExport utility.
[Example 3.2] Executing Export by Using the tbExport Utility
tbExport 6.0 97668 TmaxData Corporation Copyright (c) 2008-. All rights reserved. the entire database: Fri Feb 06 10:45:16 KST 2015 Export character set: MS949 exporting tablespaces exporting roles exporting schema: "TIBERO" exporting tables [0] exporting table BONUS no rows exported. [1] exporting table DEPT 4 rows exported. [2] exporting table EMP 10 rows exported. [3] exporting table SALGRADE 5 rows exported. exporting object privileges exporting indexes exporting sequences exporting views exporting synonyms Packing the file... Export completed successfully: Fri Feb 06 10:46:17 KST 2015