Generating Reverse Engineering (Creation DDL) from Oracle Database Users, Tablespaces, Roles, Jobs, and Profiles

Views: 1.180
Reading Time: <1 minute

Hello guys,
Good Morning!

I come here to bring some more scripts that greatly speed some routine activities of Oracle DBAs, such as generating reverse engineering script (DDL Backup) of system objects, such as users, profiles, roles, etc.

To download the scripts I will talk about in this post, visit this link:
Oracle Database - System Object Backup

As I mentioned in previous posts, my scripts are all designed for use in SQL * Plus. To use in another tool, you will have to remove some unique SQL * Plus commands. If you need scripts to generate DDL from tables, procedures and other objects, visit the post. Generating Object Reverse Engineering (DDL Backup) in Oracle Database 11g.

Let's talk now about the scripts I made available to you:

  • code_job_owner.sql - Logged in with the owner of the jobs you want to reverse, this script will display on screen the creation DDL of all user jobs.
  • code_profile.sql - This script will request the profile name to generate its creation DDL
  • code_role.sql - This script will prompt for the role name and display the role creation script on the screen with all permissions assigned
  • code_tablespace.sql - This script will request part of the name of the tablespace (s) and display the script for creating the tablespace (s), including keeping the same number of bytes.
  • user_code.sql - This script will request the owner's name and generate the creation script already retrieving the password hash, default tablespace and temporary_tablespace, as well as saving the default user profile, quotas on tablespaces, roles and system privileges. If you uncomment the end of the script, it generates user permissions as well.

Simple and effective.
Until the next post!