• Created by Unknown User (morgan), last modified by Unknown User (fackler) on Jan 14, 2023

A GEODESC project is effectively an Oracle schema.  To create a GEODESC project a user will perform the following tasks:

  1. Create the schema account in Oracle.
  2. Clone the tables, views, sequences, indexes, etc. from a "base" project (currently the base project is a pre-existing project.  In the near future we will create a special-purpose project labeled as "base").  Important: when cloning the base project copy the DDL only, do not copy any data from the source schema)
  3. Issue the proper grants to resources in the new project.
  4. Add the newly created GEODESC project to the GEODCAT.PROJECTS table (this will cause it to show up in the "projects" dropdown for GEODESC applications.
  5. Create new role and associated privilege for the new project.


Create Oracle Schema account

  1. Using SQLDeveloper, sign into Oracle with your DBA account
  2. Right click “Other users” icon, select “Create user…”
  3. “User Name” must start with prefix “GEODP”; e.g. “GEODP123” for Expedition 123 project.
  4. Create an appropriate password for the new account.
  5. Use one of the other schema accounts as a guide to include the appropriate user settings and grants. Settings should be similar to:
    1. Granted Roles:
      1. Connect
    2. System Privileges:
      1. Advisor
      2. Alter session
      3. Create Credential
      4. Create Database Link
      5. Create Job
      6. Create Procedure
      7. Create Public Synonym
      8. Create Sequence
      9. Create Session
      10. Create Synonym
      11. Create Table
      12. Create Trigger
      13. Create Type
      14. Create View
      15. Create Any Procedure
      16. Debug Connect Session
      17. Drop Public Synonym
      18. Select Any Dictionary
      19. Select Any Table
    3. Quotas
      1. LABWARE
      2. LABWAREIDX
      3. USERS |  31457280 | K


Add the newly created schema account credentials to shipboard password safe.

Copy DDL from Existing Project to New Project

In this example we will copy DDL from existing schema owner GEODPTST360 to new, target, project schema GEODPTST123.

In SQLDeveloper:

Open two database connections:

  1. One with newly created GEODESC project account schema owner; e.g., GEODPTST123
  2. One with existing GEODESC project account; e.g., GEODPTST360

From Menu select “Tools | Database copy…”  This will open the following dialog box.


  1. Select “Source Connection” and “Destination Connection”. Source is the existing project schema from which you are copying information; Destination is the newly created schema owner account created above
  2. Select “Objects Copy” (should be default in dialog)
  3. Select “Copy DDL”; use options as shown in Figure 1 above.
  4. Uncheck “Copy Data”. We do not want the data from the source project, only the DDL
  5. Click “Next” button.



Leave all items in the “Object Types” dialog checked.

Click “Next” button.



Leave all items in “Specify Objects” at defaults (leaving selections empty will cause all objects to be copied).

Click “Next” button.


Review the “Copy Summary” page; ensure source and destination databases are correct; ensure that you are copying only DDL (no data), and review set of DDL objects to ensure that you are copying e.g., Tables, Sequences, Indexes, etc.

Click “Finish” button.


SQLBuilder will run a utility that copies all of the DDL from the source account to the destination account; i.e., it will build all the tables, constraints, indexes, sequences, etc. required for the new project.  At the end of the process SQLBuilder will display a log of all of the actions that occurred.  Review this log and look for errors or warnings.


Add required Grants to Tables in New Project Schema


For each table in the new project schema run the following GRANT SQL commands:

GRANT DELETE ON "NEW-PROJ-SCHEMA"."TABLE_NAME" TO "WRITER";

GRANT INSERT ON "NEW-PROJ-SCHEMA"."TABLE_NAME" TO "WRITER";

GRANT SELECT ON "NEW-PROJ-SCHEMA"."TABLE_NAME" TO "WRITER";

GRANT UPDATE ON "NEW-PROJ-SCHEMA"."TABLE_NAME" TO "WRITER";

GRANT SELECT ON "NEW-PROJ-SCHEMA"."TABLE_NAME" TO "GUEST";

GRANT SELECT ON "NEW-PROJ-SCHEMA"."TABLE_NAME" TO PUBLIC;


For example, for project table P_CAPTURE_FILES in schema GEODPTST123 run:

GRANT DELETE ON "GEODPTST123"."P_CAPTURE_FILES" TO "WRITER";

GRANT INSERT ON "GEODPTST123 "." P_CAPTURE_FILES " TO "WRITER";

GRANT SELECT ON "GEODPTST123"." P_CAPTURE_FILES " TO "WRITER";

GRANT UPDATE ON " GEODPTST123"." P_CAPTURE_FILES " TO "WRITER";

GRANT SELECT ON " GEODPTST123"."P_CAPTURE_FILES" TO "GUEST";

GRANT SELECT ON " GEODPTST123"." P_CAPTURE_FILES " TO PUBLIC;



At this point the new project should be ready for use by GEODESC applications.


  • No labels