Vinod Sebastian – B.Tech, M.Com, PGCBM, PGCPM, PGDBIO

Hi I'm a Web Architect by Profession and an Artist by nature. I love empowering People, aligning to Processes and delivering Projects.


Tag: Database

Database

  • Miscellaneous

    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.

  • Transaction Control Language

    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

    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

    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

    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

    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.

  • Import Data

    Importing Data into a Database Using SQL

    Introduction

    When working with databases, importing data is a common task that involves transferring data from external sources into a database. In this article, we will explore how to import data into a database using SQL.

    SQL Query Example

    To import data into a database table using SQL, you can use the `INSERT INTO` statement. Below is an example of an SQL query that inserts data into a table:

    INSERT INTO tablename
(fieldname, fieldname, fieldname)
VALUES
(@fieldname, @fieldname, @fieldname);

    Explanation

    – `INSERT INTO tablename`: Specifies the table where the data will be inserted.
    – `(fieldname, fieldname, fieldname)`: Lists the columns in the table where data will be inserted.
    – `VALUES`: Indicates the values to be inserted into the specified columns.
    – `(@fieldname, @fieldname, @fieldname)`: Represents the actual values that will be inserted into the respective columns.

    Important Format

    When importing data into a database, it is essential to ensure that the data is in the correct format. A common format for storing data is the Standard Data Format (.sdf), which provides a structured way to organize and store data for easy retrieval and manipulation.

    Conclusion

    Importing data into a database is a crucial aspect of database management. By using SQL queries like the `INSERT INTO` statement, you can efficiently transfer data from external sources into your database tables. Remember to adhere to the proper data format standards to ensure data integrity and consistency in your database.

  • Create Table

    Creating a Table in SQL

    Creating tables is a fundamental aspect of working with databases, especially in SQL (Structured Query Language). The CREATE TABLE statement is used to create tables.

    Syntax:

    CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

    The CREATE TABLE statement comprises the following components:

    • CREATE TABLE: Indicates the initiation of a new table.
    • table_name: Specifies the name of the table to be created.
    • column1, column2, column3: Denote the names of columns within the table.
    • datatype: Specifies the data type for each column (e.g., VARCHAR, INT, DATE).

    Example:

    Consider the following example, where a table named users is created with columns for id, name, and email:

    CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    email VARCHAR(100)
);

    After executing this SQL statement, a new table named users will be generated with the specified columns.

    It is vital to define appropriate data types and column constraints when creating tables in SQL. This practice ensures data integrity and enhances the efficiency of your database.

  • SQL

    Basic SQL Fundamentals

    Structured Query Language (SQL) is a standard programming language used to interact with databases. Here are some basic concepts:

    Character Values and Strings

    In SQL, character values such as text data or strings are enclosed in single quotes. For example:

    SELECT * FROM employees WHERE department = 'IT';

    When querying data, it is important to use single quotes to specify string values. Failure to do so may result in syntax errors.

  • Performance Issue

    Performance Issue

    Deadlock in Database Operations

    A deadlock is a situation where two or more competing actions are each waiting for the other to finish, resulting in a standstill where none can progress. To avoid deadlocks, it is crucial to ensure that operations are performed in the same order.

    Database Index and Its Impact on Performance

    A database index is a data structure that enhances the speed of data retrieval operations on a database table. However, this improvement comes at the cost of slower write operations and increased storage space.

    Indexes can be defined as unique or non-unique. A unique index serves as a constraint on the table by preventing duplicate entries, ensuring data integrity.

    Clustered vs. Non-Clustered Index

    In a Non-Clustered Index (Default), data is stored in random order, but a logical ordering is defined by the index. On the other hand, clustering reorganizes the data blocks to match the index’s order, impacting both data storage blocks and the index itself. This reorganization leads to the creation of a Clustered Index.

    Lock Types in Database Management

    • Shared Locks: Allow multiple transactions to lock the same resource and persist until all transactions complete.
    • Exclusive Locks: Held by a single transaction, preventing other transactions from locking the same resource.
    • Read Locks: Typically shared, prevent other transactions from modifying the resource.
    • Write Locks: Exclusive locks that prevent other transactions from modifying the resource. In some systems, they also block other transactions from reading the resource.