Editing schemas

When you edit a database schema, you can update the ID and password used to log into the Oracle database. tablespaces assigned to the schema. whether the schema can be used to build applications. whether the schema can be mapped directly to a Oracle Portal user database object privileges assigned to the schema. roles to which the schema is assigned. system-level Oracle database object privileges, such as CREATE ANY TABLE. You can also drop a schema from the database while editing a schema.

To edit a database schema, you must have either:  the MANAGE ALL SCHEMAS global privilege. By default, members of the DBA group have this privilege. Manage privileges on the schema you want to edit.

To edit the options for a schema:

In the Oracle Portal Navigator, navigate to the list of schemas. The Name column on the Database Objects tab contains a list of all schemas in the database on which you have View access privileges. In the Name column, scroll down to the schema you want to edit. Note To find a schema, you can also enter the name of the schema in the Find field at the top of the page and click Go. The actions you can perform on the schema display in the Actions column next to each schema. Click Edit. The Edit a Schema page displays. In the Password field, you can enter a new password for logging into the schema. In the Confirm Password field, enter the password again. You can choose a new Default Tablespace. The default tablespace will be used to store any database objects or components created in the schema. You can choose a new Temporary Tablespace. The temporary tablespace will be used for creation of temporary storage for operations such as sorting table rows. You can choose a new Oracle Profile. A profile is used to limit the amount of system and database resources that are available to the schema. You can select the Application Schema check box to enable developers with appropriate application access privileges to create components in the schema. You can select the Use this Schema for Oracle Portal Users check box to to add the schema to the list of database schemas that Oracle Portal portal users can map to. Every Oracle Portal user must be associated with a database schema. By default, the name of this schema is is <portal>_public, where portal is the name or the schema in which Oracle Portal is installed. If you select the Use this Schema for Portal Users check box, a Portal user can be mapped instead to the schema you are currently creating. Click Apply if you are done with the current tab and want to continue editing options on other tabs. Click OK if you are finished editing all options for the schema.

To change database object privileges assigned to the schema:

Follow steps 1-5 above. Click the Grants tab. In the Object field, enter the name of the database object. Click to search for all objects on which you can grant privileges. Note You must preface the object with the name of the schema that owns it, for example my_schema.my_table. Click Add. The Grants page updates with the name of the object you just added plus checkboxes for granting or revoking privileges. In the example below, Select, Insert. Update, and Delete privileges are being granted on the MY_TABLE object to the Oracle database user AAAASCHEMA. MY_TABLE is currently stored in a schema called MYSCHEMA. Repeat steps 3 and 4 for all database object privileges you want to grant to the schema. Click Apply if you are done with the current tab and want to continue editing options on other tabs. Click OK if you are finished editing all options for the schema.

To change roles or database system-level privileges to which the schema is assigned:

Follow steps 1-5 above. Click the Roles tab. The Roles tab displays all roles and database system-level privileges for the schema. In the following example, MY_SCHEMA is a member of the ROLE1 and ROLE2 roles, and has the CREATE ANY TABLE and DELETE ANY TABLE system-level database privileges. In the Role field, enter the role you want to assign. Click to search for roles. Click Add. The Is Member of list updates with the new role. (Optional) To remove the selected schema from a role, highlight the role in the Is Member of list and click Remove. Repeat steps 3 and 4 to assign additional roles. Click Apply if you are done with the current tab and want to continue editing options on other tabs. Click OK if you are finished editing all options for the schema.

To drop the schema from the database:

Follow steps 1-3 above. On the User tab, click Delete. A message asks you to confirm that you want to delete the schema. Click Yes.

Back to top