Oracle Interview Questions and Answers
Question - 81 : - What are Roles ?
Answer - 81 : - Roles are named groups of related privileges that are granted to users or other roles.
Question - 82 : - What are the use of Roles ?
Answer - 82 : - REDUCED GRANTING OF PRIVILEGES - Rather than explicitly granting the same set of privileges to many users a database administrator can grant the privileges for a group of related users granted to a role and then grant only the role to each member of the group.
DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a group must change, only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role.
SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted to a user can be selectively enable (available for use) or disabled (not available for use). This allows specific control of a user's privileges in any given situation.
APPLICATION AWARENESS - A database application can be designed to automatically enable and disable selective roles when a user attempts to use the application.
Question - 83 : - What is Privilege Auditing ?
Answer - 83 : - Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects.
Question - 84 : - What is Object Auditing ?
Answer - 84 : - Object auditing is the auditing of accesses to specific schema objects without regard to user.
Question - 85 : - What is Auditing ?
Answer - 85 : - Monitoring of user access to aid in the investigation of database use.
Question - 86 : - How does one see the uptime for a database? (for DBA )
Answer - 86 : - Look at the following SQL query:
SELECT to_char (startup_time,'DD-MON-YYYY HH24: MI: SS') "DB Startup Time"
FROM sys.v_$instance;
Marco Bergman provided the following alternative solution:
SELECT to_char (logon_time,'Dy dd Mon HH24: MI: SS') "DB Startup Time"
FROM sys.v_$session
WHERE Sid=1 /* this is pmon */
/
Users still running on Oracle 7 can try one of the following queries:
Column STARTED format a18 head 'STARTUP TIME'
Select C.INSTANCE,
to_date (JUL.VALUE, 'J')
|| to_char (floor (SEC.VALUE/3600), '09')
|| ':'
-- || Substr (to_char (mod (SEC.VALUE/60, 60), '09'), 2, 2)
|| Substr (to_char (floor (mod (SEC.VALUE/60, 60)), '09'), 2, 2)
|| '.'
|| Substr (to_char (mod (SEC.VALUE, 60), '09'), 2, 2) STARTED
from SYS.V_$INSTANCE JUL,
SYS.V_$INSTANCE SEC,
SYS.V_$THREAD C
Where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
Select to_date (JUL.VALUE, 'J')
|| to_char (to_date (SEC.VALUE, 'SSSSS'), ' HH24:MI:SS') STARTED
from SYS.V_$INSTANCE JUL,
SYS.V_$INSTANCE SEC
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
select to_char (to_date (JUL.VALUE, 'J') + (SEC.VALUE/86400), -Return a DATE
'DD-MON-YY HH24:MI:SS') STARTED
from V$INSTANCE JUL,
V$INSTANCE SEC
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
Question - 87 : - Where are my TEMPFILES, I don't see them in V$DATAFILE or DBA_DATA_FILE?
Answer - 87 : - Tempfiles, unlike normal datafiles, are not listed in v$datafile or dba_data_files. Instead query v$tempfile or dba_temp_files:
SELECT * FROM v$tempfile;
SELECT * FROM dba_temp_files;
Question - 88 : - How do I find used/free space in a TEMPORARY tablespace?
Answer - 88 : - Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view:
SELECT tablespace_name, SUM (bytes used), SUM (bytes free)
FROM V$temp_space_header
GROUP BY tablespace_name;
Question - 89 : - What is a profile ?
Answer - 89 : - Each database user is assigned a Profile that specifies limitations on various system resources available to the user.
Question - 90 : - How will you enforce security using stored procedures?
Answer - 90 : - Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.