Users, Schemas, Quotas and Profiles

Oracle DBA Certification 14 min min read Updated: Nov 16, 2026 Beginner
Users, Schemas, Quotas and Profiles
Beginner Topic 1 of 6

Users and Schemas

A user is an account that can log in. Every user owns a schema, which is the collection of objects (tables, views, procedures) that user created. The two names are used almost interchangeably.

Create a User

sql CREATE USER report_user IDENTIFIED BY "Rep0rtPwd#1" DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 200M ON users; GRANT CREATE SESSION TO report_user;
Why GRANT? A new user can do nothing until you grant privileges. Without CREATE SESSION the user cannot even log in.

Change and Manage Accounts

sql ALTER USER report_user IDENTIFIED BY "NewPwd#2"; -- reset password ALTER USER report_user ACCOUNT LOCK; ALTER USER report_user ACCOUNT UNLOCK; ALTER USER report_user PASSWORD EXPIRE; -- force change at next login ALTER USER report_user QUOTA 1G ON users;

Drop a User

sql DROP USER report_user; -- fails if the user owns objects DROP USER report_user CASCADE; -- also drops all their objects
Careful: CASCADE permanently removes all tables and data owned by the user. Export the schema first if you may need it.

Check Users

sql SELECT username, account_status, lock_date, expiry_date, default_tablespace, profile FROM dba_users WHERE oracle_maintained = 'N' ORDER BY username;
Tip: oracle_maintained = 'N' hides Oracle's own internal accounts, so you only see the users you or your team created.

Quotas

A quota limits how much space a user can use in a tablespace. Without a quota, a user cannot create tables there.

QuotaCommand
LimitedQUOTA 500M ON users
UnlimitedQUOTA UNLIMITED ON users
NoneQUOTA 0 ON users
sql SELECT username, tablespace_name, ROUND(bytes/1024/1024) AS used_mb, max_bytes FROM dba_ts_quotas WHERE username = 'SHOP';

Profiles: Password and Resource Rules

A profile is a named set of rules for passwords and resource limits. Every user belongs to one (the default is DEFAULT).

sql CREATE PROFILE app_user_profile LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1/24 PASSWORD_LIFE_TIME 90 PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 5 SESSIONS_PER_USER 3 IDLE_TIME 30; ALTER USER report_user PROFILE app_user_profile;
SettingMeaning
FAILED_LOGIN_ATTEMPTSLock the account after this many wrong passwords
PASSWORD_LOCK_TIMEHow long it stays locked (in days, 1/24 = 1 hour)
PASSWORD_LIFE_TIMEDays until the password must change
PASSWORD_REUSE_TIME / MAXStops reuse of old passwords
SESSIONS_PER_USERMaximum concurrent sessions
IDLE_TIMEMinutes of inactivity before the session is disconnected
sql SELECT profile, resource_name, limit FROM dba_profiles WHERE profile = 'APP_USER_PROFILE';

Conclusion

Create users with a default tablespace, a temp tablespace and a quota. Grant only what they need and put them in a profile that enforces a password policy.

Quick Summary: CREATE USER, GRANT CREATE SESSION, set quota, use profiles for password rules.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators