Use Oracle Sql Developer to Design New Schema

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          

Use Oracle Sql Developer to Design New Schema

Source: https://www.thatjeffsmith.com/archive/2018/10/data-modeler-tip-assigning-a-schema-to-your-objects/

Share this post