Problem solve Get help with specific problems with your technologies, process and projects.

Script to backup specific user tables

How do I build a script to backup specific user table(s) from an Oracle database? The script will be run by my customer's backup operator and at the same time how can I configure the operator privilege so that he can't read or write to the user's tables?
Backup in terms of exporting tables cannot be possible by the operator unless that person has read privilege to the tables.

However, if you want you can backup tables within the same schema as another table with the same table name plus a suffix (_BKUP) or copy the table to a different schema within the same database or better yet to a different database. In the latter case, you will need a database link to access source database and table. You can create a stored program unit (package, procedure or function) and grant execute privilege on the program unit to the operator. That way the operator will not see the tables or write to them yet can perform the backup. However, the program unit has to be owned by the backup schema or have DBA privilege to create tables on any schemas within the database.

Alternatively, this user can be granted create any table privilege, however, this is not necessary. Next, the operator should be granted execute privilege on the procedure. A public synonym for the program unit will be wise. This way the operator can execute the procedure that will perform the backup (defined as a copy of the table within the same schema or a backup schema on a different database).

Say you have a backup database (or a backup schema within the same database). Now, say, the schema owner has a database link to the source database using a username as the schema_owner. The procedure will be compiled on the backup schema or backup database using the database link to read the source table (the link is not necessary if it is the same database, but the backup schema need to have read privilege on the source table somehow.

HINT: The procedure can be created using EXECUTE IMMEDIATE on a DDL string that can be created using parameter values of the procedure.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.