Database Tips and Tricks

Regular Expressions

Regular expressions (regex) are powerful tools for pattern matching in strings. In Oracle, there are several functions that utilize regular expressions:

• REGEX_SUBSTR(string, pattern, position, which_occurrence, instruction_of_match): Returns the character string matched.
• REGEX_INSTR(string, pattern, position, which_occurrence, optional, instruction_of_match): Returns the position of the match. Set optional = 1 to get the position after the match.
• REGEX_REPLACE(string, pattern, replace, position, optional, instruction_of_match): Replaces the matched pattern in the string.
• CHECK (REGEXP_LIKE(column_name, regex_expression)): Checks if a column value matches the regex pattern.
• Useful information:
  • Instructions for matching include: c for case-sensitive, i for case-insensitive, n to match newline, m for multiline, and x to ignore white spaces.
  • [ ] matches any one character, while [^ ] matches any character except the specified one.
  • [:graph:] is equivalent to [A-Za-z0-9punctuation].
  • Quantifiers: + for one or more, * for zero or more, ? for zero or one, and {n1}, {n1,}, {n1,n2} for specific repetitions.
  • (ab|bc|cd) uses | as an OR operator.
  • Use to escape special characters.
  • ^ denotes the start of a string, while $ denotes the end.
  • The first captured expression is referenced as 1.

Session Control Statements

In Oracle, session control statements are used to manage the current session:

• ALTER SESSION: Modifies the current session’s settings.
• SET ROLE: Assigns roles to the current session.

System Control Statements

System control statements in Oracle are used for system-wide changes:

• ALTER SYSTEM: Alters system parameters.

Embedded SQL Statements

Embedded SQL statements refer to any Data Definition Language (DDL), Data Manipulation Language (DML), or Transaction Control Language (TCL) integrated into a third-generation programming language (3GL).

Miscellaneous Tips

Here are some additional miscellaneous tips and commands:

• System Privileges vs. Object Privileges: System privileges are like having a license to drive any car, while object privileges are specific to driving a particular car.
• CREATE USER username IDENTIFIED BY password: Creates a new user with the specified username and password.
• ALTER USER username IDENTIFIED BY password: Modifies the password for an existing user.
• DROP USER username: Removes a user from the database.
• DROP USER username CASCADE: Drops a user and all objects owned by the user.
• CONNECT username/password: Connects to the database using the specified username and password.

Understanding Transaction Control Language in Databases

Transaction Control Language (TCL) plays a crucial role in managing transactions within a database. It allows users to control the flow of transactions by defining checkpoints, committing or rolling back changes, and creating savepoints.

Key Features of Transaction Control Language:

• Session-based: TCL operates on a per-session basis, allowing users to control transactions within a specific session without affecting others.
• SAVEPOINT: TCL enables the creation of savepoints, which define a specific point within a transaction to which you can roll back if needed.
• ROLLBACK WORK: This command is used to undo changes made within a transaction up to a specified savepoint or the beginning of the transaction.
• COMMIT WORK: When you are satisfied with the changes made within a transaction, the COMMIT WORK command is used to permanently apply those changes to the database.

By understanding and utilizing TCL effectively, database users can ensure data integrity and consistency while managing transactions efficiently.

Data Control Language

Role and System Privileges

In database management, role and system privileges are essential for controlling access and permissions effectively. While roles and system privileges can be granted together, they cannot be granted with object privileges.

Data Dictionary

The data dictionary in a database contains metadata, which is data about the data stored within the database. Some important tables within the data dictionary include:

• DBA_TABLES: Contains information about all tables in the database.
• ALL_TABLES: Lists all tables that the current user has privileges to access.
• USER_TABLES: Displays all tables owned by a specific user.

Common attributes found in these tables include TABLE_NAME, STATUS, ROW_MOVEMENT, AVG_ROW_LEN, and OWNER.

System Operations

System operations involve managing privileges within a database. Some key operations include:

• GRANT privilege TO user: Grants a specific privilege to a user.
• REVOKE privileges FROM user: Removes previously granted privileges from a user.
• Use ANY: Allows granting privileges to any user without specifying a particular user.
• WITH ADMIN OPTION: Enables a user to grant the same privilege to others.
• Revoking WITH ADMIN OPTION: Revocation of this option does not cascade to other users.
• GRANT ALL PRIVILEGES TO user: Grants all available privileges to a user.
• GRANT privileges To PUBLIC: Grants specific privileges to all users (public).

Object Privileges

Object privileges control access to specific objects in the database. Important points regarding object privileges include:

• PUBLIC SYNONYMS: These do not require access permissions as they are public, but the table they reference needs specific permission.
• GRANT user PRIVILEGES ON table TO user WITH GRANT OPTION: Allows a user to grant the same privileges to others.
• ALL PRIVILEGES: Can be abbreviated as ALL when granting privileges on objects.
• REVOKE privileges ON table FROM user: Revokes privileges on a specific table, cascading to related objects.

Column-Level Privileges

Column-level privileges involve granting permissions at the column level, usually limited to INSERT, UPDATE, and REFERENCES operations. When granting INSERT at the column level, all non-null columns in the row must be included.

User Privileges

Understanding user privileges is crucial for managing access within a database. Some key aspects include:

• USER_SYS_PRIVS (PRIVILEGE, ADMIN_OPTION): Displays privileges for the current user.
• USER_TAB_PRIVS: Shows on whom the user has granted privileges.

Types of Users

Users in a database are categorized based on their roles and permissions. Common types of users include:

• CONNECT: Typically used for generic user sessions.
• RESOURCE: Commonly assigned to application developers for creating tables.
• DBA: Designates a database administrator with extensive privileges.
• CREATE ROLE rolename: Creates a new role in the database.
• GRANT privilege TO rolename: Grants a specific privilege to a defined role.
• GRANT role TO user: Assigns a role to a user without cascading effects.
• Role and privilege assignments: These assignments are independent of each other.

Data Manipulation Language

Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) used to interact with databases like Oracle. It encompasses various operations for managing data within a database system such as querying, inserting, updating, and deleting data from tables.

SELECT Statement

• The SELECT statement is fundamental in DML, allowing users to retrieve data from one or more tables in a database. Key features include:
• Projection, selection, and joining capabilities for fetching specific data
• Options like DISTINCT to remove duplicate rows, UNIQUE to ensure unique results, and ALL to include all rows
• Pseudo columns such as ROWNUM for row numbers and ROWID for row identifiers
• Usage of ASTERISK (*) for selecting all columns from a table
• Manipulation of literals like numbers, strings, dates, and intervals within queries
• Application of operators following standard precedence rules for calculations

Views, Tables, and Synonyms

• Views: Virtual tables generated by executing a SELECT query, providing a structured representation of data from one or more tables.
• Tables: Physical storage structures in a database holding data, defined in the data dictionary and consisting of rows and columns.
• Synonyms: Alias names for database objects such as tables, views, sequences, and procedures, simplifying queries and enhancing security by concealing actual object names.
• DESCRIBE: SQL command displaying the structure of a table, including column names, data types, and constraints.

Filtering and Sorting

• Filtering data involves utilizing the WHERE clause with conditions like IN for matching values in a list, LIKE for pattern matching, IS NULL to identify null values, and NOT NULL to exclude null values
• Understanding the behavior of NULL values in comparisons and logical operations
• Sorting data using the ORDER BY clause to arrange results in ascending or descending order based on one or more columns

Data Definition Language

Data Definition Language (DDL) in databases comprises a set of commands used to define the structure and properties of data within a database. These commands play a crucial role in creating, modifying, and deleting database objects.

Key DDL Commands:

• DEFAULT: Sets a default value for a column.
• CONSTRAINT name_PK PRIMARY KEY (fieldname): Establishes a primary key constraint to uniquely identify rows in a table.
• CONSTRAINT name_FK FOREIGN KEY (fieldname): Enforces referential integrity by linking one table’s key to another table’s key.
• CONSTRAINT name_NN NOT NULL (fieldname): Ensures that a column cannot contain NULL values.
• CONSTRAINT name_CK CHECK (fieldname IN(x,y)): Adds a condition to check the data before inserting or updating it.
• CONSTRAINT name_UQ UNIQUE (fieldname): Ensures that all values in a column are unique.

When DDL commands are executed, an implicit commit occurs, committing the transaction even if the command fails or is rolled back.

Common DDL Operations:

• CREATE DATABASE: Initiates the creation of a new database.
• CREATE TABLE: Defines the structure of a new table including its columns, data types, and constraints.
• ALTER TABLE: Modifies the structure of an existing table by adding, modifying, or dropping columns.
• ALTER TABLE RENAME: Changes the name of a column in a table.
• ALTER TABLE DROP: Removes a column from a table.
• ALTER TABLE SET UNUSED COLUMN: Marks a column as unused to improve performance without deleting the column.

Constraints and Validation:

• ENABLE: Enforces constraints on data to maintain data integrity.
• DISABLE: Allows data to be entered even if it violates constraints.
• VALIDATE: Checks existing data against constraints to ensure they are met.
• NOVALIDATE: Skips the validation process for existing data against constraints.

Views and Sequences:

• CREATE VIEW: Defines a virtual table based on the result of a query for data abstraction and security purposes.
• CREATE SEQUENCE: Generates unique numeric values typically used for generating primary key values.

Indexes and Synonyms:

• CREATE INDEX: Enhances the performance of queries by speeding up data retrieval.
• CREATE UNIQUE INDEX: Ensures the uniqueness of values in a column to prevent duplicate entries.
• CREATE SYNONYM: Provides an alternative name or alias for a table, view, sequence, or other schema objects.

Additional DDL Operations:

• GRANT: Assigns specific access privileges to users or roles to manipulate database objects.
• FLASHBACK: Allows the restoration of database objects to a previous state, enabling point-in-time recovery.

Example of External Table Creation:

CREATE TABLE statments
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
    ACCESS PARAMETERS
    (RECORDS DELIMITED BY NEWLINE
    SKIP 2
    FIELDS ( fieldname fieldtype)
    )
    LOCATION 'filepath'

Introduction to Oracle Database

General

SQL (Structured Query Language) is a 4th generation programming language commonly used for managing relational databases.

Double quotation marks (“”) can be used in SQL to make column names case sensitive.

In the context of databases, a schema is like a folder that contains objects owned by a user account, such as tables, views, and indexes.

Normalization Forms

• 1NF (First Normal Form): Ensures that there are no repeating groups within a table, maintaining a 2-dimensional structure.
• 2NF (Second Normal Form): Requires that each table has a primary key that is not composite.
• 3NF (Third Normal Form): Eliminates data that does not depend on the primary key, ensuring data integrity.
• Boyce-Codd Normal Form: Addresses certain rare logical inconsistencies that may arise in a relational database.
• 4NF (Fourth Normal Form): Ensures that every multivalued dependency is dependent on a super key.
• 5NF (Fifth Normal Form): Specifies that every join dependency is a result of the candidate keys in the database.

Namespace in Oracle Database

• System level namespace includes USER, ROLES, and PUBLIC SYNONYMS.
• Schema level namespace consists of TABLE, VIEW, SEQUENCE, PRIVATE SYNONYMS, and USER DEFINED TYPES.
• INDEX and CONSTRAINT fall under the schema level namespace in Oracle databases.

Data Types in Oracle

• CHAR(n): Fixed-length character data type that retrieves values with spaces padded to the defined length.
• VARCHAR2(n): Variable-length character data type.
• NUMBER(n, m): Numeric data type where ‘n’ represents precision and ‘m’ represents scale.
• DATE: Stores date and time information with formats controlled by NLS_DATE_FORMAT and NLS_TERRITORY settings.
• TIMESTAMP(n): Stores date and time with fractional seconds precision ranging from 1 to 9 digits.
• TIMESTAMP(n) WITH TIMEZONE: Extends TIMESTAMP data type to include time zone information.
• TIMESTAMP(n) WITH LOCAL TIMEZONE: Stores date and time in the local time zone of the database.
• INTERVAL YEAR(n) TO MONTH: Represents a period of time in years and months.
• INTERVAL DAY(n1) TO SECOND(n2): Represents a period of time in days, hours, minutes, and seconds.
• BLOB: Binary Large Object data type for storing large binary data.
• CLOB: Character Large Object data type for storing large text data.
• NCLOB: National Character Large Object data type for storing Unicode text data.