ORACLE/PLSQL: AFTER DELETE TRIGGER

This Oracle tutorial explains how to create an AFTER DELETE Trigger in Oracle with syntax and examples.

DESCRIPTION

An AFTER DELETE Trigger means that Oracle will fire this trigger after the DELETE operation is executed.

SYNTAX

The syntax to create an AFTER DELETE Trigger in Oracle/PLSQL is:

CREATE [ OR REPLACE ] TRIGGER trigger_name
AFTER DELETE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Parameters or Arguments

OR REPLACE is optional. If specified, it allows you to re-create the trigger is it already exists so that you can change the trigger definition without issuing a DROP TRIGGER statement.

trigger_name is the name of the trigger to create.

AFTER DELETE indicates that the trigger will fire after the DELETE operation is executed.

table_name is the name of the table that the trigger is created on.

RESTRICTIONS

You can not create an AFTER trigger on a view.
You can not update the :NEW values.
You can not update the :OLD values.

NOTE

See also how to create AFTER INSERT, AFTER UPDATE, BEFORE DELETE, BEFORE INSERT, and BEFORE UPDATE triggers.
See also how to drop a trigger.

EXAMPLE

Let's look at an example of how to create an AFTER DELETE trigger using the CREATE TRIGGER statement.

If you had a table created as follows:

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);

We could then use the CREATE TRIGGER statement to create an AFTER DELETE trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
   ON orders
   FOR EACH ROW

DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing the DELETE on the table
   SELECT user INTO v_username
   FROM dual;
   
   -- Insert record into audit table
   INSERT INTO orders_audit
   ( order_id,
     quantity,
     cost_per_item,
     total_cost,
     delete_date,
     deleted_by)
   VALUES
   ( :old.order_id,
     :old.quantity,
     :old.cost_per_item,
     :old.total_cost,
     sysdate,
     v_username );
     
END;

ORACLE/PLSQL: DROP TRIGGER STATEMENT

This Oracle tutorial explains how to use the DROP TRIGGER statement to drop a trigger in Oracle with syntax and examples.

DESCRIPTION

Once you have created a trigger in Oracle, you might find that you need to remove it from the database. You can do this with the DROP TRIGGER statement.

SYNTAX

The syntax to a drop a trigger in Oracle in Oracle/PLSQL is:

DROP TRIGGER trigger_name;

Parameters or Arguments

trigger_name is the name of the trigger that you wish to drop.

NOTE

See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, BEFORE INSERT, and BEFORE UPDATE triggers.

EXAMPLE

Let's look at an example of how to drop a trigger in Oracle.

For example:

DROP TRIGGER orders_before_insert;

This example uses the ALTER TRIGGER statement to drop the trigger called orders_before_insert.

ORACLE/PLSQL: DISABLE A TRIGGER

This Oracle tutorial explains how to disable a trigger in Oracle with syntax and examples.

DESCRIPTION

Once you have created a Trigger in Oracle, you might find that you are required to disable the trigger. You can do this with the ALTER TRIGGER statement.

SYNTAX

The syntax for a disabling a Trigger in Oracle/PLSQL is:

ALTER TRIGGER trigger_name DISABLE;

Parameters or Arguments

trigger_name is the name of the trigger that you wish to disable.

NOTE

See also how to disable all triggers on a table.
See also how to enable a trigger on a table or enable all triggers on a table.

EXAMPLE

Let's look at an example that shows how to disable a trigger in Oracle.

For example:

ALTER TRIGGER orders_before_insert DISABLE;

This example uses the ALTER TRIGGER statement to disable the trigger called orders_before_insert.

ORACLE/PLSQL: DISABLE ALL TRIGGERS ON A TABLE

This Oracle tutorial explains how to disable all triggers on a table in Oracle with syntax and examples.

DESCRIPTION

Once you have created Triggers in Oracle, you might find that you are required to disable all of the triggers on a table.. You can do this with the ALTER TRIGGER statement.

SYNTAX

The syntax for a disabling all Triggers on a table in Oracle/PLSQL is:

ALTER TABLE table_name DISABLE ALL TRIGGERS;

Parameters or Arguments

table_name is the name of the table that all triggers should be disabled on.

NOTE

See also how to disable a trigger.
See also how to enable a trigger on a table or enable all triggers on a table.

EXAMPLE

Let's look at an example that shows how to disable all triggers on a table in Oracle.

For example:

ALTER TABLE orders DISABLE ALL TRIGGERS;

This example uses the ALTER TRIGGER statement to disable all triggers on the table called orders.

ORACLE/PLSQL: ENABLE A TRIGGER

This Oracle tutorial explains how to enable a trigger in Oracle with syntax and examples.

DESCRIPTION

You may have found that you have disabled a trigger on a table and you wish to enable the trigger again. You can do this with the ALTER TRIGGER statement.

SYNTAX

The syntax for a enabling a Trigger in Oracle/PLSQL is:

ALTER TRIGGER trigger_name ENABLE;

Parameters or Arguments

trigger_name is the name of the trigger that you wish to enable.

NOTE

See also how to enable all triggers on a table.
See also how to disable a trigger on a table or disable all triggers on a table.

EXAMPLE

Let's look at an example that shows how to enable a trigger in Oracle.

For example:

ALTER TRIGGER orders_before_insert ENABLE;

This example uses the ALTER TRIGGER statement to enable the trigger called orders_before_insert.

ORACLE/PLSQL: ENABLE ALL TRIGGERS ON A TABLE

This Oracle tutorial explains how to enable all triggers on a table in Oracle with syntax and examples.

DESCRIPTION

You may have found that you have disabled all triggers on a table and you wish to enable the triggers again. You can do this with the ALTER TRIGGER statement.

SYNTAX

The syntax to enable all triggers on a table in Oracle/PLSQL is:

ALTER TABLE table_name ENABLE ALL TRIGGERS;

Parameters or Arguments

table_name is the name of the table that all triggers should be enabled on.

NOTE

See also how to enable a trigger.
See also how to disable a trigger on a table or disable all triggers on a table.

EXAMPLE

Let's look at an example that shows how to enable all triggers on a table in Oracle.

For example:

ALTER TABLE orders ENABLE ALL TRIGGERS;

This example uses the ALTER TRIGGER statement to enable all triggers on the table called orders.

ORACLE/PLSQL: CREATE TABLESPACE STATEMENT

This Oracle tutorial explains how to use the Oracle CREATE TABLESPACE statement with syntax and examples.

DESCRIPTION

The CREATE TABLESPACE statement is used to allocate space in the Oracle database where schema objects are stored.

The CREATE TABLESPACE statement can be used to create the 3 kinds of tablespaces:

Permanent Tablespace
Temporary Tablespace
Undo Tablespace

We will take a look at all 3 kinds of tablespaces.

#1 - PERMANENT TABLESPACE

A permanent tablespace contains persistent schema objects that are stored in data files.

Syntax

The syntax for the CREATE TABLESPACE statement when creating a permanent tablespace is:

CREATE
  [ SMALLFILE | BIGFILE ]
  TABLESPACE tablespace_name
  { DATAFILE { [ 'filename' | 'ASM_filename' ]
               [ SIZE integer [ K | M | G | T | P | E ] ]
               [ REUSE ]
               [ AUTOEXTEND
                   { OFF
                   | ON [ NEXT integer [ K | M | G | T | P | E ] ]
                   [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
                   }
               ]
             | [ 'filename | ASM_filename'
             | ('filename | ASM_filename'
                 [, 'filename | ASM_filename' ] )
             ]
             [ SIZE integer [ K | M | G | T | P | E ] ]
             [ REUSE ]
             }
     { MINIMUM EXTENT integer [ K | M | G | T | P | E ]
     | BLOCKSIZE integer [ K ]
     | { LOGGING | NOLOGGING }
     | FORCE LOGGING
     | DEFAULT [ { COMPRESS | NOCOMPRESS } ]
       storage_clause
     | { ONLINE | OFFLINE }
     | EXTENT MANAGEMENT
        { LOCAL
           [ AUTOALLOCATE
           | UNIFORM
              [ SIZE integer [ K | M | G | T | P | E ] ]
           ]
        | DICTIONARY
        }
     | SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }
     | FLASHBACK { ON | OFF }
         [ MINIMUM EXTENT integer [ K | M | G | T | P | E ]
         | BLOCKSIZE integer [ K ]
         | { LOGGING | NOLOGGING }
         | FORCE LOGGING
         | DEFAULT [ { COMPRESS | NOCOMPRESS } ]
             storage_clause
         | { ONLINE | OFFLINE }
         | EXTENT MANAGEMENT
              { LOCAL
                [ AUTOALLOCATE | UNIFORM [ SIZE integer [ K | M | G | T | P | E ] ] ]
                | DICTIONARY
              }
         | SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }
         | FLASHBACK { ON | OFF }
         ]
     }

SMALLFILE or BIGFILE is optional. It determines the size of the tablespace. If you omit this option, the Oracle database will use the default tablespace type.

SMALLFILE is a tablespace that contains 1,022 data or temp files (each file can be up to 4 million blocks in size).
BIGFILE is a tablespace that contains only one data or temp file (this file can be up to 4 million blocks in size).

tablespace_name is the name of the tablespace to create.

storage_clause is:

STORAGE
   ({ INITIAL integer [ K | M | G | T | P | E ]
    | NEXT integer [ K | M | G | T | P | E ]
    | MINEXTENTS integer
    | MAXEXTENTS { integer | UNLIMITED }
    | PCTINCREASE integer
    | FREELISTS integer
    | FREELIST GROUPS integer
    | OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
    | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
    }
       [ INITIAL integer [ K | M | G | T | P | E ]
       | NEXT integer [ K | M | G | T | P | E ]
       | MINEXTENTS integer
       | MAXEXTENTS { integer | UNLIMITED }
       | PCTINCREASE integer
       | FREELISTS integer
       | FREELIST GROUPS integer
       | OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
       | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
       ]
   )

Example - PERMANENT TABLESPACE

The following is a CREATE TABLESPACE statement that creates a simple permanent tablespace:

CREATE TABLESPACE tbs_perm_01
  DATAFILE 'tbs_perm_01.dat' 
    SIZE 20M
  ONLINE;

This CREATE TABLESPACE statement creates a permanent tablespace called tbs_perm_01 that has one data file calledtbs_perm_01.dat.

The following is a CREATE TABLESPACE statement that creates a permanent tablespace that will extend when more space is required:

CREATE TABLESPACE tbs_perm_02
  DATAFILE 'tbs_perm_02.dat' 
    SIZE 10M
    REUSE
    AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

This CREATE TABLESPACE statement creates a permanent tablespace called tbs_perm_02 that has one data file calledtbs_perm_02.dat. When more space is required, 10M extents will automatically be added until 200MB is reached.

The following is a CREATE TABLESPACE statement that creates a BIGFILE permanent tablespace that will extend when more space is required:

CREATE BIGFILE TABLESPACE tbs_perm_03
  DATAFILE 'tbs_perm_03.dat'
    SIZE 10M
    AUTOEXTEND ON;

This CREATE TABLESPACE statement creates a BIGFILE permanent tablespace called tbs_perm_03 that has one data file called tbs_perm_03.dat.

#2 - TEMPORARY TABLESPACE

A temporary tablespace contains schema objects that are stored in temp files that exist during a session.

Syntax

The syntax for the CREATE TABLESPACE statement when creating a temporary tablespace is:

CREATE
  [ SMALLFILE | BIGFILE ]
  TEMPORARY TABLESPACE tablespace_name
    [ TEMPFILE { [ 'filename' | 'ASM_filename' ]
                 [ SIZE integer [ K | M | G | T | P | E ] ]
                 [ REUSE ]
                 [ AUTOEXTEND
                     { OFF
                     | ON [ NEXT integer [ K | M | G | T | P | E ] ]
                     [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
                     }
                 ]
               | [ 'filename | ASM_filename'
               | ('filename | ASM_filename'
                   [, 'filename | ASM_filename' ] )
               ]
               [ SIZE integer [ K | M | G | T | P | E ] ]
               [ REUSE ]
               }
    [ TABLESPACE GROUP { tablespace_group_name | '' } ]
    [ EXTENT MANAGEMENT
       { LOCAL
          [ AUTOALLOCATE | UNIFORM [ SIZE integer [ K | M | G | T | P | E ] ] ]
       | DICTIONARY
       } ]

SMALLFILE or BIGFILE is optional. It determines the size of the tablespace. If you omit this option, the Oracle database will use the default tablespace type.

SMALLFILE is the most common tablespace size to create. It can contain 1,022 data or temp files (each file can be up to 4 million blocks in size).
BIGFILE is a tablespace that contains only one data or temp file (this file can be up to 4 million blocks in size).

tablespace_name is the name of the tablespace to create.

Example - TEMPORARY TABLESPACE

The following is a CREATE TABLESPACE statement that creates a temporary tablespace:

CREATE TEMPORARY TABLESPACE tbs_temp_01
  TEMPFILE 'tbs_temp_01.dbf'
    SIZE 5M
    AUTOEXTEND ON;

This CREATE TABLESPACE statement creates a temporary tablespace called tbs_temp_01 that has one temp file calledtbs_temp_01.dbf.

#3 - UNDO TABLESPACE

A undo tablespace is created to manage undo data if the Oracle database is being run in automatic undo management mode.

Syntax

The syntax for the CREATE TABLESPACE statement when creating an undo tablespace is:

CREATE
  [ SMALLFILE | BIGFILE ]
  UNDO TABLESPACE tablespace_name
    [ DATAFILE { [ 'filename' | 'ASM_filename' ]
                 [ SIZE integer [ K | M | G | T | P | E ] ]
                 [ REUSE ]
                 [ AUTOEXTEND
                     { OFF
                     | ON [ NEXT integer [ K | M | G | T | P | E ] ]
                     [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
                     }
                 ]
               | [ 'filename | ASM_filename'
               | ('filename | ASM_filename'
                   [, 'filename | ASM_filename' ] )
               ]
               [ SIZE integer [ K | M | G | T | P | E ] ]
               [ REUSE ]
               }
    [ EXTENT MANAGEMENT
       { LOCAL
          [ AUTOALLOCATE | UNIFORM [ SIZE integer [ K | M | G | T | P | E ] ] ]
       | DICTIONARY
       } ]
    [ RETENTION { GUARANTEE | NOGUARANTEE } ]

SMALLFILE or BIGFILE is optional. It determines the size of the tablespace. If you omit this option, the Oracle database will use the default tablespace type.

SMALLFILE is the most common tablespace size to create. It can contain 1,022 data or temp files (each file can be up to 4 million blocks in size).
BIGFILE is a tablespace that contains only one data or temp file (this file can be up to 4 million blocks in size).

tablespace_name is the name of the tablespace to create.

Example - UNDO TABLESPACE

The following is a CREATE TABLESPACE statement that creates an undo tablespace:

CREATE UNDO TABLESPACE tbs_undo_01
  DATAFILE 'tbs_undo_01.f'
    SIZE 5M 
    AUTOEXTEND ON
  RETENTION GUARANTEE;

This CREATE TABLESPACE statement creates an undo tablespace called tbs_undo_01 that is 5MB in size and has one data file called tbs_undo_01.f.

ORACLE/PLSQL: ALTER TABLESPACE STATEMENT

This Oracle tutorial explains how to use the Oracle ALTER TABLESPACE statement with syntax and examples.

DESCRIPTION

The ALTER TABLESPACE statement is used to modify a tablespace or one of its data files or temp files. A tablespace is used to allocate space in the Oracle database where schema objects are stored.

SYNTAX

The syntax for the ALTER TABLESPACE statement in Oracle/PLSQL is:

ALTER TABLESPACE tablespace_name
  { DEFAULT
     [ { COMPRESS | NOCOMPRESS } ] storage_clause
  | MINIMUM EXTENT integer [ K | M | G | T | P | E ]
  | RESIZE integer [ K | M | G | T | P | E ]
  | COALESCE
  | RENAME TO new_tablespace_name
  | { BEGIN | END } BACKUP
  | { ADD { DATAFILE | TEMPFILE }
       [ file_specification
          [, file_specification ]
       ]
    | DROP {DATAFILE | TEMPFILE } { 'filename' | file_number }
    | RENAME DATAFILE 'filename' [, 'filename' ] TO 'filename' [, 'filename' ]
    | { DATAFILE | TEMPFILE } { ONLINE | OFFLINE }
    }
  | { logging_clause | [ NO ] FORCE LOGGING }
  | TABLESPACE GROUP { tablespace_group_name | '' }
  | { ONLINE
    | OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE ]
    }
    | READ { ONLY | WRITE }
    | { PERMANENT | TEMPORARY }
  | AUTOEXTEND
     { OFF
     | ON [ NEXT integer [ K | M | G | T | P | E ] ]
        [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
     }
  | FLASHBACK { ON | OFF }
  | RETENTION { GUARANTEE | NOGUARANTEE }
  } ;

Parameters or Arguments

tablespace_name is the name of the tablespace to remove from the Oracle database.

storage_clause is:

STORAGE
   ({ INITIAL integer [ K | M | G | T | P | E ]
    | NEXT integer [ K | M | G | T | P | E ]
    | MINEXTENTS integer
    | MAXEXTENTS { integer | UNLIMITED }
    | PCTINCREASE integer
    | FREELISTS integer
    | FREELIST GROUPS integer
    | OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
    | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
    }
       [ INITIAL integer [ K | M | G | T | P | E ]
       | NEXT integer [ K | M | G | T | P | E ]
       | MINEXTENTS integer
       | MAXEXTENTS { integer | UNLIMITED }
       | PCTINCREASE integer
       | FREELISTS integer
       | FREELIST GROUPS integer
       | OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
       | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
       ]
   )

file_specification is:

{ [ 'filename' | 'ASM_filename' ]
  [ SIZE integer [ K | M | G | T | P | E ] ]
  [ REUSE ]
  [ AUTOEXTEND
      { OFF
      | ON [ NEXT integer [ K | M | G | T | P | E ] ]
      [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
      }
  ]
| [ 'filename | ASM_filename'
| ('filename | ASM_filename'
    [, 'filename | ASM_filename' ] )
]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
}

EXAMPLE - RENAME DATAFILE

Let's look at an ALTER TABLESPACE statement that renames a datafile associated with a tablespace.

For example:

ALTER TABLESPACE tbs_perm_01 OFFLINE NORMAL;

ALTER TABLESPACE tbs_perm_01
  RENAME DATAFILE 'tbs_perm_01.dat'
  TO 'tbs_perm_01_new.dat';

ALTER TABLESPACE tbs_perm_01 ONLINE;

This ALTER TABLESPACE statement would take the tablespace offline, rename the datafile from tbl_perm_01.dat totbl_perm_01_new.dat, and then bring the tablespace back online again.

EXAMPLE - ADD DATAFILE

Let's look at an ALTER TABLESPACE statement that adds a datafile to a tablespace.

For example:

ALTER TABLESPACE tbs_perm_02
 ADD DATAFILE 'tbs_perm_02.dat'
   SIZE 20M
   AUTOEXTEND ON;

This ALTER TABLESPACE statement add the datafile called tbs_perm_02.dat to the tbs_perm_02 tablespace.

EXAMPLE - DROP DATAFILE

Let's look at an ALTER TABLESPACE statement that drops a datafile from a tablespace.

For example:

ALTER TABLESPACE tbs_perm_03
 DROP DATAFILE 'tbs_perm_03.dat';

This ALTER TABLESPACE statement drops the datafile called tbs_perm_03.dat to the tbs_perm_03 tablespace.

EXAMPLE - ADD TEMPFILE

Let's look at an ALTER TABLESPACE statement that adds a tempfile to a tablespace.

For example:

ALTER TABLESPACE tbs_temp_04
 ADD TEMPFILE 'tbs_temp_04.dat'
   SIZE 10M
   AUTOEXTEND ON;

This ALTER TABLESPACE statement add the tempfile called tbs_temp_04.dat to the tbs_temp_04 tablespace.

EXAMPLE - DROP TEMPFILE

Let's look at an ALTER TABLESPACE statement that drops a tempfile from a tablespace.

For example:

ALTER TABLESPACE tbs_temp_05
 DROP TEMPFILE 'tbs_temp_05.dat';

This ALTER TABLESPACE statement drops the tempfile called tbs_temp_05.dat to the tbs_temp_05 tablespace.

ORACLE/PLSQL: DROP TABLESPACE STATEMENT

This Oracle tutorial explains how to use the Oracle DROP TABLESPACE statement with syntax and examples.

DESCRIPTION

The DROP TABLESPACE statement is used to remove a tablespace from the Oracle database. A tablespace is used to allocate space in the Oracle database where schema objects are stored.

SYNTAX

The syntax for the DROP TABLESPACE statement is:

DROP TABLESPACE tablespace_name
  [ INCLUDING CONTENTS [ {AND DATAFILES | KEEP DATAFILES ]
    [ CASCADE CONSTRAINTS ] ] ;

Parameters or Arguments

tablespace_name is the name of the tablespace to remove from the Oracle database.

INCLUDING CONTENTS is optional. If you specify INCLUDING CONTENTS, all contents of the tablespace will be dropped. If there are objects in the tablespace, you must specify INCLUDING CONTENT or you will receive an error.

AND DATAFILES is optional. It will delete the associated operating system files. When using Oracle-managed files, you can omit the AND DATAFILES option because Oracle will automatically delete the associated operating system files.
KEEP DATAFILES is optional. It will NOT delete the associated operating system files. When using Oracle-managed files, if you want to keep the associated operating system files, you must specify the KEEP DATAFILES option.

CASCADE CONSTRAINTS is optional. If you specify CASCADE CONSTRAINTS, all referential integrity constraints will be dropped that meet the following criteria:

A referential integrity constraint from a table outside tablespace_name that refers to a primary key or unique key on a table that is inside tablespace_name.

EXAMPLE

Let's look at a simple DROP TABLESPACE statement.

For example:

DROP TABLESPACE tbs_perm_01
  INCLUDING CONTENTS
    CASCADE CONSTRAINTS;

This would drop tablespace called tbs_perm_01, delete all contents from the tbs_perm_01 tablespace, and drop all referential integrity constraints (Referential integrity constraints from a table outside tablespace_name that refers to a primary key or unique key on a table that is inside tablespace_name.)

Let's look at a another DROP TABLESPACE statement.

For example:

DROP TABLESPACE tbs_perm_02
  INCLUDING CONTENTS AND DATAFILES
    CASCADE CONSTRAINTS;

This would drop tablespace called tbs_perm_02, delete all contents from the tbs_perm_02 tablespace, remove the associated operating system files, and drop all referential integrity constraints (Referential integrity constraints from a table outsidetablespace_name that refers to a primary key or unique key on a table that is inside tablespace_name.)

Let's look at a one file DROP TABLESPACE statement.

For example:

DROP TABLESPACE tbs_perm_03
  INCLUDING CONTENTS KEEP DATAFILES;

This would drop tablespace called tbs_perm_03, delete all contents from the tbs_perm_03 tablespace, but keep the associated operating system files.

ORACLE/PLSQL: FIND DEFAULT TABLESPACES (BOTH PERMANENT AND TEMP)

This Oracle tutorial explains how to find default permanent and temporary tablespaces in Oracle with syntax and examples.

HOW TO FIND OUT DEFAULT PERMANENT TABLESPACE

To find the default permanent tablespace in Oracle, you can run the following SELECT statement:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

This will query the Oracle system tables and return the value of the default permanent tablespace.

HOW TO FIND OUT DEFAULT TEMPORARY TABLESPACE

To find the default temporary tablespace in Oracle, you can run the following query:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

This will query the Oracle system tables and return the value of the default temporary tablespace.

HOW TO FIND OUT BOTH DEFAULT TABLESPACES (PERMANENT AND TEMPORARY)

To find both the default permanent tablespace as well as the default temporary tablespace in Oracle, you can run the following command:

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME IN ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

This will query the Oracle system tables and return the both the default permanent and default temporary tablespaces.

ORACLE/PLSQL: SET DEFAULT TABLESPACES (BOTH PERMANENT AND TEMP)

This Oracle tutorial explains how to set default permanent and temporary tablespaces in Oracle with syntax and examples.

HOW TO SET DEFAULT PERMANENT TABLESPACE

First, make sure that you have created a permanent tablespace.

Next you will need to change the Oracle database to use your permanent tablespace as the default permanent tablespace.

To set the default permanent tablespace in Oracle, you can run the following ALTER DATABASE statement:

ALTER DATABASE DEFAULT TABLESPACE tbs_perm_01;

This will update the default permanent tablespace to use the tbs_perm_01 tablespace.

You can run the following query to see that the default permanent tablespace has, in fact, been changed:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

This will query the Oracle system tables and return the value of the default permanent tablespace.

HOW TO SET DEFAULT TEMPORARY TABLESPACE

First, make sure that you have created a temporary tablespace.

Next you will need to change the Oracle database to use your temporary tablespace as the default temporary tablespace.

To set the default temporary tablespace in Oracle, you can run the following ALTER DATABASE statement:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_temp_01;

This will update the default temporary tablespace to use the tbs_temp_01 tablespace.

You can run the following query to see that the default temporary tablespace has, in fact, been changed:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

This will query the Oracle system tables and return the value of the default temporary tablespace.

ORACLE/PLSQL: CREATE A SCHEMA

This Oracle tutorial explains how to create a schema in Oracle with syntax and examples.

DESCRIPTION

Creating a schema in Oracle, can at first, appear to be a little confusing. You might think that the CREATE SCHEMA statement would create your schema, but that is not the case. The CREATE SCHEMA statement is used only to create objects (ie: tables, views, etc) in your schema in a single SQL statement, but does not actually create the schema itself.

To create a schema in Oracle, you need to do the following steps:

STEP 1 - CREATE A NEW USER IN ORACLE

In essence, a schema is created in Oracle when a user is created. (Learn the syntax for the CREATE USER statement).

We can create a new user with the CREATE USER statement as follows:

CREATE USER smithj
  IDENTIFIED BY pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01;

This CREATE USER statement would create a new user called smithj in the Oracle database whose password ispwd4smithj, the default tablespace would be tbs_perm_01 with a quota of 20MB, and the temporary tablespace would betbs_temp_01.

If you don't have tablespaces yet, learn how to create default and temporary tablespaces.

STEP 2 - ASSIGN SYSTEM PRIVILEGES TO NEW USER IN ORACLE

The next step in setting up your schema is to assign "system privileges" to the new user smithj.

These "system privileges" will allow our new user to create a session in Oracle as well as create tables, views, triggers, procedures, sequences, and synonyms in the new schema. Here is an example of how we might grant those system privileges:

GRANT create session TO smithj;
GRANT create table TO smithj;
GRANT create view TO smithj;
GRANT create any trigger TO smithj;
GRANT create any procedure TO smithj;
GRANT create sequence TO smithj;
GRANT create synonym TO smithj;

These new privileges are now granted to the user called smithj.

STEP 3 - CREATE OBJECTS IN THE SCHEMA

Now that the schema (called smithj) has been created with the necessary privileges, you can create objects in the schema. This can be done one of 2 ways:

Executing individual SQL statements to create each object. This would be done through CREATE TABLE statementsand CREATE VIEW statements.
Executing a CREATE SCHEMA statement to create multiple objects in a single SQL statement.

STEP 4 - GRANT OBJECT PRIVILEGES

After you have created your objects in the schema, you will need to grant privileges so that other schemas/users can access your database objects (ie: tables).

STEP 5 - CREATE SYNONYMS FOR OBJECTS

As a last step, 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 smithj 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
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.

ORACLE/PLSQL: CREATE SCHEMA STATEMENT

This Oracle tutorial explains how to use the Oracle CREATE SCHEMA statement with syntax and examples.

DESCRIPTION

The CREATE SCHEMA statement does NOT actually create a schema in Oracle. (Find out how to create a schema in Oracle.)

The CREATE SCHEMA statement is used only to create objects (ie: tables, views) in your schema in a single SQL statement, instead of having to issue individual CREATE TABLE statements and CREATE VIEW statements.

If an error occurs creating any of the objects in the CREATE SCHEMA statement, the Oracle database will roll back all create statements (e: tables and view) in the CREATE SCHEMA statement.

SYNTAX

The syntax for the CREATE SCHEMA statement is:

CREATE SCHEMA AUTHORIZATION schema_name
    [create_table_statement]
    [create_view_statement]
    [grant_statement];

Parameters or Arguments

schema_name is the name of the schema (which is the same as your Oracle username that you are logged in as).

create_table_statement is optional. It is a valid CREATE TABLE statement.

create_view_statement is optional. It is a valid CREATE VIEW statement.

grant_statement is optional. It is a valid GRANT statement.

EXAMPLE

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

CREATE SCHEMA AUTHORIZATION smithj
     CREATE TABLE products
        ( product_id number(10) not null,
          product_name varchar2(50) not null,
          category varchar2(50),
          CONSTRAINT products_pk PRIMARY KEY (product_id)
         );

This create schema statement creates a schema called smithj. In this new schema, it creates one table called products.

You can also create more than one table using the CREATE SCHEMA statement as follows:

CREATE SCHEMA AUTHORIZATION smithj
     CREATE TABLE products
        ( product_id number(10) not null,
          product_name varchar2(50) not null,
          category varchar2(50),
          CONSTRAINT products_pk PRIMARY KEY (product_id)
         )
     CREATE TABLE suppliers
        ( supplier_id number(10) not null,
          supplier_name varchar2(50) not null,
          city varchar2(25),
          CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
         );

This CREATE SCHEMA statement would create two tables - products and suppliers. If an error occurs creating either of these tables, neither table will be created.

Alternatively, you could have created these 2 tables using 2 individual CREATE TABLE statements as follows (while logged in smithj):

CREATE TABLE products
   ( product_id number(10) not null,
     product_name varchar2(50) not null,
     category varchar2(50),
     CONSTRAINT products_pk PRIMARY KEY (product_id)
   );

CREATE TABLE suppliers
   ( supplier_id number(10) not null,
     supplier_name varchar2(50) not null,
     city varchar2(25),
     CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
   );

ORACLE/PLSQL: COMMIT STATEMENT

This Oracle tutorial explains how to use the Oracle COMMIT statement with syntax and examples.

DESCRIPTION

In Oracle, the COMMIT statement commits all changes for the current transaction. Once a commit is issued, other users will be able to see your changes.

SYNTAX

The syntax for the COMMIT statement in Oracle/PLSQL is:

COMMIT [ WORK ] [ COMMENT clause ] [ WRITE clause ] [ FORCE clause ];

Parameters or Arguments

WORK is optional. It was added by Oracle to be SQL-compliant. Issuing the COMMIT with or without the WORK parameter will result in the same outcome.

COMMENT clause is optional. It is used to specify a comment to be associated with the current transaction. The comment that can be up to 255 bytes of text enclosed in single quotes. It is stored in the system view called DBA_2PC_PENDING along with the transaction ID if there is a problem.

WRITE clause is optional. It is used to specify the priority that the redo information for the committed transaction is to be written to the redo log. With this clause, you have two parameters to specify:

WAIT or NOWAIT (WAIT is the default if omitted)

WAIT - means that the commit returns to the client only after the redo information is persistent in the redo log.
NOWAIT - means that the commit returns to the client right away regardless of the status of the redo log.

IMMEDIATE or BATCH (IMMEDIATE is the default if omitted)

IMMEDIATE - forces a disk I/O causing the log writer to write the redo information to the redo log.
BATCH - forces a "group commit" and buffers the redo log to be written with other transactions.

FORCE clause is optional. It is used to force the commit of a transaction that may be corrupt or in doubt. With this clause, you can specify the FORCE in 3 ways:

FORCE 'string', [integer] or FORCE CORRUPT_XID 'string' or FORCE CORRUPT_XID_ALL

FORCE 'string', [integer] - allows you to commit a corrupt or in doubt transaction in a distributed database system by specifying the transaction ID in single quotes as string. You can find the transaction ID in the system view called DBA_2PC_PENDING. You can specify integer to assign the transaction a system change number if you do not wish to commit the transaction using the current system change number.
FORCE CORRUPT_XID 'string' - allows you to commit a corrupt or in doubt transaction by specifying the transaction ID in single quotes as string. You can find the transaction ID in the system view called V$CORRUPT_XID_LIST.
FORCE CORRUPT_XID_ALL - allows you to commit all corrupted transactions.

NOTE

You must have DBA privileges to access the system views - DBA_2PC_PENDING and V$CORRUPT_XID_LIST.
You must have DBA privileges to specify certain features of the COMMIT statement.

EXAMPLE

Let's look at an example that shows how to issue a commit in Oracle using the COMMIT statement.

For example:

COMMIT;

This COMMIT example would perform the same as the following:

COMMIT WORK WRITE WAIT IMMEDIATE;

In this example, the WORK keyword is implied and the omission of the WRITE clause would default to WRITE WAIT IMMEDIATE so the first 2 COMMIT statements are equivalent.

Comment

Let's look at an example of a COMMIT that shows how to use the COMMENT clause:

For example, you can write the COMMIT with a comment in two ways:

COMMIT COMMENT 'This is the comment for the transaction';

COMMIT WORK COMMENT 'This is the comment for the transaction';

Since the WORK keyword is always implied, both of these COMMIT examples are equivalent. The COMMIT would store the comment enclosed in quotes along with the transaction ID in the DBA_2PC_PENDING system view, if the transaction was in error or in doubt.

Force

Finally, look at an example of a COMMIT that shows how to use the FORCE clause.

For example, you can write the COMMIT of an in-doubt transaction in two ways:

COMMIT FORCE '22.14.67';

COMMIT WORK FORCE '22.14.67';

Since the WORK keyword is always implied, both of these COMMIT examples would force the commit of the corrupted or in doubt transaction identified by the transaction ID '22.14.67'.

ORACLE/PLSQL: ROLLBACK STATEMENT

This Oracle tutorial explains how to use the Oracle ROLLBACK statement with syntax and examples.

DESCRIPTION

In Oracle, the ROLLBACK statement is used to undo the work performed by the current transaction or a transaction that is in doubt.

SYNTAX

The syntax for the ROLLBACK statement is:

ROLLBACK [ WORK ] [ TO [SAVEPOINT] savepoint_name  | FORCE 'string' ];

Parameters or Arguments

WORK is optional. It was added by Oracle to be SQL-compliant. Issuing the ROLLBACK with or without the WORK parameter will result in the same outcome.

TO SAVEPOINT savepoint_name is optional. The ROLLBACK statement undoes all changes for the current session up to the savepoint specified by savepoint_name. If this clause is omitted, then all changes are undone.

FORCE 'string' is optional. It is used to force the rollback of a transaction that may be corrupt or in doubt. With this clause, you specify the transaction ID in single quotes as string. You can find the transaction ID in the system view called DBA_2PC_PENDING.

NOTE

You must have DBA privileges to access the system views - DBA_2PC_PENDING and V$CORRUPT_XID_LIST.
You can not rollback a transaction that is in doubt to a savepoint.

EXAMPLE

Let's look at an example that shows how to issue a rollback in Oracle using the ROLLBACK statement.

For example:

ROLLBACK;

This ROLLBACK example would perform the same as the following:

ROLLBACK WORK;

In this example, the WORK keyword is implied so the first 2 ROLLBACK statements are equivalent. These examples would rollback the current transaction.

Savepoint

Let's look at an example of a ROLLBACK that shows how to use the rollback to a specific savepoint.

For example, you can write the ROLLBACK to a savepoint in two ways:

ROLLBACK TO SAVEPOINT savepoint1;

ROLLBACK WORK TO SAVEPOINT savepoint1;

Since the WORK keyword is always implied, both of these ROLLBACK examples would rollback the current transaction to the savepoint called savepoint1.

Force

Finally, look at an example of a ROLLBACK that shows how to force the rollback of a transaction that is in doubt.

For example, you can write the ROLLBACK of an in-doubt transaction in two ways:

ROLLBACK FORCE '22.14.67';

ROLLBACK WORK FORCE '22.14.67';

Since the WORK keyword is always implied, both of these ROLLBACK examples would force the rollback of the corrupted or in doubt transaction identified by the transaction ID '22.14.67'.

ORACLE/PLSQL: SET TRANSACTION STATEMENT

This Oracle tutorial explains how to use the Oracle SET TRANSACTION statement with syntax and examples.

DESCRIPTION

In Oracle, the SET TRANSACTION statement is used to set a transaction as read-only, set a transaction as read/write, set a transaction's isolation level, assign a name to a transaction, or assign a rollback segment to a transaction.

SYNTAX

The syntax for the SET TRANSACTION statement in Oracle/PLSQL is:

SET TRANSACTION [ READ ONLY | READ WRITE ]
                [ ISOLATION LEVEL [ SERIALIZE | READ COMMITED ]
                [ USE ROLLBACK SEGMENT 'segment_name' ]
                [ NAME 'transaction_name' ];

Parameters or Arguments

READ ONLY is optional. If specified, it sets the transaction as a read-only transaction.

READ WRITE is optional. If specified, it sets the transaction as a read/write transaction.

ISOLATION LEVEL is optional. If specified, it has two options:

ISOLATION LEVEL SERIALIZE - If a transaction attempts to update a resource that has been updated by another transaction and uncommitted, the transaction will fail.
ISOLATION LEVEL READ COMMITTED - If a transaction requires row locks held by another transaction, the transaction will wait until the row locks are released.

USE ROLLBACK SEGMENT is optional. If specified, it assigns the transaction to a rollback segment identified by 'segment_name' which is the segment name enclosed in quotes.

NAME assigns a name to the transaction identified by 'transaction_name' which is enclosed in quotes.

EXAMPLE

Let's look at an example that shows how to use the SET TRANSACTION statement in Oracle.

READ ONLY

First, let's look at how to set a transaction as read-only using the SET TRANSACTION statement.

For example:

SET TRANSACTION READ ONLY NAME 'RO_example';

This SET TRANSACTION example would set the current transaction to read-only and assign it the name of 'RO_example'.

READ WRITE

Next, let's look at how to set a transaction as read/write using the SET TRANSACTION statement.

For example:

SET TRANSACTION READ WRITE NAME 'RW_example';

ORACLE/PLSQL: LOCK TABLE STATEMENT

This Oracle tutorial explains how to use the Oracle LOCK TABLE statement with syntax and examples.

DESCRIPTION

The LOCK TABLE statement is used to lock tables, table partitions, or table subpartitions.

SYNTAX

The syntax for the LOCK TABLE statement is:

LOCK TABLE tables IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];

Parameters or Arguments

tables is a comma-delimited list of tables.

lock_mode is one of the following values:

lock_mode	Explanation
ROW SHARE	Allows concurrent access to the table, but users are prevented from locking the entire table for exclusive access.
ROW EXCLUSIVE	Allows concurrent access to the table, but users are prevented from locking the entire table with exclusive access and locking the table in share mode.
SHARE UPDATE	Allows concurrent access to the table, but users are prevented from locking the entire table for exclusive access.
SHARE	Allows concurrent queries but users are prevented from updating the locked table.
SHARE ROW EXCLUSIVE	Users can view records in table, but are prevented from updating the table or from locking the table in SHARE mode.
EXCLUSIVE	Allows queries on the locked table, but no other activities.

WAIT specifies that the database will wait (up to a certain number of seconds as specified by integer) to acquire a DML lock.

NOWAIT specifies that the database should not wait for a lock to be released.

EXAMPLE

Let's look at an example of how to use the LOCK TABLE statement in Oracle.

For example:

LOCK TABLE suppliers IN SHARE MODE NOWAIT;

This example would lock the suppliers table in SHARE MODE and not wait for a lock to be released.

ORACLE/PLSQL: ORACLE SYSTEM TABLES

Below is an alphabetical listing of the Oracle system tables that are commonly used.

System Table	Description
ALL_ARGUMENTS	Arguments in object accessible to the user
ALL_CATALOG	All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS	Comments on columns of accessible tables and views
ALL_CONSTRAINTS	Constraint definitions on accessible tables
ALL_CONS_COLUMNS	Information about accessible columns in constraint definitions
ALL_DB_LINKS	Database links accessible to the user
ALL_ERRORS	Current errors on stored objects that user is allowed to create
ALL_INDEXES	Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS	COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS	Description of LOBs contained in tables accessible to the user
ALL_OBJECTS	Objects accessible to the user
ALL_OBJECT_TABLES	Description of all object tables accessible to the user
ALL_SEQUENCES	Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS	Snapshots the user can access
ALL_SOURCE	Current source on stored objects that user is allowed to create
ALL_SYNONYMS	All synonyms accessible to the user
ALL_TABLES	Description of relational tables accessible to the user
ALL_TAB_COLUMNS	Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS	Columns of user's tables, views and clusters
ALL_TAB_COMMENTS	Comments on tables and views accessible to the user
ALL_TRIGGERS	Triggers accessible to the current user
ALL_TRIGGER_COLS	Column usage in user's triggers or in triggers on user's tables
ALL_TYPES	Description of types accessible to the user
ALL_UPDATABLE_COLUMNS	Description of all updatable columns
ALL_USERS	Information about all users of the database
ALL_VIEWS	Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL	Database compatible parameter set via init.ora
DBA_DB_LINKS	All database links in the database
DBA_ERRORS	Current errors on all stored objects in the database
DBA_OBJECTS	All objects in the database
DBA_ROLES	All Roles which exist in the database
DBA_ROLE_PRIVS	Roles granted to users and roles
DBA_SOURCE	Source of all stored objects in the database
DBA_TABLESPACES	Description of all tablespaces
DBA_TAB_PRIVS	All grants on objects in the database
DBA_TRIGGERS	All triggers in the database
DBA_TS_QUOTAS	Tablespace quotas for all users
DBA_USERS	Information about all users of the database
DBA_VIEWS	Description of all views in the database
DICTIONARY	Description of data dictionary tables and views
DICT_COLUMNS	Description of columns in data dictionary tables and views
GLOBAL_NAME	global database name
NLS_DATABASE_PARAMETERS	Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS	NLS parameters of the instance
NLS_SESSION_PARAMETERS	NLS parameters of the user session
PRODUCT_COMPONENT_VERSION	version and status information for component products
ROLE_TAB_PRIVS	Table privileges granted to roles
SESSION_PRIVS	Privileges which the user currently has set
SESSION_ROLES	Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP	Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES	Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP	Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names

ORACLE/PLSQL: || OPERATOR

This Oracle tutorial explains how to use the Oracle/PLSQL || concatenate operator with syntax and examples.

DESCRIPTION

The Oracle/PLSQL || operator allows you to concatenate 2 or more strings together.

SYNTAX

The syntax for the Oracle/PLSQL || operator is:

string1 || string2 || string_n

Parameters or Arguments

string1 is the first string to concatenate.

string2 is the second string to concatenate.

string_n is the nth string to concatenate.

APPLIES TO

The || operator can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle || operator examples and explore how to use the || operator in Oracle/PLSQL.

For example:

'Tech on' || ' the Net'
Result: 'Tech on the Net'

'a' || 'b' || 'c' || 'd'
Result: 'abcd'

ORACLE/PLSQL: CONCAT FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL CONCAT function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CONCAT function allows you to concatenate two strings together.

SYNTAX

The syntax for the Oracle/PLSQL CONCAT function is:

CONCAT( string1, string2 )

Parameters or Arguments

string1 is the first string to concatenate.

string2 is the second string to concatenate.

NOTE

See also the || operator.

APPLIES TO

The CONCAT function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle CONCAT function examples and explore how to use the CONCAT function in Oracle/PLSQL.

For example:

CONCAT('Tech on', ' the Net')
Result: 'Tech on the Net'

CONCAT('a', 'b')
Result: 'ab'

FREQUENTLY ASKED QUESTIONS

Question: How can you use the CONCAT function to concatenate more than 2 strings together?

Answer: Since the CONCAT function will only let you concatenate 2 strings, you will need to nest multiple CONCAT functions to concatenate more than 2 strings together.

For example, to concatenate 3 strings, you could nest the CONCAT function as follows:

CONCAT( CONCAT( string1, string2 ), string3 )

Or you could nest the CONCAT function as follows, if you wanted to concatenate 4 strings:

CONCAT( CONCAT( CONCAT( string1, string2 ), string3 ), string4 )

Here is an example provided by Ruth that demonstrates how to nest multiple CONCAT functions to concatenate 6 strings:

CONCAT( CONCAT( CONCAT( CONCAT( CONCAT(
 'I like ', t.type_desc_column), ' cake with '), t.icing_desc_column),' and a '),
   t.fruit_desc_column)

The CONCAT function is one method to concatenate strings in Oracle. An alternative to using the CONCAT function would be to use the || operator to catenatenate multiple strings, as follows:

'I like ' || t.type_desc_column || ' cake with ' || t.icing_desc_column || ' and a '
  || t.fruit_desc_column

ORACLE/PLSQL: LENGTH FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LENGTH function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL LENGTH function returns the length of the specified string.

SYNTAX

The syntax for the Oracle/PLSQL LENGTH function is:

LENGTH( string1 )

Parameters or Arguments

string1 is the string to return the length for.

NOTE

If string1 is NULL, then the LENGTH function will return NULL.

APPLIES TO

The LENGTH function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle LENGTH function examples and explore how to use the LENGTH function in Oracle/PLSQL.

For example:

LENGTH(NULL)
Result: NULL

LENGTH('')
Result: NULL

LENGTH(' ')
Result: 1

LENGTH('Tech on the Net')
Result: 15

LENGTH('Tech on the Net ')
Result: 16

ORACLE/PLSQL: LOWER FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LOWER function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

SYNTAX

The syntax for the Oracle/PLSQL LOWER function is:

LOWER( string1 )

Parameters or Arguments

string1 is the string to convert to lowercase.

NOTE

See also the UPPER function.

APPLIES TO

The LOWER function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle LOWER function examples and explore how to use the LOWER function in Oracle/PLSQL.

For example:

LOWER('Tech on the Net');
Result: 'tech on the net'

LOWER('GEORGE BURNS 123   ');
Result: 'george burns 123

Discover Millions of Products your daily Need

Amazon::::Discover Millions of Products your daily Need

Amway Products in Hyderabad. All India Distributors (Certified Dietitian / Nutrition Advisor )

Amway Products in Kakinada. All India Distributors ( Certified Dietitian / Nutrition Advisor)

Monday, December 1, 2014

Oracle Tutorial 7

ORACLE/PLSQL: AFTER DELETE TRIGGER

DESCRIPTION

SYNTAX

Parameters or Arguments

RESTRICTIONS

NOTE

EXAMPLE

ORACLE/PLSQL: DROP TRIGGER STATEMENT

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE

ORACLE/PLSQL: DISABLE A TRIGGER

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE

ORACLE/PLSQL: DISABLE ALL TRIGGERS ON A TABLE

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE

ORACLE/PLSQL: ENABLE A TRIGGER

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE

ORACLE/PLSQL: ENABLE ALL TRIGGERS ON A TABLE

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE

ORACLE/PLSQL: CREATE TABLESPACE STATEMENT

DESCRIPTION

#1 - PERMANENT TABLESPACE

Syntax

Example - PERMANENT TABLESPACE

#2 - TEMPORARY TABLESPACE

Syntax

Example - TEMPORARY TABLESPACE

#3 - UNDO TABLESPACE

Syntax

Example - UNDO TABLESPACE

ORACLE/PLSQL: ALTER TABLESPACE STATEMENT

DESCRIPTION

SYNTAX

Parameters or Arguments

EXAMPLE - RENAME DATAFILE

EXAMPLE - ADD DATAFILE

EXAMPLE - DROP DATAFILE

EXAMPLE - ADD TEMPFILE

EXAMPLE - DROP TEMPFILE

ORACLE/PLSQL: DROP TABLESPACE STATEMENT

DESCRIPTION

SYNTAX

Parameters or Arguments

EXAMPLE

ORACLE/PLSQL: FIND DEFAULT TABLESPACES (BOTH PERMANENT AND TEMP)

HOW TO FIND OUT DEFAULT PERMANENT TABLESPACE

HOW TO FIND OUT DEFAULT TEMPORARY TABLESPACE

HOW TO FIND OUT BOTH DEFAULT TABLESPACES (PERMANENT AND TEMPORARY)

ORACLE/PLSQL: SET DEFAULT TABLESPACES (BOTH PERMANENT AND TEMP)

HOW TO SET DEFAULT PERMANENT TABLESPACE

HOW TO SET DEFAULT TEMPORARY TABLESPACE

ORACLE/PLSQL: CREATE A SCHEMA

DESCRIPTION

STEP 1 - CREATE A NEW USER IN ORACLE

STEP 2 - ASSIGN SYSTEM PRIVILEGES TO NEW USER IN ORACLE

STEP 3 - CREATE OBJECTS IN THE SCHEMA