Oracle DBA
Pavan Dhage
https://www.linkedin.com/in/pavandhage1/
Oracle DBA
Oracle User Management
1. User Creation in Oracle
➤ Create a New User:
CREATE USER test_user IDENTIFIED BY strongpassword
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;
➤ Verify:
SELECT username, account_status FROM dba_users WHERE username = 'TEST_USER';
2. Granting Privileges
➤ System Privileges:
GRANT CREATE SESSION, CREATE TABLE TO test_user;
➤ Object Privileges:
GRANT SELECT, INSERT ON emp TO test_user;
➤ Granting All Privileges (use cautiously):
GRANT ALL PRIVILEGES TO test_user;
3. Creating and Assigning Roles
➤ Create a Role:
CREATE ROLE analyst_role;
➤ Grant Privileges to Role:
GRANT CREATE SESSION, CREATE VIEW TO analyst_role;
➤ Assign Role to User:
GRANT analyst_role TO test_user;
Pavan Dhage
https://www.linkedin.com/in/pavandhage1/
Oracle DBA
4. Creating Profiles
Profiles manage resource limits (CPU, sessions, password rules).
➤ Create Profile:
CREATE PROFILE limited_user LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 3000
CONNECT_TIME 30
PASSWORD_LIFE_TIME 60;
➤ Assign Profile:
ALTER USER test_user PROFILE limited_user;
5. Managing Datafiles
➤ Add a Datafile to a Tablespace:
ALTER TABLESPACE users ADD DATAFILE '/u01/app/oracle/oradata/ORCL/users02.dbf' SIZE 100M
AUTOEXTEND ON;
➤ View Datafile Details:
SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb FROM dba_data_files;
6. Listener Configuration
➤ Location: $ORACLE_HOME/network/admin/listener.ora
➤ Sample listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
Pavan Dhage
https://www.linkedin.com/in/pavandhage1/
Oracle DBA
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
➤ Start/Stop Listener:
lsnrctl start
lsnrctl stop
lsnrctl status
7. TNS Configuration (tnsnames.ora)
➤ Location: $ORACLE_HOME/network/admin/tnsnames.ora
➤ Sample Entry:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
Pavan Dhage
https://www.linkedin.com/in/pavandhage1/
Oracle DBA
8. Alert Log File
The alert log file records important database events like startup, shutdown, errors, redo log
switches, etc.
➤ Location:
$ORACLE_BASE/diag/rdbms/<db_name>/<SID>/trace/alert_<SID>.log
➤ View using:
tail -f /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log
Pavan Dhage
https://www.linkedin.com/in/pavandhage1/
Oracle DBA
Interview Questions
1. How do you create a user in Oracle?
Answer:
CREATE USER john IDENTIFIED BY john123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 50M ON users;
This creates a user john with limited space in the users tablespace.
2. What are roles in Oracle and how do you create one?
Answer:
A role is a set of privileges that can be assigned to users.
CREATE ROLE app_role;
GRANT CREATE SESSION, CREATE TABLE TO app_role;
GRANT app_role TO john;
This reduces repetitive privilege assignments.
3. How do you create a profile and assign it to a user?
Answer:
CREATE PROFILE dev_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 30;
ALTER USER john PROFILE dev_profile;
Profiles control password policies and resource limits.
Pavan Dhage
https://www.linkedin.com/in/pavandhage1/
Oracle DBA
4. How do you add a datafile to an existing tablespace?
Answer:
ALTER TABLESPACE users
ADD DATAFILE '/u01/oradata/ORCL/users02.dbf' SIZE 100M AUTOEXTEND ON;
This is useful when a tablespace is full.
5. What is listener.ora and how do you configure it?
Answer:
The listener.ora file configures Oracle Net Listener for database connections.
Path: $ORACLE_HOME/network/admin/listener.ora
Sample entry:
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
Use lsnrctl start/status to manage it.
6. What is tnsnames.ora used for?
Answer:
It maps service names to network addresses for client connections.
Path: $ORACLE_HOME/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = orcl))
Pavan Dhage
https://www.linkedin.com/in/pavandhage1/
Oracle DBA
7. Where can you find the Oracle alert log file?
Answer:
It is located at:
$ORACLE_BASE/diag/rdbms/<db_name>/<sid>/trace/alert_<sid>.log
Used to troubleshoot issues like startup errors, shutdowns, redo log switches, and background
process failures.
Pavan Dhage
https://www.linkedin.com/in/pavandhage1/
