Export Table Using Export Utilities
Published by : Obay Salah , November 19, 2024
The user in the database can export the tables he owns, and the database administrator can export all the tables in the database.
Also, anyone who has the EXP_FULL_DATABASE privilege can export the tables of another user, but without this privilege, he cannot export a table he does not own.
A- How does a VBS user export the EMPLOYEE table he owns?
C:\>EXP VBS/VBS FILE=D:\EXPORT\EMPLOYEE.DMP TABLES=EMPLOYEE
The above export order can be formulated as follows:
C:\>EXP VBS/VBS FILE=D:\EXPORT\EMPLOYEE.DMP TABLES=EMPLOYEE ROWS=Y TRIGGERS=Y INDEXES=Y GRANTS=Y CONSTRAINTS=Y COMPRESS=Y BUFFER=256
The wording is different, but the result is the same, so it is better not to write the options unless we want to change their default values. For example, if we want to export the table including the constraints Constraints, it is better not to write the option Constraints=Y because if you do not write it explicitly, the formula contains it implicitly. If you do not want to export the constraints with the table, you must write the option Constraints=N in the export command syntax, and so on for the rest of the options. Let us now assume that the VBS user wants to export the EMPLOYEE table that he owns, but without data.
C:\>EXP VBS/VBS FILE=D:\EXPORT\EMPLOYEE.DMP LOG=D:\EXPORT\EMPLOYEE.LOG TABLES=EMPLOYEE ROWS=N
B- How does the database administrator or the person with the EXP_FULL_DATABASE privilege issue the Employee table owned by the VBS user?
C:\>EXP SYSTEM/SYS FILE=D:\EXPORT\EMPLOYEE١.DMP LOG=D:\EXPORT\EMPLOYEE1.LOG TABLES=VBS.EMPLOYEE
Note that the table name is preceded by its owner TABLES=VBS.EMPLOYEE.
The database user does not have to export his tables, as the database administrator or someone with the EXP_FULL_DATABASE privilege can also export the user tables in the database. By the way, if Oracle finds another file with the same name in the path specified for creating the export file FILE.DMP, it will rewrite it.
Now, if the user PENT tries to export the table EMPLOYEE owned by the user VBS, knowing that the user PENTA does not have this privilege EXP_FULL_DATABSE, what do you expect the result to be?
The result is EXP-900000 no privilege to export VBS table EMPLOYEE.
You do not have this permission so the export failed.
Now let's assume that the VBS user wants to export the EMPLOYEE table that he owns but wants to export the data with a specific condition for example WHERE EMP_NO=2.
Here we use the Query option.
C:\>EXP VBS/VBS FILE=D:\EXPORT\EMPLOYEE.DMP TABLES=EMPLOYEE QUERY=’WHERE EMP_NO=2'
Comments
no comment yet!