SQL concepts - 1. Introduction to Databases

DATABASE

A database is a collection of Data (Information).  Examples of databases, which we use in our daily life, is  an Attendance Register, Telephone Directory, Muster Rule.

Database Management System(DBMS): A database management system is a collection of programs written to manage a database. That is, it acts as a interface between user and database.

RDBMS

A Database Management System based on Relational Data Model is known as Relational Database Management System (RDBMS).

The father of Relational Data Model was Dr. E.F. CODD. He developed the relational data model by taking the concept from Relational Algebra in  June-1970.

Relational Data Model is nothing but 12 Rules which are named after Codd as Codd Rules. According to Codd a package can be called as RDBMS only if it satisfies the Codd Rules.

ORACLE

Oracle is an Object-Relational Database Management System. It is the leading RDBMS vendor worldwide. Nearly half of RDBMS  worldwide market is owned by Oracle. 

ORACLE DATABASE

Every Oracle Database Contains Logical and Physical Structures. Logical Structures are tablespaces, Schema objects, extents and segments. Physical Structures are Datafiles, Redo Log Files, Control File.

A database is divided into logical storage units called tablespaces, which group related logical structures together. Each Tablespace in turn consists of one are more datafiles.

All the tables and other objects are stored in tablespace logically, but physically they are stored in the datafiles associated with the tablespace.

Every Oracle database has a set of two or more redo log files. The set of redo log files for a database is collectively known as the database's redo log. A redo log is made up of redo entries (also called redo records).

The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, the changes can be obtained from the redo log so work is never lost.

Every Oracle database has a control file. A control file contains the database name and locations of all datafiles and redo log files.

Every Oracle database also has a Parameter File. Parameter file contains the name of the Database, Memory Settings and Location of Control file.

DBA Scripts - Very Useful - 3. Tablespace Information

Here are some scripts related to Tablespace Information .

Information

TABLESPACE INFORMATION NOTES:

  Tablespace Name - Name of the tablespace

  Initial Extent - Default initial extent size

  Next Extent - Default incremental extent size

  Min Extents - Default minimum number of extents

  Max Extents - Default maximum number of extents

  PCT Increase - Default percent increase for extent size

  Status - Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)

  Contents - Type of tablespace. This column will have 'TEMPORARY' (v7.3+) for dedicated temporary tablespaces, and 'PERMANENT' for tablespaces that can store both temporary sort segments and permanent objects.

select       TABLESPACE_NAME,

               INITIAL_EXTENT,

               NEXT_EXTENT,

               MIN_EXTENTS,

               MAX_EXTENTS,

               PCT_INCREASE,

               STATUS,

               CONTENTS

from        dba_tablespaces

order        by TABLESPACE_NAME 

Coalesced Exts

WAIT STATISTIC NOTES:

  Tablespace Name - Name of tablespace

  Total Extents - Total number of free extents in tablespace

  Extents Coalesced - Total number of coalesced free extents in tablespace

  % Extents Coalesced - Percentage of coalesced free extents in tablespace

  Total Bytes - Total number of free bytes in tablespace

  Bytes Coalesced - Total number of coalesced free bytes in tablespace

  Total Blocks - Total number of free oracle blocks in tablespace

  Blocks Coalesced - Total number of coalesced free Oracle blocks in tablespace

  % Blocks Coalesced - Percentage of coalesced free Oracle blocks in tablespace

select       TABLESPACE_NAME,

               TOTAL_EXTENTS,

               EXTENTS_COALESCED,

               PERCENT_EXTENTS_COALESCED,

               TOTAL_BYTES,

               BYTES_COALESCED,

               TOTAL_BLOCKS,

               BLOCKS_COALESCED,

               PERCENT_BLOCKS_COALESCED

from        dba_free_space_coalesced

order        by TABLESPACE_NAME

Usage

TABLESPACE USAGE NOTES:

1. Tablespace Name - Name of the tablespace
2. Bytes Used - Size of the file in bytes
3. Bytes Free - Size of free space in bytes
4. Largest - Largest free space in bytes
5. Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%

select       a.TABLESPACE_NAME,

               a.BYTES bytes_used,

               b.BYTES bytes_free,

               b.largest,

               round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used

from        

               (

                               select       TABLESPACE_NAME,

                                              sum(BYTES) BYTES 

                               from        dba_data_files 

                               group       by TABLESPACE_NAME

               )

               a,

               (

                               select       TABLESPACE_NAME,

                                              sum(BYTES) BYTES ,

                                              max(BYTES) largest 

                               from        dba_free_space 

                               group       by TABLESPACE_NAME

               )

               b

where       a.TABLESPACE_NAME=b.TABLESPACE_NAME

order        by ((a.BYTES-b.BYTES)/a.BYTES) desc

Users Default (SYSTEM)

SYSTEM TABLESPACE USAGE NOTES:

  Username - Name of the user

  Created - User creation date

  Profile - Name of resource profile assigned to the user

  Default Tablespace - Default tablespace for data objects

  Temporary Tablespace - Default tablespace for temporary objects

  Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.

select       USERNAME,

               CREATED,

               PROFILE,

               DEFAULT_TABLESPACE,

               TEMPORARY_TABLESPACE

from        dba_users

order        by USERNAME

Objects in SYSTEM TS

OBJECTS IN SYSTEM TABLESPACE NOTES:

  Owner - Owner of the object

  Object Name - Name of object

  Object Type - Type of object

  Tablespace - Tablespace name

  Size - Size (bytes) of object

  Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace

select       OWNER,

               SEGMENT_NAME,

               SEGMENT_TYPE,

               TABLESPACE_NAME,

               BYTES

from        dba_segments

where       TABLESPACE_NAME = 'SYSTEM'

and          OWNER not in ('SYS','SYSTEM')

order        by OWNER, SEGMENT_NAME

Freespace/Largest Ext

FREE, LARGEST, & INITIAL NOTES:

  Tablespace - Name of the tablespace

  Total Free Space - Total amount (bytes) of freespace in the tablespace

  Largest Free Extent - Largest free extent (bytes) in the tablespace

select       TABLESPACE_NAME,

               sum(BYTES) Total_free_space,

               max(BYTES) largest_free_extent

from        dba_free_space

group       by TABLESPACE_NAME