Checklist to determine if an Oracle schema is self-contained

For a few months, I have been working on massive migrations of Oracle 10g/11g databases on Solaris to 12c on Linux. Most of these databases are highly consolidated with several schemas (no PDBs of course), and these schemas are used by different applications. These applications have supposedly nothing do to with each other. Of course, those applications being different, they follow different roadmaps. Knowing this, it was quite impossible to migrate the whole database at once, and schema-by-schema migration was relevant and comfortable for everybody.

I realized that the main challenge to address was not technical complexity, it was more about understanding the muddle between schemas that should be distinct.
Everytime I had to migrate another schema, I wondered : Is this particular schema “self-contained” ? Can I move it with Datapump from one database to another “as is”, with minimum work and error handling ?
I knew DBMS_TTS.TRANSPORT_SET_CHECK procedure could be helpful at tablespace level, but I needed information at schema level.

I still have no magic wand to separate dependant schemas, but at least I have a checklist to identify wether a schema is self-contained or not.

  • What roles are granted to this schema ? Does this schema indirectly inherits other roles ?

It is important to know, as it can lead to particular privileges granted to roles that are not easy to find.

SELECT level, grantee, LISTAGG(granted_role, ',') WITHIN GROUP (ORDER BY level) role_list
FROM dba_role_privs
START WITH grantee = 'MYSCHEMA'
CONNECT BY PRIOR granted_role = grantee
GROUP BY level, grantee;
    • Do this schema’s segments reside in only one tablespace ?
SELECT owner, tablespace_name, count(*) objects_no
FROM dba_segments 
WHERE owner = 'MYSCHEMA' 
GROUP BY owner, tablespace_name;
    • Does this schema have referential integrity constraints refering to tables it does not own ?
SELECT owner, r_owner, count(*) constr_no 
FROM dba_constraints 
WHERE owner != r_owner 
AND owner = 'MYSCHEMA' 
GROUP BY owner, r_owner;
    • Does this schema have objects (procedures, triggers, views …) based on objects it does not own ?
SELECT owner, referenced_owner, count(*) depend_no 
FROM dba_dependencies 
WHERE owner != referenced_owner 
AND owner = 'MYSCHEMA'
GROUP BY owner, referenced_owner;

You will certainly get dependent objects on like SYS, SYSTEM, or even CTXSYS if you have Oracle Text installed. It is up to you to decide whether it is important to manage or not.
Starting from Oracle 12cR1, there is the very useful column ORACLE_MAINTAINED on DBA_USERS that “denotes whether the user was created, and is maintained, by Oracle-supplied scripts” (as described in official documentation) :

SELECT username FROM dba_users WHERE oracle_maintained = 'Y';

In earlier versions, you can also look at column SCHEMA on DBA_REGISTRY. It gives you an idea of which schemas refer to components within the database :

SELECT comp_name, schema FROM dba_registry;
    • Does this schema have privileges on objects it does not own ?
SELECT grantee, owner, count(*) privs_no 
FROM dba_tab_privs 
WHERE grantee = 'MYSCHEMA' 
GROUP BY grantee, owner;
    • Does this schema have privileges on columns of tables it does not own ?
SELECT grantee, owner, count(*) privs_no 
FROM dba_col_privs 
WHERE grantee = 'MYSCHEMA' 
GROUP BY grantee, owner;

Finally, I put all those useful queries together and use the following procedure to check if a schema is self-contained :

DECLARE
   l_schema_name VARCHAR2(30) := 'MYSCHEMA' ;
   CURSOR c_roles
   IS
      SELECT level, grantee, LISTAGG(granted_role, ',') WITHIN GROUP (ORDER BY level) role_list
      FROM dba_role_privs
      START WITH grantee = l_schema_name
      CONNECT BY PRIOR granted_role = grantee
      GROUP BY level, grantee;
   CURSOR c_segments
   IS
      SELECT owner, tablespace_name, count(*) objects_no
      FROM dba_segments 
      WHERE owner = l_schema_name 
      GROUP BY owner, tablespace_name;
   CURSOR c_constraints
   IS
      SELECT owner, r_owner, count(*) constr_no 
      FROM dba_constraints 
      WHERE owner != r_owner 
      AND owner = l_schema_name 
      GROUP BY owner, r_owner;
   CURSOR c_dependencies
   IS
      SELECT owner, referenced_owner, count(*) depend_no 
      FROM dba_dependencies 
      WHERE owner != referenced_owner 
      AND owner = l_schema_name
      GROUP BY owner, referenced_owner;
   CURSOR c_obj_privileges
   IS
      SELECT grantee, owner, count(*) privs_no 
      FROM dba_tab_privs 
      WHERE grantee = l_schema_name 
      GROUP BY grantee, owner;
   CURSOR c_col_privileges
   IS
      SELECT grantee, owner, count(*) privs_no 
      FROM dba_col_privs 
      WHERE grantee = l_schema_name 
      GROUP BY grantee, owner;
BEGIN
   dbms_output.put_line('*** Roles heritage : ');
   FOR rec in c_roles
   LOOP
      dbms_output.put_line(rec.grantee||' has the following roles : '||rec.role_list);
   END LOOP; 
   dbms_output.put_line('*** Tablespace occupation : ');
   FOR rec in c_segments
   LOOP
      dbms_output.put_line(rec.owner||' has '||rec.objects_no||' segments on '||rec.tablespace_name);
   END LOOP;   
   dbms_output.put_line('*** Constraints : ');
   FOR rec in c_constraints
   LOOP
      dbms_output.put_line(rec.owner||' has '||rec.constr_no||' constraint(s) on schema '||rec.r_owner);
   END LOOP;
   dbms_output.put_line('*** Objects dependencies : ');
   FOR rec in c_dependencies
   LOOP
      dbms_output.put_line(rec.owner||' has '||rec.depend_no||' object(s) depending on schema '||rec.referenced_owner);
   END LOOP;
   dbms_output.put_line('*** Objects privileges : ');
   FOR rec in c_obj_privileges
   LOOP
      dbms_output.put_line(rec.grantee||' has '||rec.privs_no||' privilege(s) on schema '||rec.owner);
   END LOOP;   
   dbms_output.put_line('*** Columns privileges : ');
   FOR rec in c_col_privileges
   LOOP
      dbms_output.put_line(rec.grantee||' has '||rec.privs_no||' privilege(s) on '||rec.owner||' columns');
   END LOOP;
END;
/

This list is not exhaustive but I think it is a good start to evaluate how complicated a migration can be.

Advertisements

One thought on “Checklist to determine if an Oracle schema is self-contained

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s