Managing Schema Objects and Users with Oracle Tools
Managing Schema Objects and Users with Oracle Tools
This domain tests using Oracle tools, especially SQLPlus and SQL Developer, to create and inspect schema objects and user accounts. Know SQLPlus commands versus SQL statements and the data dictionary views that report object and user information.
Core rules
- CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE users QUOTA 100M ON users; then GRANT CREATE SESSION, CREATE TABLE TO username.
- A user needs CREATE SESSION to connect, quota on a tablespace to store objects, and object privileges to access other schemas’ objects.
- SQL*Plus commands such as DESCRIBE, SPOOL, SET, COLUMN, and @script are not SQL and usually do not need a semicolon. SQL statements need a semicolon or slash.
- Data dictionary views: USER_TABLES/USER_OBJECTS for your schema, ALL_TABLES/ALL_OBJECTS for accessible objects, DBA_TABLES/DBA_USERS for administrative views.
- DROP USER username CASCADE removes the user and owned objects; without CASCADE it fails if the user owns objects.
- SQL Developer provides the same operations through a GUI; exam questions may still use SQL*Plus syntax.
Worked example
CREATE USER app_user IDENTIFIED BY S3cure_pw DEFAULT TABLESPACE users QUOTA 100M ON users; GRANT CREATE SESSION, CREATE TABLE TO app_user; CONNECT app_user/S3cure_pw DESCRIBE app_user.employees; SELECT table_name FROM user_tables; The user can now connect and create tables. The DESCRIBE command is a SQL*Plus command, not a SQL statement.
Traps
- Terminating SQL*Plus commands with semicolons or SQL statements without them.
- Forgetting quota, so CREATE TABLE fails.
- Dropping a user without CASCADE when objects exist.
- Treating DDL as rollbackable; it commits.