Click on the banner to learn about and purchase my database training on Azure

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

Views: 1.359
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 - Backup of System Objects

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 generate the reverse, this script will display on the screen the DDL for creating all the user's jobs
  • code_profile.sql - This script will request the name of the profile to generate the DDL of creation of it
  • code_role.sql - This script will request the role name and display the role creation script on the screen, assigning all permissions
  • code_tablespace.sql - This script will request part of the name of the tablespace (s) and display the script for creating the tablespace (s) on the screen, including keeping the same number of bytes
  • user_code.sql - This script will request the name of the owner and generate the creation script already recovering the password hash, default tablespace and temporary_tablespace, in addition to saving the user's default profile, quotas on tablespaces, roles and system privileges. If you uncomment the end of the script, it generates the user's permissions as well.

Simple and effective.
Until the next post!