Hey guys,
Good morning!

I come to bring some more scripts that greatly speed up 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 that I will talk about in this post, access this link:
Oracle Database – System Object Backup

As I mentioned in previous posts, my scripts are all created to be used in SQL*Plus. To use it in another tool, you will have to remove some commands exclusive to SQL*Plus. If you need scripts to generate the DDL of tables, procedures and other objects, access the post Reverse Engineering Objects (DDL Backup) in Oracle Database 11g.

Let's now talk 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 creation DDL of all the user's jobs
  • code_profile.sql – This script will request the name of the profile and generate the DDL for creating it
  • code_role.sql – This script will request the name of the role and display the role creation script on the screen and assign 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 in 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!