REVIEW - CODING QUESTIONS Flashcards
What are tablespaces?
A tablespace in Oracle is a storage location where the actual data associated with database objects like tables and indexes is stored.
How are users created in Oracle?
CREATE USER statement.
What is the syntax for creating a user?
CREATE USER user_name IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT CREATE SESSION, CREATE TABLE TO user_name;
What is database authenticated?
Users are authenticated using a username and password that are stored and verified by the database.
What is the syntax for database authenticated?
CREATE USER db_user IDENTIFIED BY db_password;
What is externally authenticated?
Users are authenticated externally, such as through the operating system or network services.
What is the syntax for externally authenticated?
CREATE USER external_user IDENTIFIED EXTERNALLY;
What is a role in oracle?
A collection of privileges that can be granted to users or other roles, simplifying the management of user permissions.
What are the characteristics of a role?
- Roles can be granted to users or other roles.
- Roles can be enabled or disabled.
- Roles can contain both system and object privileges.
- Roles simplify privilege management by grouping multiple privileges.
- Roles can be password-protected to restrict usage.
What are some common predefined Oracle Roles?
CONNECT: Basic privileges for connecting to the database.
RESOURCE: Privileges for creating and managing schema objects.
DBA: All administrative privileges.
SELECT_CATALOG_ROLE: Allows querying data dictionary views.
EXP_FULL_DATABASE: Full database export privileges.
What are the types of privileges in Oracle?
System and object.
What are system privileges?
Allow users to perform actions on the database itself (e.g., CREATE TABLE, CREATE USER).
What are object privileges?
Allow users to perform actions on database objects (e.g., SELECT, INSERT, UPDATE on a table).
What is the difference between CUBE and ROLLUP?
CUBE: Generates subtotals for all combinations of columns specified in the GROUP BY clause. It’s useful when you need a comprehensive breakdown of all possible aggregation levels.
ROLLUP: Generates subtotals for hierarchical levels. It’s useful when you want to aggregate data progressively across a defined hierarchy.
What are the components of exception handling?
EXCEPTION: Marks the start of the exception block.
WHEN: Specifies the condition to handle specific exceptions.
RAISE: Used to explicitly raise an exception.
RAISE_APPLICATION_ERROR: Used to raise a user-defined error with a custom error code and message.
