Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Data Modeler Tip: Assigning a SCHEMA to Your Objects

3 Mins Read

Was helping someone on Stackoverflow this morning, and thought you might enjoy seeing this process as well.

PROBLEM: You’ve created a schema script, you’ve generated the DDL, and you ran it on your brand-spankin’ new database.

Except.

Except, you ran that script as SYS, and your schema scripts didn’t have a SCHEMA defined.

So you’ve corrupted your SYS schema!!! How to fix that (FLASHBACK!) is a different post.

This post is about how to assign a user/schema to your data model so you can AVOID this situation.

I’ve Already Created My Tables, Now I Need to Assign a Schema for Them

In your relational design, expand tree item for Physical Models, and create one.

Then expand that, and go to ‘Users’, and create a new one.

I’ve also gone to the System Privs page and assigned CONNECT and RESOURCE for my user so they can actually connect and own stuff.

Now, in my design, I have two tables. But I’m going to assume you have 30 or 300 or 3,000. And you don’t want to go assign an USER (which translates to a schema or user in Oracle) 30 or 300 or 3,000 times.

So we’re going to do it in one big batch of search and replace.

Click the Binoculars Button

We’re going to do an ADVANCED search on the physical model.

I’m leaving the value input field blank or NULL – because i’m looking for objects W/O an OWNER.

This will find our orphaned schema objects. If we were to generate the DDL script now, there would be no user or schema prefixes for the object names. And if you ran this in a SYS or SYSTEM connection…bad things!

Ok, so follow the buttons in the order I have them labeled:

This will replace the NULL with STACKOVERFLOW value for the USER property for every object found by the SEARCH.

  1. Toggle to the results after you’ve ran your search
  2. Hit the properties button
  3. Toggle down to User, hit the ellipsis button
  4. Select your new user, and click OK and then APPLY

We can confirm this is ‘all good’ by observing both the physical model tree list and by taking a peek into the DDL under our table.

ta-da!

Save Your Model, Generate Your DDL

Click the DDL button, accept the default.

Voila.

By the way, don’t forget to SOURCE CONTROL this. Preferably, one file per object – which is an option when generating the code.

Does this code pass your code review? Don’t trust, ALWAYS verify.

And the code, ready for you to run on any connection with a high enough PRIV level.

-- Generated by Oracle SQL Developer Data Modeler 18.2.0.179.0756
--   at:        2018-10-12 09:55:55 EDT
--   site:      Oracle Database 12cR2
--   type:      Oracle Database 12cR2
 
 
 
CREATE USER stackoverflow IDENTIFIED BY oracle
    ACCOUNT UNLOCK;
 
GRANT CONNECT,resource TO stackoverflow;
 
CREATE TABLE stackoverflow.places (
    id                    INTEGER
        GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 NOCACHE ORDER )
    NOT NULL,
    general_description   VARCHAR2(4000 BYTE),
    lat                   NUMBER(9,6),
    "LONG"                NUMBER(9,6)
)
LOGGING;
 
ALTER TABLE stackoverflow.places ADD CONSTRAINT places_pk PRIMARY KEY ( id );
 
CREATE TABLE stackoverflow.things (
    id            INTEGER
        GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 NOCACHE ORDER )
    NOT NULL,
    name          VARCHAR2(30 CHAR),
    place_id      INTEGER NOT NULL,
    description   VARCHAR2(4000 BYTE),
    VALUE         INTEGER
)
LOGGING;
 
COMMENT ON COLUMN stackoverflow.things.id IS
    'auto-increment via IDENTITY clause, not guaratneed to be sequential or w/o gaps, so deal with it ';
 
COMMENT ON COLUMN stackoverflow.things.place_id IS
    'points to places.ID';
 
COMMENT ON COLUMN stackoverflow.things.value IS
    'in whole US Dollars (USD)';
 
ALTER TABLE stackoverflow.things ADD CONSTRAINT things_pk PRIMARY KEY ( id );
 
ALTER TABLE stackoverflow.things
    ADD CONSTRAINT things_places_fk FOREIGN KEY ( place_id )
        REFERENCES stackoverflow.places ( id )
    NOT DEFERRABLE;
 
 
 
-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                             2
-- CREATE INDEX                             0
-- ALTER TABLE                              3
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE MATERIALIZED VIEW LOG             0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              1
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

9 Comments

  1. George Prikhodko Reply

    Hi,

    I am trying to make use of Schemas in a relation model.
    I have:
    – created an entity in logical model;
    – engineered it to a relational model;
    – created a schema in the relational model;
    – assigned the engineered table to the schema;
    – created a physical model for Oracle Database 12c.

    After that I would expect:
    – the schema to show up as a new user under Users;
    – the table to be assigned to the new user;
    – create table statement in generated DDL to contain the table name qualified with the user name.

    But nothing like this happens.
    Am I missing some settings or doing something wrong?

    Thank you.

  2. Rodrigo Reply

    Hi!

    The feature I’m still missing/couldn’t find yet is to automatically add a GRANT REFERENCES on the ddl export whenever the foreign key references a table from a different schema.

    Thanks!

    • thatjeffsmith Post Author

      There’s no way to automatically do it, but you can of course always manually add it…I’m assuming you know how to do that?

    • Rodrigo

      Yes, I figure how to add the grant references (AFAIK in the physical model/users/edit user/permissions), but, as my database model keeps growing, it would be more than nice to not have to worry about it every time… Something to add to that endless TODO list 🙂

      Thank you very much for the quick response!

    • thatjeffsmith Post Author

      I don’t suppose granting the schema a ALTER ANY or CREATE ANY type of privilege would be in the cards?

  3. Ed Girard Reply

    Can we please get this feature added to SQL Developer as well.

    Currently when you click on ANY code, whether it’s a FUNCTION, PROCEDURE, PACKAGE and even a TRIGGER, the schema is completely absent.

    CREATE OR REPLACE TRIGGER “ACC_CUST_AUDIT” AFTER
    INSERT OR UPDATE ON “ACCESSORIZE_CUSTOMER”
    FOR EACH ROW
    BEGIN
    …

    This is an annoying oversight when code is pulled, pushed to “change control” and when executed by a DBA, is pushed to an incorrect schema.

    At the very least, please create an OPTION for prepending of the schema to ALL CODE.

    • thatjeffsmith Post Author

      It’s not absent..the schema isn’t there in the code in the DB, you’re seeing it as it exists in the DB…if you export it with SQLDev and have the schema option enabled, you’ll get exactly what you’re asking for

Write A Comment