Oracle Concepts - Tablespaces

Oracle Tablespace Concepts

Tablespaces are the bridge between certain physical and logical components of the Oracle database. Tablespaces are where you store Oracle database objects such as tables, indexes and rollback segments.  You can think of a tablespace like a shared disk drive in Windows. You can store files on your shared drive, move files around and remove files. The same is true with tablespaces. 

A tablespace is made up of one or more database datafiles. The total amount of storage space available in a tablespace is the sum of the physical disk size of all the datafiles associated with that tablespace (less some system overhead). The datafiles are created automatically when the tablespace is defined. In most cases, all datafile space is pre-allocated; that is, the space is set aside when the datafile is created. Thus, when you create a tablespace, you define the initial size of the associated datafile. We will discuss the specifics of creating tablespaces later in this book.

Tablespaces are given names as they are created. For example, the first tablespaces that are created are named SYSTEM and SYSAUX (though SYSAUX is only created in 10g).

Tablespaces are generally named based on the objects within the tablespace.  For example you might name a tablespace PAYROLL_DATA if it?s going to store payroll related information. We will be discussing tablespaces in great detail in later chapters in this book.

Oracle Block Concept

As we discussed earlier, a block is the smallest unit of storage in Oracle. The size of a database block is fixed when the database is created, and can not be changed except by rebuilding the database from scratch. The database block size is fixed at 2K, 4K, 8K, 16K, or 32K in size. Once the base block size is defined, you can create new tablespace with alternate block sizes.

Remember that the Oracle instance also includes a RAM buffer cache which is made-up of RAM blocks which map to the data block in the physical datafiles.

Oracle Extents Concepts

An extent is an uninterrupted (or contiguous) allocation of blocks within a segment. Extents are assigned to a segment automatically by Oracle, so you will rarely deal directly with an extent, rather you will deal directly with its associated segment.

An extent must be on contiguous blocks within a single datafile, so an extent cannot span multiple Oracle datafiles. Oracle will allocate the size of the extents based on the type of tablespace. We will discuss more about extent allocation in later chapters when we discuss table and index creation. 

Oracle Segments Concepts

Segments are the storage objects within the Oracle database. A segment might be a table, an index, a cluster or any one of more than 20 object types.

The DBA creates the segments and assigns them to a specific tablespace. In most cases a single segment cannot reside in more than one tablespace. However, you can split up, or partition, a segment into different tablespaces.

After completing above introduction goto this link for tablespace creation

http://yasirdba.blogspot.com/2014/06/database-basic.html

Database basic

Create table space

create tablespace tbs_yasir

datafile 'C:\oraclexe\oradata\XE\yasir_01.dbf'

size 50m;

Create Temp table space

CREATE TEMPORARY TABLESPACE tbs_temp_yasir

TEMPFILE  'C:\oraclexe\oradata\XE\tbs_temp_yasir.dbf' 

SIZE 5M AUTOEXTEND ON;

CREATE A NEW USER IN ORACLE

CREATE USER yasir

  IDENTIFIED BY yasir123

  DEFAULT TABLESPACE tbs_yasir

  TEMPORARY TABLESPACE   tbs_temp_yasir  QUOTA 20M on tbs_yasir;

ASSIGN SYSTEM PRIVILEGES TO NEW USER IN ORACLE

GRANT create session TO yasir;

GRANT create table TO yasir;

GRANT create view TO yasir;

GRANT create any trigger TO yasir;

GRANT create any procedure TO yasir;

GRANT create sequence TO yasir;

GRANT create synonym TO yasir;

CREATE OBJECTS IN THE SCHEMA

The following is a CREATE SCHEMA statement (creating one table within the schema):

CREATE SCHEMA AUTHORIZATION yasir

    CREATE TABLE suppliers

( supplier_id number(10) not null,

  supplier_name varchar2(50) not null,

  address varchar2(50),

  city varchar2(50),

  state varchar2(25),

  zip_code varchar2(10)

);

CREATE SYNONYMS FOR OBJECTS

You may want to create synonyms so that other schemas can access the new database objects 

(ie: tables) without having to prefix the object names with the schema name.

For example, if you were another user named yasir and wanted to select from the suppliers 

table in new_schema, you would have to run the following SELECT statement (before any 

synonyms are created):

SELECT * 

FROM new_schema.suppliers;

If you then created a synonym for the suppliers table as follows:

CREATE PUBLIC SYNONYM suppliers_yasir

FOR new_schema.suppliers;

You could run the SELECT statement as follows:

SELECT * 

FROM suppliers;

No longer needing to prefix the table name with the schema name.

SOLUTION FOR PRACTICE EXERCISE #1:

CREATE TABLE SUPPLIERS

( SUPPLIER_ID NUMBER(10) NOT NULL,

  SUPPLIER_NAME VARCHAR2(50) NOT NULL,

  ADDRESS VARCHAR2(50),

  CITY VARCHAR2(50),

  STATE VARCHAR2(25),

  ZIP_CODE VARCHAR2(10)

);

PRACTICE EXERCISE #2:

Create an Oracle table called customers that stores customer ID, name, and address information.

But this time, the customer ID should be the primary key for the table.

SOLUTION FOR PRACTICE EXERCISE #2:

The Oracle CREATE TABLE statement for the customers table is:

CREATE TABLE customers

( customer_id number(10) not null,

  customer_name varchar2(50) not null,

  address varchar2(50),

  city varchar2(50),

  state varchar2(25),

  zip_code varchar2(10),

  CONSTRAINT customers_pk PRIMARY KEY (customer_id)

);

SOLUTION FOR PRACTICE EXERCISE #3:

CREATE TABLE DEPARTMENTS(
DEPARTMENT_ID NUMBER(10));

The Oracle CREATE TABLE statement for the employees table is:

CREATE TABLE employees

( employee_number number(10) not null,

  employee_name varchar2(50) not null,

  department_id number(10),

  salary number(6),

  CONSTRAINT employees_pk PRIMARY KEY (employee_number),

  CONSTRAINT fk_departments

    FOREIGN KEY (department_id)

    REFERENCES departments(department_id)

);

flash player installation in linux

https://forums.kali.org/archive/index.php/t-977.html

11g R2 – Trace Files and Alert Logs

Hi,

My name is Mark Tiger, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly.  I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks; that typically need a lot of research, before you can do them.  I will try to present the information in an easy to understand way.  My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA.  The illustrations are primarily meant for Linux, since this is a Platform; that enjoys preference from Oracle.  However they are easily adaptable for versions of UNIX/AIX and windows etc.

11g R2 – Trace Files and Alert Logs

You use the trace files and alert log to monitor your database on a regular basis.  This way you are familiar with the normal operation of the database, when you start looking at an error condition.  Being familiar with the normal operation will help you to understand an error condition better.  The home page of Enterprise manager contains ways to monitor the Oracle Database.

Each server and background process can write to an associated trace file.  When an error condition is detected by a process, it dumps information about the error condition to its associated trace file.  Some of the information is intended for the Oracle DBA, some of the information is intended for Oracle Support Services.  The trace file information is also useful for tuning applications and instances.

The alert log is a chronological log of messages and errors, and includes:

·         All internal errors (ORA-00600), block corruption errors (ORA-01578), and deadlock errors (ORA-00060)

·         Database admin operations such as: CREATE, ALTER, DROP, STARTUP, SHUTDOWN, and ARCHIVELOG statements

·         Messages and errors relating to the functions of shared server and dispatcher processes

·         Errors occurring during the automatic refresh of a materialized view

·         The values of all initialization parameters that had non-default values when the instance and database were started up and opened

Oracle uses the alert log to record these operations, as an alternative to displaying them on an operators console.  If an operation is successful, then a completed message is written to the alert log, which includes a timestamp.

In Unix/Linux you can monitor the alert log file on a monitor by using the command:

$ tail –f alert_ORACLE_SID.log

You will then see the entries in real time, as they are appended to the alert_ORACLE_SID.log file.

In windows it is a bit more tricky to get it working.  However I have had success with powershell, using  this command:

> get-content alert_ORACLE_SID.log -wait

The only thing is it will first display the entire alert log, and then get to the mode, where it spools the latest changes out.  Of course you can rename your alert log to alert.timestamp.log, when appropriate, to make it more manageable.

The alert log is maintained in test format and XML format.  You can read both forms with a text editor.  You can read the XML version with the ADRCI utility, which strips the tags from the view displayed.

You should check the alert log and trace files of an instance periodically to learn whether the background processes have encountered errors.  For example if the LGWR can’t write to a member of a log group, an error message indicating the nature of the problem is written to the alert log, and to the LGWR trace file.  Such an error indicates media failure or an I/O error, and should be attended to immediately.

You can also monitor the alert log from within SQL*Plus:

SQL> select distinct

   originating_timestamp,

   message_text

from   x$dbgalertext

where  originating_timestamp > sysdate-1

order by 1;

The Oracle database also writes the values of initialization parameters to the alert log, as well as various important performance statistics.  The alert log and all the trace files are written to the “Automatic Diagnostic Repository”.  The location of the Automatic diagnostic repository is documented in the DIAGNOSTIC_DEST initialization parameter.  The names of the trace files are operating system specific, but will usually contain the name of the process wring the file, for example LGWR, RECO  etc.  The name of the alert log would usually be something like “alert_ORACLE_SID.log”.

Controlling trace files

You can limit the maximum size of all trace files by using the initialization parameter: MAX_DUMP_FILE_SIZE.  Fortunately this is a dynamic parameter, so you can set it without a database restart.

SQL> alter system set max_dump_file_size=’20m’ scope=both;

The alert log is a different situation. The information will continue to be appended to the alert log, regardless of the size it grows to.  It is a good idea to delete it from time to time to control the size.  Better to rename it to something like alert.timestamp.log on a regular basis, so that you have a history of what happened in the past, in case you have a future problem; that requires investigating the history of error conditions of the instance.

Background processes always write information to a trace file when appropriate. With the ARCn background process you can control the amount of trace written, by setting the LOG_ARCHIVE_TRACE initialization parameter:

SQL> alter system set log_archive_trace=12 scope=both;

Trace files are always written on behalf of server processes, whenever critical conditions occur.

You can also control the amount of SQL trace information.  If you set the SQL_TRACE initialization parameter to true, then performance statistics for all SQL statements is generated and saved to the Automatic Diagnostic Repository.

You can also control the SQL_TRACE setting from a session level.  For example:

SQL> alter session set SQL_TRACE=TRUE;

You can use the DBMS_SESSION and DBMS_MONITOR packages to control SQL Tracing for a session.

The SQL Trace facility can carry a significant system overhead, which could result in a substantial performance impact.  So SQL Trace should only be enabled to collect statistics, and not for normal database operation.

If shared server is enabled, then each session using a dispatcher is routed to a shared server process; and trace information is written to the trace file only if the session has tracing enabled or if an error conditions occurs.  In this situation tracing for a specific session may involve looking through several trace files.  Oracle provides a trace utility to assist: TRCSESS, which consolidates all the trace information for a session in one place, and orders it by timestamp.

Monitoring Database operations with Server Generated Alerts

A server generated alert is typically a notification from the Oracle database server of an impending problem.  The notification may also contain suggestions to remedy the situation.  Notifications will also be provided when the error condition has been cleared.

Alerts are automatically generated for error conditions, or when data does not match expected values for metrics, for example.

·         Physical reads per second

·         User commits per second

·         SQL Service response time

Server-generated alerts can be based on threshold levels or be generated because of an error condition. Threshold-based alerts can be triggered at both warning and critical levels.  These levels can be either internal values or customer defined values. Tablespace usage has a warning generated at 85% usage and at the critical level of 97%.  These can be adjusted to requirements.

Alerts are not always based on threshold levels:

·         Snapshot too old

·         Resumable Session Suspended

·         Recovery Area Space Usage

An alert message is periodically sent to the pre-defined persistent queue ALERT_QUE owned by user SYS.  Oracle Enterprise Manager reads this queue and provides notifications about outstanding Server alerts, and may also provide suggestions for correcting the problem.  The alerts are displayed on the Enterprise Manager Database Home Page.  You can configure notifications for these alerts to be emailed or paged to selected administrators.  If an alert can’t be written to the ALERT_QUE, then a message about the alert is written to the database alert_ORACLE_SID.log. 

The data is periodically flushed to the Automatic Workload Repository, to capture or create a history of metric values.  The alert history table and ALERT_QUE are purged automatically by the system at regular intervals.

You can view and set threshold settings for the server alert metrics using the SET_THRESHOLD and GET_THRESHOLD procedures of the DBMS_SERVER_ALERT package.  You can  do this through Enterprise Manager.

For example setting the CPU threshold time:

DBMS_SERVER_ALERT.SET_THRESHOLD

(

    DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,

    DBMS_SERVER_ALERT.OPERATOR_GE,

    ‘8000’,

    DBMS_SERVER_ALERT.OPERATOR_GE,

    ‘10000’,

    1,

    2,

    ‘inst1’,

    DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,

    ‘main.regress.rdbms.dev.testcompany.com’

);

In the above example a warning message is issued if the CPU time exceeds 8000 microseconds for a user call.  A critical alert is issued if a user call exceeds 10,000 microseconds of CPU time.

·         DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, specifies the metric to be addressed

·         The observation period is set to 1 minute.  This indicates the number of minutes that the condition must deviate from the threshold value before the alert is issued.  This helps to filter out transient conditions

·         The number of consecutive occurrences is set to 2.  This specifies how many times the metric must violate the metric before the alert is generated

·         The name of the instance is set to ‘inst1’

·         DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE  specifies the object type on which the service is set.  In this case the threshold is set on: ‘main.regress.rdbms.dev.testcompany.com’

To retrieve threshold values, you can use the GET_THRESHOLD procedure.

DECLARE

   warning_operator                        BINARY_INTEGER;

  warning_value                                VARCHAR2(60);

   critical_operator                            BINARY_INTEGER;

   critical_value                                   VARCHAR2(60);

   observation_period                     BINARY_INTEGER;

   consecutive_occurrences         BINARY_INTEGER;

BEGIN

   DBMS_SERVER_ALERT.GET_THRESHOLD

(  

                DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,

warning_operator,

warning_value,

                critical_operator,

critical_value,

observation_period,

                consecutive_occurrences,

'inst1',

                DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,

‘main.regress.rdbms.dev.testcompany.com’

);

   DBMS_OUTPUT.PUT_LINE('Warning operator: ' || warning_operator);

   DBMS_OUTPUT.PUT_LINE('Warning value: ' || warning_value);

   DBMS_OUTPUT.PUT_LINE('Critical operator: ' || critical_operator);

   DBMS_OUTPUT.PUT_LINE('Critical value: ' || critical_value);

   DBMS_OUTPUT.PUT_LINE('Observation_period: ' || observation_period);

   DBMS_OUTPUT.PUT_LINE('Consecutive occurrences:' || consecutive_occurrences);

END;

/

You can also check the DBA_THRESHOLDS view:

SQL> SELECT metrics_name, warning_value, critical_value, consecutive_occurrences

  2  FROM DBA_THRESHOLDS

  3  WHERE metrics_name LIKE '%CPU Time%';

Viewing Server Generated Alerts

The easiest way to view server-generated alerts is by accessing the Database Home Page of Enterprise Manager.  There are however a number of other ways to view these server generated alerts.

If you are not using enterprise manager:

You must subscribe to the ALERT_QUE, and read the ALERT_QUE.  To create an agent and subscribe the agent to the ALERT_QUE, use the CREATE_AQ_AGENT, and ADD_SUBSCRIBER procedures of the DBMS_AQADM package.

Then you must associate a database user with the subscribing agent, because only a user associated with the subscribing agent can access queued messages in the secure ALERT_QUE.  You must also assign the ENQUEUE privilege to the user.  Use the ENABLE_DB_ACCESS and GRANT_QUEUE_PRIVILEGE procedures of the DBMS_AQADM package.

You also have the option of registering with the DBMS_AQ.REGISTER procedure to receive an asynchronous notification when an alert is enqueued to ALERT_QUE. The notification can be in the form of an email, an HTTP post, or a PL/SQL Procedure.

To read an alert message you can use the DBMS_AQ.DEQUEUE procedure or OCIAQDeq call.  After the message has been dequeued, use the DBMS_SERVER_ALERT.EXPAND_MESSAGE procedure to expand the text of the message.

The following views provide information about server-generated alerts:

View	Description
DBA_THRESHOLDS	Lists the threshold settings defined for the database
DBA_OUTSTANDING_ALERTS	Describes the outstanding alerts in the database
DBA_ALERT_HISTORY	Lists a history of alerts that have been cleared
V$ALERT_TYPES	Provides information such a group and type for each alert
V$METRICNAME	Contains the names, identifiers, and other information about the system metrics
V$METRIC	Contains system level metric values
V$METRIC_HISTORY	Contains a history of system level metric values

  

Monitoring Performance

Locks are mechanisms that prevent the destructive interaction between transactions accessing the same resources.  The resources can be user objects such as tables and rows or system objects, such as shared data structures in memory and data dictionary rows.  The Oracle database automatically manages locks when executing SQL statements.  You can lock data manually if you wish to.

A deadlock occurs when two or more users are waiting for data that is locked by each other.  Deadlocks will prevent certain transactions from continuing to work.  The Oracle Database automatically detects deadlock situations and resolves them by rolling back one of the statements in involved in the deadlock, and thereby releasing one set of conflicting row locks.

The Oracle database has been designed to avoid deadlocks, so they are not that common.  Most often deadlocks occur, when transactions explicitly overwrite the default locking mechanism of the database.  Deadlocks can be a performance issue, so Oracle provides scripts and views to enable you to monitor locks.

$ORACLE_HOME\rdbms\admin\catblock.sql

This creates the views that is used with this script:

$ORACLE_HOME\rdbms\admin\utllockt.sql

Utllockt.sql displays in a tree fashion, the sessions in the system that are waiting for locks, and the locks that are waiting for them.

Wait events are statistics that are incremented by a server process to indicate that it had to wait for an event to complete, before being able to continue processing. Sessions may be required to wait for a number of reasons.  The session could need to wait for user input, Operating system, Disk writes, locks or latches etc.

When a session is waiting for a resource it does not do any useful work.  When you get a large number of waits, it could be a problem.  Wait event data reveals various symptoms or problems that may be affecting performance, such as latch contention, buffer contention, and I/O contention.

General performance monitoring data dictionary views

View	Description
V$LOCK	List the locks currently held by Oracle Database and outstanding requests for a lock or latch
DBA_BLOCKERS	Displays a session if it is holding a lock on an object for which another session is waiting
DBA_WAITERS	Displays a session if it is waiting for a locked object
DBA_DDL_LOCKS	Lists all DDL locks held in the database and all outstanding requests for a DDL lock
DBA_DML_LOCKS	Lists all DML locks held in the database and all outstanding requests for a DML lock
DBA_LOCK	Lists all locks or latches held in the database and all outstanding requests for a lock or latch
DBA_LOCK_INTERNAL	Displays a row for each lock or latch that is being held, and one row for each outstanding requests for a lock or latch
V$LOCKED_OBJECT	Lists all locks acquired by every transaction on the system
V$SESSION_WAIT	Lists the objects or resources for which active sessions are waiting
V$SYSSTAT	Contains session statistics
V$RESOURCE_LIMIT	Provides information about current and maximum resource utilization for some system resources
V$SQL_AREA	Contains statistics about shared SQL area and contains one row for each SQL string.  Also provides statistics about SQL statements that are in memory, parsed and ready for execution
V$LATCH	Contains statistics for non parent latches and summary statistics for parent latches