Managing UNDO data in Oracle

PROBLEM:
What are the basics of Oracle undo data?

SOLUTION:

1. A.C.I.D Standard

• Atomicity
• Consistency
• Isolation
• Durability

2. DML & DDL - Behind the scenes

• Executing a Rollback
• Executing a Commit
• DDL and Transaction Control
• The So-Called “Auto-Commit”
• Executing SQL statements
• Executing an Update Statement
• Executing Insert and Delete Statements

3. Consistency TYPES
4. Managing UNDO Data

• Undo Parameters
• Undo Retention Guarantee (10g) 
• Sizing your undo tablespace

5. Flashback query
6. Oracle Isolation Levels

--------------------------------------------------------------------------
-- ACID (aka ACID test)
--------------------------------------------------------------------------

-- In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee database transactions are processed reliably.

-- Atomicity
-- The principle of atomicity states that all parts of a transaction must complete, or none of them.
-- For example, if your business analysts have said that every time you change an employee’s salary you must also change his grade, then the “atomic” transaction will consist of two updates. The database must guarantee that both go through, or neither.

-- Consistency
-- The principle of consistency states that the results of a query must be consistent with the state of the database at the time the query started.
-- For uncommited data - as long as user-A changing data in Table-X without applying Commit, all other users will see the "old values".
-- For commited data -  Imagine a simple query that averages the value of a column of a table. If the table is large, it will take many minutes to pass through the table.
-- If other users are updating the column and commit while the query is in progress, should the query include the new or the old values? Should it include rows that were inserted or deleted after the query started?

-- Isolation
-- The principle of isolation states that an incomplete (that is, uncommitted) transaction must be invisible to the rest of the world.

-- Durability
-- The principle of durability states that once a transaction completes with a COMMIT, it must be impossible for the database to lose it.

--------------------------------------------------------------------------
-- DML & DDL - Behind the scenes
--------------------------------------------------------------------------

-- Executing a Rollback
-- If the session that initiated the transaction fails (perhaps the PC running the user process reboots, or the network link goes down), then the PMON will detect that there is a problem, and roll back the transaction.
-- If the server crashes, then on startup SMON will detect the problem and initiate a rollback. A manual rollback requires the user to issue the ROLLBACK command. But however the rollback is initiated, the mechanism is identical.
-- Oracle’s implementation of the ROLLBACK command is to use undo data to construct and execute another statement that will reverse the effect of the first statement.

-------------------------
-- Executing a Commit
-------------------------

-- When you say COMMIT, all that happens physically is that LGWR flushes the log buffer to disk. DBWn does absolutely nothing. This is one of the most important performance features of the Oracle database.

--------------------------------
-- DDL and Transaction Control
--------------------------------

-- The COMMIT and ROLLBACK statements apply only to DML. You cannot roll back a DDL statement: once executed, it is immediately durable

--------------------------------
-- The So-Called “Auto-Commit”
--------------------------------
-- Quite simply, there is no such thing as an automatic commit.
-- When you execute a DDL statement for instance, a perfectly normal COMMIT is included in the source code that implements the DDL command.
-- When you exit from your user process - If you are using SQL*Plus on a Windows terminal (never  mind what operating system the database server is running) and you issue a DML statement followed by an “exit,” your transaction will be committed.
-- This is because a COMMIT statement is built into the SQL*Plus “exit” command. When you click in the top-right corner of the SQL*Plus window? The window will close, and if you log in again, you will see that the transaction has been rolled back.
-- This is because the programmers who wrote SQL*Plus for Microsoft Windows included a ROLLBACK statement in the code that is executed when you close the window.

-----------------------------
-- Executing SQL statements
-----------------------------

-- Executing a SELECT Statement
-- 1. The server process executing the statement will first check whether the blocks containing the data required are already in memory, in the database buffer cache.
-- 2. If they are, then execution can proceed immediately. If they are not, the server must locate them on disk and copy them into the database buffer cache.
-- 3. Once the data blocks required for the query are in the database buffer cache, any further processing (such as sorting or aggregation) is carried out in the PGA of the session.
-- 4. When the execution is complete, the result set is returned to the user process.

----------------------------------
-- Executing an Update Statement
----------------------------------

-- 1. The first step in executing DML is the same as executing SELECT: the required blocks must be found in memory or copied into memory from disk. The only change is that an empty block of an undo segment is needed too
-- 2. Locks must be placed on any rows that will be affected by the operation
-- 3. Then the redo is generated: the server process writes to the log buffer the changes that are going to be applied to the data blocks.
-- 4. Having generated the redo, the update is carried out in the database buffer cache:
--    A. The block of table data is updated with the new version of the changed column,
--    B. The old version of the changed column is written to the block of an undo segment.
-- 5. From this point until the update is committed, all queries from other sessions addressing the changed row will be redirected to the undo data. Only the session that is doing the update will see the actual current version of the row in the table block.

-------------------------------------------
-- Executing Insert and Delete Statements
-------------------------------------------

-- Conceptually, INSERT and DELETE are managed in the same fashion as an UPDATE.
-- Redo generation is exactly the same: all changes to be made to data are first written out to the log buffer.
-- The difference is in the amount of undo generated.
-- INSERT - When a row is inserted, the only undo generated consists of writing out the new rowid to the undo block. This is because to roll back an INSERT, the only information Oracle requires is the rowid,
-- DELETE - the whole row is written to the undo block, so that the deletion can be rolled back if need be by inserting the complete row back into the table.

--------------------------------------------------------------------------
-- Managing UNDO Data
--------------------------------------------------------------------------

-- Undo Parameters
SHOW PARAMETER undo

-- UNDO_MANAGEMENT
-- Manual - meaning that Oracle will not use undo segments at all. This is for backward compatibility, and if you use this, you will have to do a vast amount of work creating and tuning rollback segments. Don’t do it.
-- Auto - to enable automatic undo management through the use of undo segments. In this case, DBAs relinquish undo segment management to Oracle. This parameter is static.The other parameters are dynamic.

-- UNDO RETENTION
-- UNDO_RETENTION parameter specifies the amount of time in seconds that Oracle *attempts* to keep undo data available.
-- If, for example, your longest running query is thirty minutes, you would set this parameter to 1800. Oracle will then attempt to keep all undo data for at least 1800 seconds, and your query should therefore never fail with ORA-1555.
-- If, however, you do not set this parameter, or if you set it to zero, Oracle will still keep data for as long as it can anyway.
-- The algorithm controlling which expired undo data is overwritten first will always choose to overwrite the oldest bit of data; Therefore, UNDO_RETENTION is always at the maximum allowed by the size of the tablespace

-- UNDO_TABLESPACE
UNDO_TABLESPACE This parameter nominates a tablespace, which must have been created as an undo tablespace, as the active undo tablespace.

-- See Undo tablespaces:
SELECT tablespace_name, contents, retention
FROM dba_tablespaces;

-- Change retention
ALTER SYSTEM SET undo_retention = 800 ;

-- Undo Retention Guarantee (10g)
-- With the 10g release, there is an option to guarantee undo retention. This means that undo data will never be overwritten until the time specified by the UNDO_RETENTION parameters has passed.
-- Retention guarantee is enabled at the tablespace level via the Retention Guarantee clause. This attribute can be specified at undo tablespace creation time, or an undo tablespace can be altered later to enable it.
-- Once you activate an undo tablespace for which a retention guarantee has been specified, all queries will complete successfully, provided they finish within the undo retention time. You -- -- will never have “snapshot too old” errors again. The downside is that transactions may fail for lack of undo space because Oracle cannot overwrite committed undo data until the retention time has passed.

SELECT tablespace_name, contents, retention
FROM dba_tablespaces;

ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

SELECT tablespace_name, contents, retention
FROM dba_tablespaces;

-- Via OEM:
-- Server => (Database Configuration) => Automatic Undo Management
-- Server => (Database Configuration) => Initialization Parameters

-------------------------------------
-- Sizing your undo tablespace
-------------------------------------

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));

-- or via OEM ...

-------------------------------------
-- Undo segment management
-------------------------------------

-- active segments in the Undo tablespace

set lines 200
col program for a15
col username for a10
col osuser for a15
col sid for 999
col sr# for 999
col segment_name for a25
col tablespace_name for a15
col status for a15

SELECT segment_id, segment_name, B.status, xacts, tablespace_name
FROM DBA_ROLLBACK_SEGS, V$ROLLSTAT B
WHERE segment_id=usn;

-- for every new transaction a new undo segment will be available

update hr.employees set salary = salary + 1 ;

update hr.departments set department_name = department_name || 'xxx';

update hr.regions set region_name = region_name || 'zzz' ;

rollback

--------------------------------------------------------------------
-- Useful queries for UNDO usage
--------------------------------------------------------------------

-- Identify transaction and session:

-- In one session:
Conn hr/hr
UPDATE employees SET salary=salary;

-- In another session:
conn / as sysdba

-- See all active Undo segments:

SET LINES 120 PAGES 120
col program for a15
col username for a10
col osuser for a15
col sid for 999
col sr# for 999
col segment_name for a25
col tablespace_name for a15
col status for a15

SELECT segment_id, segment_name, tablespace_name, B.status, xacts "Active Transactions"
FROM DBA_ROLLBACK_SEGS, V$ROLLSTAT B
WHERE segment_id=usn and xacts > 0;

-- Which sessions use a specific undo segment:
SELECT username, program, osuser, sid, serial#
FROM v$session S, v$transaction T
WHERE S.taddr = T.addr ;

-- Which sessions use active undo segments:
-- If needed can kill the problematic session:
ALTER SYSTEM KILL SESSION 'sid, sr#';

--------------------------------------------------------------------------
-- DEMO - Flashback query
--------------------------------------------------------------------------

SELECT last_name, salary
FROM hr.employees;

-- Update and commit (User forgot to put a where condition, and also commited his changes...)
UPDATE hr.employees
SET salary = 1;

commit;

-- Data is changed and cannot be rolled back...
SELECT last_name, salary
FROM hr.employees;

-- Flashback Query - Select from the table as it was at a specific point in the past:
-- In this example - go back one hour:

SELECT last_name, salary
FROM hr.employees AS OF TIMESTAMP sysdate - 1/24;

-- Fix the table with a correlated Update:
UPDATE hr.employees e
SET salary = (SELECT salary from hr.employees AS OF TIMESTAMP sysdate - 1/24
              WHERE employee_id = e.employee_id);

------------------------------------------------------
-- Oracle Isolation Levels
------------------------------------------------------
-- Read committed - This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.
-- Serializable - Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements.
-- Read-only - Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements

-- Serializable Example:

-- User A
----------

-- Default behaviour for each new transaction
 ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE; 

-- or
-- Specific behaviour for the next new transaction
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;   

 conn hr/hr

 SELECT last_name , salary
 FROM employees
 WHERE rownum < 11 ;

-- User B
----------

 conn hr/hr

 SELECT last_name , salary
 FROM employees
 WHERE rownum < 11 ;

 UPDATE employees SET salary = 1 ;

 COMMIT ;

-- USER A
----------

 SELECT last_name , salary
 FROM employees
 WHERE rownum < 11 ;

-- Fix the table
 UPDATE hr.employees e
 SET salary = (SELECT salary from hr.employees AS OF TIMESTAMP sysdate - 10/1440
              WHERE employee_id = e.employee_id);

----------------------------------

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET TRANSACTION READ ONLY;

ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;

ALTER SESSION SET ISOLATION_LEVEL=READ COMMITTED;

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Managing Oracle database instance - the basics

PROBLEM:
What is the basics of managing Oracle database instance?

SOLUTION:

-- Data Files
SELECT name FROM v$datafile;

-- Control Files
SELECT name FROM v$controlfile;

-- Redo Log Files
SELECT member FROM v$logfile;

--------------------------
-- View a parameter value
--------------------------

show parameter undo

show parameter retention

show parameter mttr

desc v$parameter

SELECT name, value
FROM v$parameter
WHERE name LIKE '%mttr%';

show parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS

---------------- 
-- Change values
----------------

ALTER SYSTEM SET fast_start_mttr_target=300;

SELECT name, value
FROM v$parameter
WHERE name LIKE '%mttr%';

show parameter mttr

ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS = 10 ;

ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS = 10 SCOPE = SPFILE;

SELECT  name, ISSYS_MODIFIABLE
FROM v$parameter
WHERE NAME IN ('fast_start_mttr_target', 'sec_max_failed_login_attempts')

--------------------------------------------------- 
-- Difference between V$parameter and v$spparameter
---------------------------------------------------

-- * V$PARAMETER
--   It displays the information about initialization parameters that
--   are currently in effect for the session.
-- * V$SPPARAMETER
--   It displays the information about contents of the server parameter file.
--   If a server parameter file was not used to start the instance,
--   then ISSPECIFIED column contains FALSE value.

SELECT name, value FROM v$spparameter
WHERE name LIKE '%mttr%';

SELECT name, value FROM v$parameter
WHERE name LIKE '%mttr%';

alter system set fast_start_mttr_target = 100 scope = spfile ;

SELECT name, value FROM v$spparameter
WHERE name LIKE '%mttr%';

SELECT name, value FROM v$parameter
WHERE name LIKE '%mttr%';

-- View only used parameters from the spfile :

select count(*)
from v$spparameter
where ISSPECIFIED <> 'FALSE'

--------------------------- 
-- CREATE PFILE FROM SPFILE
---------------------------

-- Default value : ARC%S_%R.%T

show parameter log_archive_format

ALTER SYSTEM SET log_archive_format = 'RAM%K' ;

ALTER SYSTEM SET log_archive_format = 'RAM%K' SCOPE = SPFILE ;

SHUTDOWN IMMEDIATE

STARTUP

CREATE PFILE FROM SPFILE ;

-- change parameter value in the pfile

CREATE SPFILE FROM PFILE ;

STARTUP

show parameter log_archive_format

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

How to set Oracle SQL*PLUS character set

PROBLEM:
When opening your SQL*PLUS you see unrecognized characters, something like this:

SOLUTION:
To change SQL*PLUS language to english:
1. Close SQL*PLUS
2. Search "NLS_LANG" in the registry
3. Change the value to AMERICAN_AMERICA.WE8MSWIN1252
4. Open again SQL*PLUS to see changes

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Show connected user in Oracle SQL*PLUS command prompt

PROBLEM:
You want to see your connected user name in SQL*PLUS command prompt

SOLUTION:
You need to edit your glogin.sql file located in the following path:

oracle_home\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql

Add to the original file contents the following lines:

set serveroutput on
prompt hello!
set lines 120 pages 120
set sqlprompt '_user> '

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Useful SQL Joins diagram..

What is Oracle ASM - Automatic Storage Management?

PROBLEM:
What is Oracle Automatic Storage Management (ASM)?

1. ASM Advantages
2. ASM Basic Concepts
• Disk Group (DG)
• Allocation Unit (AU)
• ASM Instance
• ASM Templates 
• ASM Striping
• Disk Group Dynamic Rebalancing
• ASM Mirroring & Failure Groups
3. ASM Instance Management
4. ASM initialization parameters
5. ASM Shutdown / Startup
6. ASM Instance Architecture
7. Common ASM V$ Views
8. ASM Fast Mirror Resync
9. Bad block repair (11g)
10. ASM Intallation Guidelines
11. DEMO!

SOLUTION:

-------------------------------------
-- Oracle Automatic Storage Management (ASM)
-------------------------------------
-- * An Oracle file system, built specifically for database related files.
-- * Can be used for single or multiple instance architecture (RAC).
--     (Eliminates the need for third party Clustered File System)
-- * Enables storage solutions such as Striping & Mirroring (for performance & Survivability)
-- * Stripes and Mirrors at file level (as opposed to volume level)
--     - Files are striped more efficiently.
--     - Mirroring can be enabled for specific files only, at different levels.
-- * Each DB can have both ASM and regular files. (Can also migrate some files to ASM)
-- * ASM can store all Oracle file types. (Not just Data Files).

-------------------------------------
-- ASM Advantages
-------------------------------------
-- 1. it is aware of the nature of Oracle database files. This means it can make
--    more intelligent decisions about how to manage the files than a third-party product.
-- 2. ASM can handle files individually, whereas all other LVMs work at the
--    volume level: they are not aware of the files within the volume. So with a third-party
--    LVM, you have to specify RAID attributes per volume.
--    ASM can specify the attributes per file, so you can for instance have three-way
--    mirroring for your SYSTEM tablespace datafiles but no mirroring at all for your
--    temporary tablespaces’ tempfiles, all within the same logical volume.

-------------------------------------
-- ASM Basic Concepts
-------------------------------------

-----------------------
-- * Disk Group (DG)
-----------------------
--   * A group of disks which are managed as a single logical unit:
--     * If I need to add more space to a DG, I'll add another disk to it.
--     * Although we may be constantly adding ASM Files and Disks to a growing system, the number of Disk Groups to manage remains constant.
--   * ASM files are assigned to a Disk Group, instead of to a specific disk.
--   * All data will be striped and mirrored between the disks of this DG.
--   * DG >-< DB:
--     * Each DG can contain files from multiple DBs.
--     * Each DB can store his files on different DGs.
--   * Each DG is self describing, and contains all information about his Disks and Files.

CREATE DISKGROUP DG3
    '/devices/diskb3' NAME diskb3,
    '/devices/diskb4' NAME diskb4

-----------------------
-- * Allocation Unit (AU)
-----------------------
--   *Each datafile is striped to Allocation Units (AU).
--   * AUs are spread evenly across the disks of the Disk Group.
--   * AUs are rebalanced automatically across the disks whenever disks are added or removed from the DG.
--   * AU size:
--     * 10g: 1MB
--     * 11g: Determined at DG creation. (1MB, 2, 4, 8, 16, 32, 64MB)

CREATE DISKGROUP DG3
    '/devices/diskb3' NAME diskb3,
    '/devices/diskb4' NAME diskb4
ATTRIBUTE 'au_size'='4M';

-----------------------
-- * ASM Instance
-----------------------
--   * ASM metadata is managed by a single ASM instance, which can serve many DBs and Instances.
--   * The instance mounts his DGs instead of a specific database.
--   * Created and configured via the DBCA. ("Configure ASM")
--   * The instances, however, work directly with the ASM files.

----------------------------------
-- Creating a Disk Group
-- (Performed in the ASM instance)
----------------------------------

conn /@ASM AS SYSASM

-- See current Disk Groups:
SELECT GROUP_NUMBER, NAME, TOTAL_MB
FROM v$asm_diskgroup;

col path for a50
-- See available disks:
SELECT GROUP_NUMBER "DG#", PATH
FROM v$asm_disk
ORDER BY path;

-- Join:
SELECT GROUP_NUMBER, dg.name, SUM(d.TOTAL_MB) AS "Size", COUNT(*) AS "Disks"
FROM v$asm_diskgroup dg JOIN v$asm_disk d
USING (GROUP_NUMBER)
GROUP BY GROUP_NUMBER, dg.name ;


-- Create a new disk group DG2:
CREATE DISKGROUP DG2
DISK
'\\.\ORCLDISK_LABEL_G' NAME DG2_0,
'\\.\ORCLDISK_LABEL_H' NAME DG2_1 ;


-- See available disks:
SELECT GROUP_NUMBER "DG#", PATH
FROM v$asm_disk
ORDER BY path;

-- DROP DISKGROUP DG2;

-------------------------------------
-- Create a tablespace with ASM file
-- (Performed in the DB instance)
-------------------------------------
conn sys/oracle@orcl as sysdba

CREATE TABLESPACE ASM_TBS
  DATAFILE '+DG2/asm_tbs01.dbf' SIZE 100M;

SELECT name FROM v$datafile;

-- DROP TABLESPACE asm_tbs INCLUDING CONTENTS AND DATAFILES;


-- * Stamp candidate disks / change name and creating disk groups can be done by using ASMCA
--   $oracle_grid_home\BIN\asmca.bat
-- * for stamping and name changing :
--   SHUTDOWN ASM => stamp/change names => STARTUP ASM

-------------------------------------
-- ASM Templates
-------------------------------------

-- * A template is a collection of file attributes that are applied to the ASM files.
--   It is associated with a Disk Group.
-- * Whenever a file is created the file attributes specific to the template are applied to 
--   the files.
-- * For eg., if we want to create a users tablespace that contains data file we use the
--   DATAFILE template.
-- * Default Template: When you create a disk group, Oracle creates system default template
--   for the disk group.

Select * From V$ASM_TEMPLATE

-------------------------------------
-- ASM Striping
-------------------------------------
-- * ASM stripes the file to AUs, and devides them between the DG disks.
-- * Two types of striping:
--      1. Coarse-based striping:
--          - Each stripe size is one AU.
--          - Classic striping. Suitable for most file types.
--          - Used for load balancing.
--      2. Fine-Grained striping
--            - Each stripe size is 128KB. (Wider distribution of data)
--            - Used for decreasing IO Latency (Waiting for IO).
--            - Suitable for small IO operations such as Redo Log Files.

/*
ALTER DISKGROUP DG2
      ATTRIBUTE 'template.tname.stripe' = 'COARSE',
            'template.tname.redundancy' = 'MIRROR';

ALTER DISKGROUP dg2 MODIFY TEMPLATE ONLINELOG
ATTRIBUTES (FINE);

ALTER DISKGROUP dg2 MODIFY TEMPLATE DATAFILE
ATTRIBUTES (COARSE);

Select * From V$ASM_TEMPLATE
WHERE name = ('DATAFILE', 'ONLINELOG')
AND group_number = 2

*/

-------------------------------------
-- Disk Group Dynamic Rebalancing
-------------------------------------
-- * ASM performs automatic rebalancing online whenever we add / remove disks from a DG.
-- * ASM_POWER_LIMIT:
--   - Controls IO overhead during rebalancing.
--   - Values are between 1 and 11. (Minimum overhead (1) .. Minumum time (11))
--   - The higher the limit, the faster rebalancing will complete.
--   - Lower values will take longer, but consume fewer processing and I/O resources.

show parameter ASM_POWER_LIMIT

-------------------------------------
-- ASM Mirroring & Failure Groups
-------------------------------------
-- * ASM can protect our files from media failures (disk / disk-controller crash)
--
-- * Mirroring:
--   - Applied at Extent (AU) level (Not at file or disk level).
--   - When an AU is allocated to a file on a specific disk,
--       a mirrored AU is also allocated on another disk in the SAME Disk Group.
--   - Therefore, if a disk is lost, we can restore its contents
--      from other disks on its Disk Group.

-- * Failure Group:
--   - A failure group is a subset of the disks in a disk group,
--     which could fail at the same time because they share hardware.
--     (For example, disks that share the same disk controller)
--   - By default, each ASM disk belongs to his own Failure Group (FG).
--   - However, we can group a few disks in a DG to a single Failure Group.
--   - When an AU is allocated on a FG disk, a mirrored AU is also allocated
--      on ANOTHER Failure Group in the SAME Disk Group.
--   - Therefore, if a Failure Group is lost, we can restore its contents
--      from other disks on its Disk Group.

-- *   types or mirroring:
--     1. External Redundancy - No mirroring or FGs. (When using an external tool for --        protection).
--     2. Normal Redundancy   - Two-way mirroring.   (Two copies)
--     3. High Redundancy     - Triple-mirroring.    (Three copies)

DROP DISKGROUP DG2 INCLUDING CONTENTS;

-- A protected disk group:
CREATE DISKGROUP DG2
  NORMAL REDUNDANCY 
  DISK
     '//./ORCLDISKDATA3' NAME DG2_0,
     '//./ORCLDISKDATA4' NAME DG2_1,
     '//./ORCLDISKDATA5' NAME DG2_2,
     '//./ORCLDISKDATA6';

col name for a10
col failgroup for a11
col path for a20
col redundancy for a10

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk;

DROP DISKGROUP DG2 INCLUDING CONTENTS;

-- A Disk Group with 2 Failure Groups (one for each controller):
CREATE DISKGROUP DG2
  NORMAL REDUNDANCY 
  FAILGROUP controller1 DISK
     '//./ORCLDISKDATA3' NAME DG2_0,
     '//./ORCLDISKDATA4' NAME DG2_1
  FAILGROUP controller2 DISK
     '//./ORCLDISKDATA5' NAME DG2_2,
     '//./ORCLDISKDATA6' NAME DG2_3;


col name for a10
col failgroup for a11
col path for a20
col redundancy for a10

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk;

-------------------------------------
-- ASM Instance Management
-------------------------------------

-------------------------------------
-- ASM initialization parameters
-------------------------------------
-- * Important ASM related Parameters:
--    - INSTANCE_TYPE  : should be set to ASM. This is the only parameter that MUST be --      defined.
--    - ASM_DISKGROUPS : List of disk groups to be mounted at startup.
--                        (Or during ALTER DISKGROUP ALL MOUNT)
--    - ASM_POWER_LIMIT: The speed for a rebalance operation (1-11. default is 1).
-- * Additional parameters can be set...

-------------------------------------
-- ASM Shutdown / Startup
-------------------------------------

-------------
-- STARTUP
-------------
-- * The options for the STARTUP command are:
--   - NOMOUNT - Starts the ASM instance without mounting any disk groups.
--   - MOUNT   - Starts the ASM instance and mounts the disk groups specified
--               by the ASM_DISKGROUPS parameter.
--   - FORCE   - Performs a SHUTDOWN ABORT before restarting the ASM instance.
--   - OPEN    - Not a valid option.
--   - RESTRICT- DB instances cannot use the DGs. (Used for maintenance)

-- * If the ASM_DISKGROUPS parameter is empty, no DGs are mounted.
--    (Can be mounted manually using ALTER DISKGROUP...MOUNT)

-------------
-- SHUTDOWN
-------------
-- * ASM instance shutdown is possible only after shutting down all instances connected to it
--     (Otherwise: ORA-15097: cannot SHUTDOWN ASM instance with connected RDBMS instance)

-- * The options for the SHUTDOWN command are:
--   - NORMAL    - Waits for all connections to finish (DB instances & SQL sessions)
--   - TRANSACTIONAL - Waits for any active transactions to complete. (Doesn't wait for sessions)
--   - IMMEDIATE - Same as TRANSACTIONAL.
--   - ABORT     - The ASM instance shuts down instantly (Crash).

-- * Shutdown ABORT will require crash recovery upon next startup:
--   - All connected instances will also shutdown.
--       (ORA-15064: communication failure with ASM instance)
--   - Each DG has log files for recovery of ASM metadata.
--   - Actual data is recovered using the DB Redo Logs.

------------------------------
-- ASM Instance Architecture
------------------------------
-----------------
-- MEMORY
-----------------
-- * SGA:
--    - Shared Pool: Used for metadata information
--    - Large Pool : Used for parallel operations
--    - ASM Cache  : Used for data blocks during rebalance operations
--    - Free Memory: Unallocated memory available
-- * Minimum recommended size is 256 MB. (Depends on the used disk space size)
-- * AMM (Automatic memory management) is enabled by default.

------------------------------
-- Background Processes
------------------------------
-----------------------
-- In the ASM instance
-----------------------
-- * The ASM instance contains some special ASM background processes:
--    - RBAL - Manages the Rebalancing process across the DG.
--    - ARBn - Perform the actual relocation of the AU during rebalancing.
--    - GMON - (Group Monitor) - Disk-level activities (Offline, Drop etc).
--    - MARK - Marks ASM allocation units as stale when needed
--    - Onnn - pool of connections to the ASM instance for exchanging messages

-----------------------
-- In the DB instance
-----------------------
-- * In addition, each regular instance that uses ASM has some special background processes:
--    - ASMB - Connects to the ASM instance at startup, and works with it (Data file management).

--------------------------------------------
-- Common ASM V$ Views
--------------------------------------------
-- V$ASM_FILE - Files of disk groups mounted by the ASM instance.

SELECT file_number, name, ROUND(bytes/1024/1024) SIZE_MB
FROM v$asm_alias JOIN v$asm_file
USING (file_number)
ORDER BY file_number, name;


-- V$ASM_CLIENT - DB instances using the ASM instance.
--   (In the DB - ASM instance data if the database has open ASM files)


-- V$ASM_DISK   - List of discovered Disks. (even if not part of any disk group).
--   (In the DB - Disks of DGs used by the database instance).
col name for a10
col failgroup for a11
col path for a20
col redundancy for a10
SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH
FROM v$asm_disk
ORDER BY path;

-- V$ASM_DISKGROUP - List of discovered disk groups.
--   (In the DB - DGs mounted by the local ASM instance)

-- V$ASM_OPERATION - Files with executing long running operations.

-- V$ASM_TEMPLATE - Templates of DGs mounted by the ASM instance.

-- V$ASM_ALIAS  - Aliases that belong to mounted disk groups.
--              - Files with aliases will have 2 rows. (Original & alias)

SELECT file_number, name, CASE ALIAS_DIRECTORY WHEN 'Y' THEN 'Directory' ELSE 'File' END Type
FROM v$asm_alias
ORDER BY type, file_number;

-------------------------------------
-- SYSASM
-------------------------------------
-- * Meant for ASM management. (Separate from SYSDBA for security)
-- * As ASM has no Data Dictionary, users are authenticated via OS or password-file.

-- * SYSOPER, enables basic administration, but not CREATE/ALTER/DROP DGs.
--     (Startup/Shutdown, Mount/Dismount, Rebalance, Online/Offline disk, v$ASM_*)
-- * SYSDBA enables all SYSASM privs, but will be limited in the future.

-- * Via OEM (Only if connected via SYSASM):
--    ASM => Users => Create/Edit/Delete
-- (Connecting as SYSASM: Preferences => Preferred Credentials => ASM:Set Credentials => Specify...

-- In CMD:
SET ORACLE_SID=+ASM
sqlplus /nolog
conn / as sysasm

SELECT GROUP_NUMBER, NAME, TOTAL_MB
FROM v$asm_diskgroup;

-- Or:
CREATE USER asm_mgr IDENTIFIED BY asm_mgr;
GRANT SYSASM TO asm_mgr;
SELECT * FROM v$pwfile_users;
conn asm_mgr/asm_mgr AS SYSASM

-------------------------------------
-- Interaction Between Database Instances and ASM
-------------------------------------

-- * The Database Instance and ASM Instance work together in a coordinated fashion.
--   A Database instance must interact with ASM to map database files to ASM extents.
-- * A Database instance also receives a constant stream of messages relating to ASM operations
--   (such as disk group rebalancing) that may lock or move ASM extents.
-- * Database I/O is not channeled through the ASM instance. In fact, the database conducts I/O
--   operations directly against ASM files.

-------------------------------------
-- Disk Group Maintenance - SYNTAX...
-------------------------------------
-- * Should be performed from the ASM instance by a user with a SYSASM privilege.

set oracle_sid=+ASM
sqlplus / as sysasm

col name for a10
col path for a30

SELECT GROUP_NUMBER, NAME, STATE, TYPE, TOTAL_MB
FROM V$ASM_DISKGROUP
ORDER BY 1;

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk;

-----------------------
-- CREATE DG
-----------------------
-- * We can optionally name and size each disk.
--    (Otherwise a system generated name is given, and ASM determines its size automatically)
-- * FORCE - Move the disk from another DG to this one.

CREATE DISKGROUP DG2
  NORMAL REDUNDANCY
  DISK
   '//./ORCLDISKDATA3' NAME DG2_0,
   '//./ORCLDISKDATA4' NAME DG2_1,
   '//./ORCLDISKDATA5' NAME DG2_2,
   '//./ORCLDISKDATA6' NAME DG2_3;

-----------------------
-- DROP DG
-----------------------
-- * INCLUDING CONTENTS - Must be specified if it contains files.
-- * The DG must be mounted, and its files inactive.
-- * The header of each disk is formatted, so it will be available
--     to other DGs or to the OS.

DROP DISKGROUP DG2 INCLUDING CONTENTS;

-----------------------------
-- Adding disks to a DG
-----------------------------
-- * Disks are added and Rebalaced online while data is fully accessible.
-- * The rebalancing load is determined according to ASM_POWER_LIMIT.

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk
WHERE group_number=0
ORDER BY path;

-- Add two disks to a "DG1":
ALTER DISKGROUP DG1 ADD DISK
   '//./ORCLDISKDATA3' NAME DG1_3,
   '//./ORCLDISKDATA4' NAME DG2_4;


-- Special discovery characters can be used to add a group of disks.
ALTER DISKGROUP DG1 ADD DISK '//./ORCLDISKDATA*';

------------------------------
-- ASM DG Compatability
------------------------------
-- * Determines which Oracle version can run the ASM or work with him.
-- * Determined at Disk Group level.
-- * Two types:
--    1. RDBMS Compatibility (Compatible.RDBMS)
--       - Refers to the regular instances which work with the ASM.
--       - The compatability version of the instance must be equal or higher.
--       - Determines the format of the massages between the ASM and the regular instance.
--       - ASM can operate with instances of different compatability versions.
--    2. ASM Compatability (Compatible.ASM)
--       - The compatability version of the ASM instance (ASM Metadata).
--       - Must be greater or equal to the RDBMS comp. of the FG.

------------------------------
-- ASM DG Attributes
------------------------------
-- * We can specify DG attributes when creating and altering the DG in the ATTRIBUTE clause.
--   - AU_SIZE (C)           - Allocation Unit size (1MB ,2, 4, 8, 16, 32, 64MB)
--   - COMPATIBLE.RDBMS (AC) - Minimal Oracle version for the regular instances using the DG.
--   - COMPATIBLE.ASM   (AC) - Minimal Oracle version for the ASM instance mounting the DG.
--   - DISK_REPAIR_TIME (AC) - Length of time before removing a disk once OFFLINE
--   - TEMPLATE.t-name.REDUNDANCY (A) - Redundancy level of a specifid template.
--                                      (UNPROTECTED / MIRROR / HIGH)
--   - TEMPLATE.t-name.STRIPE (A) - Striping attribute of specified template. (COARSE / FINE)

ALTER DISKGROUP DG2
ATTRIBUTE 'compatible.asm'='11.1';

-- * We can view all attributes of the DGs in the V$ASM_ATTRIBUTE view.
-- * Via the OEM: Create/Edit DG => Advanced Attributes
col name for a15
col value for a10
SELECT group_number DG#, name, value
FROM v$asm_attribute;

--------------------------------
-- ASM Fast Mirror Resync
--------------------------------
-- * Reduces the time required to resynchronize failed disk data.
-- * When a disk goes offline due to a failure, his mirrored extents are still available.
-- * ASM tracks the extents that were modified during the outage using a bitmap
--   (Bit for each extent), and resynchronizes only these extents when the disk is back online.
--
-- * When a disk becomes unavailable due to a failure, ASM places him OFFLINE automatically.
-- * DISK_REPAIR_TIME - Determines the length of time before removing a disk once OFFLINE.
--      (Length of outage which still enables auto-recovery of the disk).
-- * To manually place a DG or FG Offline/Online via the OEM (For maintenance):
--     - ASM Management => Click the DG usage pie-chart => View by disk/FG => Offline
--     - We can override the default value of DISK_REPAIR_TIME here.

--------------------------------------------
-- Bad block repair (11g)
--------------------------------------------
-- * Performed automatically when in Normal/High Redundancy.
-- * When a block access fails, ASM recovers it automatically from its mirror.
-- * If recovery to the original location fails, its recovered to an alternative location.
-- * Can perform manually from ASMCMD using repair/remap.

--------------------------------------------
-- Miscellaneous ALTER Commands
--------------------------------------------
-- Remove a disk from a DG:
ALTER DISKGROUP dgroupA DROP DISK A5;

-- Removing and adding disks in a single statement (Efficient for rebalancing):
ALTER DISKGROUP dgroupA
 DROP DISK A6
 ADD FAILGROUP fred
         DISK '/dev/rdsk/c0t8d0s2' NAME A9;

-- Cancel a disk drop operation (Only if the drop operationis not yet completed)
ALTER DISKGROUP dgroupA UNDROP DISKS;

-- Manually rebalance a DG:
--   - Useful to change the default Power level.
--       (Also for a rebalace that is already active in the background)
--   - Setting the Power level to 0, stop the rebalancing.
ALTER DISKGROUP dgroupB REBALANCE POWER 5;

-- Mount/Dismount a DG:
--   - Makes it unavailable to the DB.
ALTER DISKGROUP DG1 DISMOUNT;
ALTER DISKGROUP DG1 MOUNT;
ALTER DISKGROUP ALL DISMOUNT;

--------------------------------------------
-- ASMCMD
--------------------------------------------
-- * Manage ASM from the command prompt.
-- * Works with ASM Files, Directories & Aliases.
-- * Like other file systems, ASM also has directories which contain the ASM Files.
-- * Each file has a "Fully Qualified File Name", which is the path to it:
--     - The + sign represents the Root directory of the Disk Group:
ASMCMD> ls -l +DGROUP1/ORCL/DATAFILE

-- * mkdir - Add new directories:
ASMCMD> mkdir +dgroup1/sample/mydir

-- * md_backup / md_restore - backup and restore ASM Metadata:
--    - This enables us to recreate a DG with the same directories and templates (attributes):
ASMCMD> md_backup –b /tmp/dgbackup070222 –g admdsk1 –g asmdsk2
ASMCMD> md_restore –t full –g asmdsk1 –i backup_file

-- * lsdsk - Displays ASM disk information.
--     - When connected - uses V$/GV$ tables.
--     - When not-connected - scans the disk headers to get the information.
--         (can use a disk string to limit the discovery set)

--------------------------------------------
-- ASM Scalability and Performance
--------------------------------------------

--------------------------------------------
-- ASM Directories
--------------------------------------------
-- * A directory heirarchy can be defined using the ALTER DISKGROUP statement
--     to support ASM file aliasing:

-- Create a directory
ALTER DISKGROUP DG1 ADD DIRECTORY '+DG1/my_dir';
ALTER DISKGROUP DG1 ADD DIRECTORY '+DG1/emps';

-- Rename a directory.
ALTER DISKGROUP DG1 RENAME DIRECTORY '+DG1/emps' TO '+DG1/employees';

-- Delete a directory and all its contents.
ALTER DISKGROUP DG1 DROP DIRECTORY '+DG1/employees';

--------------------------------------------
-- Aliases
--------------------------------------------

-- * Aliases allow you to reference ASM files using user-friendly names,
--     rather than the fully qualified ASM filenames.
-- *
SELECT name FROM v$datafile;

-- Create an alias using the fully qualified filename.
ALTER DISKGROUP DG1 ADD ALIAS '+DG1/my_dir/my_file.dbf'
  FOR '+DG1/mydb/datafile/asm_tbs.256.718138495';

-- Create an alias using the numeric form filename.
ALTER DISKGROUP DG1 ADD ALIAS '+DG1/my_dir/my_file.dbf'
  FOR '+DG1.256.718138495';

-- Rename an alias.
ALTER DISKGROUP DG1 RENAME ALIAS '+DG1/my_dir/my_file.dbf'
  TO '+DG1/my_dir/my_file2.dbf';


-- Delete an alias.
ALTER DISKGROUP DG1 DROP ALIAS '+DG1/my_dir/my_file2.dbf';

Attempting to drop a system alias results in an error

--------------------------------------------
-- Create an ASM-based tablespace
--------------------------------------------

conn /@orcl as sysdba
CREATE TABLESPACE ASM_TBS DATAFILE '+DG1/asm_tbs01.dbf' size 10m;
ALTER TABLESPACE ASM_TBS ADD DATAFILE '+DG1' size 10m;

SELECT name FROM v$datafile;

--------------------------------------------
-- ASM Intallation Guidelines
--------------------------------------------

-- 1. Using Third-Party tools such as EASEUS Partition Master 8.0.1 Home Edition
--    Create Unallocated space on your hard drive
-- 2. Create free space out of the anallocated space
-- 3. Create at least 2 raw devices (without any file-system)
-- 4. download Oracle Grid Infrastructure from oracle.com
-- 5. Install Oracle Grid Infrastructure must be installed before Oracle Database
--    NOTE : Oracle Grid Infrastructure must be installed before Oracle Database
-- 6. Install Oracle Database


-- * Networking (tnsnames / listener / sqlnet) should be configured in grid_home
-- TNSNAMES.ORA Should look something like this:
/*
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OWNER-3WSG9BKMD)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OWNER-3WSG9BKMD)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )
*/

-- Listener should look something like this :
/*

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = +ASM)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\grid)
    )

  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = OWNER-3WSG9BKMD)(PORT = 1521))
    )
  )

*/

------------------------------------------------
DEMO!
------------------------------------------------

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 21 08:04:08 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

@> conn /@ASM AS SYSASM
Connected.
SYS@ASM>
SYS@ASM> -- See current Disk Groups:
SYS@ASM> SELECT GROUP_NUMBER, NAME, TOTAL_MB
  2  FROM v$asm_diskgroup;

GROUP_NUMBER NAME                             TOTAL_MB
------------ ------------------------------ ----------
           1 DG1                                  9998

SYS@ASM> col path for a50
SYS@ASM> -- See available disks:
SYS@ASM> SELECT GROUP_NUMBER "DG#", PATH
  2  FROM v$asm_disk
  3  ORDER BY path;

       DG# PATH
---------- --------------------------------------------------
         1 \\.\ORCLDISK_LABEL_E
         1 \\.\ORCLDISK_LABEL_F
         0 \\.\ORCLDISK_LABEL_G
         0 \\.\ORCLDISK_LABEL_H

SYS@ASM>
SYS@ASM> -- DG#=0 means that specific path does not have any Disk Group
SYS@ASM>
SYS@ASM> -- Join:
SYS@ASM> SELECT GROUP_NUMBER, dg.name, SUM(d.TOTAL_MB) AS "Size", COUNT(*) AS "Disks"
  2  FROM v$asm_diskgroup dg JOIN v$asm_disk d
  3  USING (GROUP_NUMBER)
  4  GROUP BY GROUP_NUMBER, dg.name ;

GROUP_NUMBER NAME                                 Size      Disks
------------ ------------------------------ ---------- ----------
           1 DG1                                  9998          2

SYS@ASM>
SYS@ASM> -- Create a new disk group DG2:
SYS@ASM> CREATE DISKGROUP DG2
  2  DISK
  3  '\\.\ORCLDISK_LABEL_G' NAME DG2_0,
  4  '\\.\ORCLDISK_LABEL_H' NAME DG2_1 ;

Diskgroup created.

SYS@ASM> -- See available disks:
SYS@ASM> SELECT GROUP_NUMBER "DG#", PATH
  2  FROM v$asm_disk
  3  ORDER BY path;

       DG# PATH
---------- --------------------------------------------------
         1 \\.\ORCLDISK_LABEL_E
         1 \\.\ORCLDISK_LABEL_F
         2 \\.\ORCLDISK_LABEL_G
         2 \\.\ORCLDISK_LABEL_H

SYS@ASM>
SYS@ASM> -- DROP DISKGROUP DG2;
SYS@ASM>
SYS@ASM> -------------------------------------
SYS@ASM> -- Create a tablespace with ASM file
SYS@ASM> -- (Performed in the DB instance)
SYS@ASM> -------------------------------------
SYS@ASM>
SYS@ASM> conn sys/oracle@orcl as sysdba
Connected.
SYS@orcl>
SYS@orcl> CREATE TABLESPACE ASM_TBS
  2    DATAFILE '+DG2/asm_tbs01.dbf' SIZE 100M;

Tablespace created.

SYS@orcl> SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
+DG2/asm_tbs01.dbf

6 rows selected.

SYS@orcl>
SYS@orcl> --   DROP TABLESPACE asm_tbs INCLUDING CONTENTS AND DATAFILES;
SYS@orcl>
SYS@orcl>
SYS@orcl> -- * Stamp candidate disks / change name and creating disk groups can be done by using ASMCA
SYS@orcl> --   $oracle_grid_home\BIN\asmca.bat
SYS@orcl> -- * for stamping and name changing :
SYS@orcl> --   SHUTDOWN DB => SHUTDOWN ASM => stamp/change names => STARTUP ASM => STARTUP DB
SYS@orcl> -- * SHUTDOWN ASM => stamp/change names => STARTUP ASM
SYS@orcl>

SYS@orcl> -------------------------
SYS@orcl> -- ASM Templates
SYS@orcl> -------------------------
SYS@orcl>
SYS@orcl> -- * A template is a collection of file attributes that are applied to the ASM files.
SYS@orcl> --   It is associated with a Disk Group.
SYS@orcl> -- * Whenever a file is created the file attributes specific to the template are applied to
SYS@orcl> --   the files.
SYS@orcl> -- * For eg., if we want to create a users tablespace that contains data file we use the
SYS@orcl> --   DATAFILE
SYS@orcl> --   template.
SYS@orcl> -- * Default Template: When you create a disk group, Oracle creates system default template
SYS@orcl> --   for the disk group.
SYS@orcl>

SYS@orcl> SELECT GROUP_NUMBER , NAME , STRIPE, REDUNDANCY
  2  FROM V$ASM_TEMPLATE ;

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           1 PARAMETERFILE                  COARSE MIRROR
           1 ASMPARAMETERFILE               COARSE MIRROR
           1 ASMPARAMETERBAKFILE            COARSE MIRROR
           1 DUMPSET                        COARSE MIRROR
           1 CONTROLFILE                    FINE   HIGH
           1 FLASHFILE                      COARSE MIRROR
           1 ARCHIVELOG                     COARSE MIRROR
           1 ONLINELOG                      COARSE MIRROR
           1 DATAFILE                       COARSE MIRROR
           1 TEMPFILE                       COARSE MIRROR
           1 BACKUPSET                      COARSE MIRROR

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           1 AUTOBACKUP                     COARSE MIRROR
           1 XTRANSPORT                     COARSE MIRROR
           1 CHANGETRACKING                 COARSE MIRROR
           1 FLASHBACK                      COARSE MIRROR
           1 DATAGUARDCONFIG                COARSE MIRROR
           1 OCRFILE                        COARSE MIRROR
           1 OCRBACKUP                      COARSE MIRROR
           1 ASM_STALE                      COARSE HIGH
           2 PARAMETERFILE                  COARSE MIRROR
           2 ASMPARAMETERFILE               COARSE MIRROR
           2 ASMPARAMETERBAKFILE            COARSE MIRROR

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           2 DUMPSET                        COARSE MIRROR
           2 CONTROLFILE                    FINE   HIGH
           2 FLASHFILE                      COARSE MIRROR
           2 ARCHIVELOG                     COARSE MIRROR
           2 ONLINELOG                      COARSE MIRROR
           2 DATAFILE                       COARSE MIRROR
           2 TEMPFILE                       COARSE MIRROR
           2 BACKUPSET                      COARSE MIRROR
           2 AUTOBACKUP                     COARSE MIRROR
           2 XTRANSPORT                     COARSE MIRROR
           2 CHANGETRACKING                 COARSE MIRROR

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           2 FLASHBACK                      COARSE MIRROR
           2 DATAGUARDCONFIG                COARSE MIRROR
           2 OCRFILE                        COARSE MIRROR
           2 OCRBACKUP                      COARSE MIRROR
           2 ASM_STALE                      COARSE HIGH

38 rows selected.

SYS@orcl>

SYS@orcl> ------------------------
SYS@orcl> -- ASM Striping
SYS@orcl> ------------------------
SYS@orcl>
SYS@orcl> -- * ASM stripes the file to AUs, and devides them between the DG disks.
SYS@orcl> -- * Two types of striping:
SYS@orcl> --      1. Coarse-based striping:
SYS@orcl> --          - Each stripe size is one AU.
SYS@orcl> --          - Classic striping. Suitable for most file types.
SYS@orcl> --          - Used for load balancing.
SYS@orcl> --      2. Fine-Grained striping
SYS@orcl> --            - Each stripe size is 128KB. (Wider distribution of data)
SYS@orcl> --            - Used for decreasing IO Latency (Waiting for IO).
SYS@orcl> --            - Suitable for small IO operations such as Redo Log Files.
SYS@orcl>
SYS@orcl> -- ONLINELOG
SYS@orcl> ALTER DISKGROUP dg2 MODIFY TEMPLATE ONLINELOG
  2  ATTRIBUTES (FINE);

Diskgroup altered.

SYS@orcl> ALTER DISKGROUP dg2 MODIFY TEMPLATE DATAFILE
  2  ATTRIBUTES (COARSE);

Diskgroup altered.

  1  Select * From V$ASM_TEMPLATE
  2  WHERE name IN ('DATAFILE', 'ONLINELOG')
  3* AND group_number = 2
SYS@orcl> /

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME                           PRIM MIRR
------------ ------------ ------ ------ - ------------------------------ ---- ----
           2            7 MIRROR FINE   Y ONLINELOG                      COLD COLD
           2            8 MIRROR COARSE Y DATAFILE                       COLD COLD

SYS@orcl> SELECT GROUP_NUMBER , NAME , STRIPE, REDUNDANCY
  2  FROM V$ASM_TEMPLATE
  3  WHERE name IN ('DATAFILE', 'ONLINELOG')
  4  AND group_number = 2 ;

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           2 ONLINELOG                      FINE   MIRROR
           2 DATAFILE                       COARSE MIRROR

SYS@orcl>
SYS@orcl> CONN /@ASM AS SYSASM
SYS@ASM> DROP DISKGROUP DG2 INCLUDING CONTENTS;

---------------------------------------------------------------------------------------------
-- ASM Mirroring & Failure Groups
---------------------------------------------------------------------------------------------
-- * ASM can protect our files from media failures (disk / disk-controller crash)
--
-- * Mirroring:
--   - Applied at Extent (AU) level (Not at file or disk level).
--   - When an AU is allocated to a file on a specific disk, a mirrored AU is also allocated on another disk in the SAME Disk Group.
--   - Therefore, if a disk is lost, we can restore its contents from other disks on its Disk Group.

-- * Failure Group:
--   - A failure group is a subset of the disks in a disk group, which could fail at the same time because they share hardware. (For example, disks that share the same disk controller)
--   - By default, each ASM disk belongs to his own Failure Group (FG).
--   - However, we can group a few disks in a DG to a single Failure Group.
--   - When an AU is allocated on a FG disk, a mirrored AU is also allocated on ANOTHER Failure Group in the SAME Disk Group.
--   - Therefore, if a Failure Group is lost, we can restore its contents from other disks on its Disk Group.

-- *   types or mirroring:
--     1. External Redundancy - No mirroring or FGs. (When using an external tool for protection).
--     2. Normal Redundancy   - Two-way mirroring.   (Two copies)
--     3. High Redundancy     - Triple-mirroring.    (Three copies)

SYS@ASM> col path for a50
SYS@ASM> -- See available disks:
SYS@ASM> SELECT GROUP_NUMBER "DG#", PATH
  2  FROM v$asm_disk
  3  ORDER BY path;

       DG# PATH
---------- --------------------------------------------------
         1 \\.\ORCLDISK_LABEL_E
         1 \\.\ORCLDISK_LABEL_F
         0 \\.\ORCLDISK_LABEL_G
         0 \\.\ORCLDISK_LABEL_H

-- A protected disk group:

SYS@ASM>   CREATE DISKGROUP DG2
  1        NORMAL REDUNDANCY
  2        DISK
  3        '\\.\ORCLDISK_LABEL_G',
  4        '\\.\ORCLDISK_LABEL_H' ;

Diskgroup created.

SYS@ASM> SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP
  2  FROM v$asm_disk;

       DG#       DSK# NAME       FAILGROUP
---------- ---------- ---------- -----------
         2          0 DG2_0000   DG2_0000
         2          1 DG2_0001   DG2_0001
         1          0 DG1_0000   DG1_0000
         1          1 DG1_0001   DG1_0001


SYS@ASM> DROP DISKGROUP DG2 INCLUDING CONTENTS;

Diskgroup dropped.


SYS@ASM> CREATE DISKGROUP DG2
  2    NORMAL REDUNDANCY
  3    FAILGROUP controller1 DISK
  4   '\\.\ORCLDISK_LABEL_G'
  5    FAILGROUP controller2 DISK
  6    '\\.\ORCLDISK_LABEL_H' ;

Diskgroup created.

SYS@ASM> SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP
  2  FROM v$asm_disk;

       DG#       DSK# NAME       FAILGROUP
---------- ---------- ---------- -----------
         2          0 DG2_0000   CONTROLLER1
         2          1 DG2_0001   CONTROLLER2
         1          0 DG1_0000   DG1_0000
         1          1 DG1_0001   DG1_0001

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Oracle Users Management

PROBLEM:
What are the basics of Oracle users management?

1. Users 
• username 
• identified by 
• default tablespace 
• temporary tablespace 
• password expire option 
• profile 
• account 
2. Priviliges 
• system privileges
• object privileges
3. Roles 
4. Quotas  
5. Profiles 

SOLUTION:

----------------------------------------------------------
-- USERS
----------------------------------------------------------

-- View all users / schemas

SELECT username FROM dba_users;

-- Create a new user
CREATE USER kipi IDENTIFIED BY sumsum ;

--   * EXTERNALLY Clause - External user will not require any login password as local user does.
--     Such a user will be authenticated by operating System.
--     Means: If you can log in to operating system, you can use oracle too.

show parameter os_authent_prefix

select UPPER(sys_context('userenv','os_user')) from dual;

CREATE USER "OPS$BASDBINS\ADMINISTRATOR" IDENTIFIED EXTERNALLY;

GRANT CREATE SESSION TO "OPS$BASDBINS\ADMINISTRATOR";

conn /

-- * GLOBALLY Clause - GLobal user will also not require any login password as local user does.
--   Such a user will be authenticated by a directory service.
--   like OID (Oracle Internet Directory).
-- * GLOBALLY AS extname, where the username and password will be passed to
--   the extname service for logon validation

-- * DEFAULT TABLESPACE
-----------------------
-- * Specify the default tablespace for objects that the user creates.
--   If you omit this clause, then the user's objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user's objects are
--   stored in the SYSTEM tablespace 
-- * DEFAULT TABLESPACE option does not grant any quota to the user.

-- * QUOTA [size] ON [tablespace]
---------------------------------
--   specify the maximum amount of space the user can allocate in the tablespace
--   UNLIMITED lets the user allocate space in the tablespace without bound.

-- * TEMPORARY TABLESPACE
-------------------------
--   Specify the tablespace or tablespace group for the user's temporary segments.
--   If you omit this clause, then the user's temporary segments are stored in the database default temporary tablespace or, if none has been specified, in the SYSTEM tablespace.

-- * PASSWORD EXPIRE
--------------------
--   Specify PASSWORD EXPIRE if you want the user's password to expire.
--   This setting forces the user or the DBA to change the password before the user
--   can log in to the database

-- * PROFILE [profile name]
---------------------------
--   Specify the profile you want to assign to the user.
--   The profile limits the amount of database resources the user can use.
--   If you omit this clause, then Oracle Database assigns the DEFAULT profile to the user.

-- * ACCOUNT Clause
-------------------
--   Specify ACCOUNT LOCK to lock the user's account and disable access.
--   Specify ACCOUNT UNLOCK to unlock the user's account and enable access to the account.

CREATE USER debra;

CREATE USER debra
IDENTIFIED BY pass;

DROP USER debra;

CREATE USER debra
IDENTIFIED BY debra
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 10m ON users
QUOTA UNLIMITED ON example
PROFILE DEFAULT
ACCOUNT LOCK
PASSWORD EXPIRE;

SELECT username, DEFAULT_TABLESPACE, profile
FROM dba_users
ORDER BY username;

-- Quotas
SELECT * FROM dba_ts_quotas
WHERE USERNAME = 'debra';

-- * UNLIMITED TABLESPACE privilege - which is a result of assigning the RESOURCE role.
--   In that case user will not appear in dba_ts_quotas.

-- Via OEM:
-- Server => (Security) => Users

-------------------------------------------------------
-- Passwords
-------------------------------------------------------

-- Password column is null

SELECT username, password FROM dba_users WHERE username = 'HR';

SELECT NAME, PASSWORD FROM SYS.USER$ WHERE name = 'HR';

ALTER USER hr IDENTIFIED BY my_pass

conn hr/my_pass

-- Change whatever i want...

conn / as sysdba

ALTER USER hr IDENTIFIED BY VALUES ' '

-- Hashing vs Encription
------------------------
-- * There is a huge difference between encryption and hashing.
-- * Encrypton is a reversible proces, while hashing is not.
-- * With encryption, you can allways get the original value from the encrypted value,
--   provided that you have propper encryption key and an alghorytm.
-- * But with hashing process it is different. You can (generally speaking) never get
--   the original value from the hashed value, even if you know the hashing algorythm.
--   It is mathematicaly one-way process.

-------------------------------------------------------
-- PRIVILEGES
-------------------------------------------------------

-- * System privileges
--   Allow a user to perform certain database actions, such as create a table,
--   or create an index, or even connect to the instance
-- * Object privileges
--   allow a user to manipulate objects, as by reading data through a view,
--   executing a stored procedure, or changing data in a table

-- Connect first attempt
Conn debra/debra

conn / as sysdba
GRANT CREATE SESSION TO debra;

-- Connect second attempt :
Conn debra/debra

SELECT * FROM session_privs;

-- Try to create a table:
create table test(y number);

conn / as sysdba
grant create table to debra;

conn debra/debra

SELECT * FROM session_privs;

create table test(y number);

-- GRANT ANY
------------

-- Try to create a table on another schema:
[debra] create table sh.newtab(y number);

conn / as sysdba

grant create any table to debra;

conn debra/debra

create table sh.newtab(y number);

create table sys.newtab(y number);

conn / as sysdba

show parameter O7_DICTIONARY_ACCESSIBILITY

------------------------------------------------
-- WITH ADMIN OPTION - (System - No cascade)
------------------------------------------------

-- GRANT system privilege  [ANY] TO username [WITH ADMIN OPTION]
-- REVOKE system privilege [ANY] FROM username

conn / as sysdba

grant create any table to debra with admin option ;

conn debra/debra

grant create any table to hr ;

grant create any table to hr with admin option ;

-- is it possible to grant user hr CREATE TABLE privilege ?

grant create table to hr ;

conn hr/hr

create table sh.test2(y number) ;

conn / as sysdba

revoke create any table from debra;

conn debra/debra

create table sh.test3(y number) ;

conn hr/hr

create table sh.test3(y number) ;

-- we can monitor different privileges using dba_sys_privs
-- we cannot monitor the hierarchy

SELECT *
FROM dba_sys_privs
WHERE grantee in ('debra','HR','SH')
ORDER BY grantee ;

-------------------------------------------------------
-- OBJECT PRIVILEGES (CASCADE)
-------------------------------------------------------

-- GRANT | REVOKE object privilege ON object TO username [WITH GRANT OPTION]

conn debra/debra

update hr.employees set salary=200;

conn hr/hr

GRANT UPDATE ON employees TO debra WITH GRANT OPTION;

GRANT UPDATE (salary) ON employees TO debra WITH GRANT OPTION;

conn debra/debra

update hr.employees set salary=200;

roll

GRANT UPDATE ON hr.employees to SH;
-- or
GRANT UPDATE ON hr.employees to SH WITH GRANT OPTION ;

Grant succeeded.

conn sh/sh

update hr.employees set salary=400;

roll

conn / as sysdba

col owner for a10
col table_name for a15
col grantor for a15
col privilege for a15

SELECT GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE,GRANTABLE
FROM  dba_tab_privs
WHERE table_name='EMPLOYEES'
AND GRANTEE <> 'OE'

conn hr/hr (or conn / as sysdba)

REVOKE UPDATE ON hr.employees FROM debra;

SELECT GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE,GRANTABLE
FROM  dba_tab_privs
WHERE table_name='EMPLOYEES'
AND GRANTEE <> 'OE'

-- Granting all object privileges to a user using a quick script

conn hr/hr

SELECT 'GRANT ALL ON ' || table_name || ' TO ' || '&username' FROM user_tables

-------------------------------------------------------
-- ROLES
-------------------------------------------------------

-- recreate debra

drop user debra ;

create user debra identified by debra;

grant create session to debra ;

grant CREATE VIEW to debra ;

grant UPDATE on HR.EMPLOYEES to debra ;

-- create role basic;

drop role basic ;

create role basic;

grant CREATE TABLE to basic;

grant RESOURCE to basic ;

grant DROP ANY TABLE to basic ;

grant UPDATE on HR.DEPARTMENTS to basic ;

-- Grant role to debra

GRANT basic TO debra;

conn debra/debra

select * from session_privs ;

conn / as sysdba

Helpful Views
-------------------------------

-- list of object privs for every user
SELECT GRANTEE, TABLE_NAME, PRIVILEGE
FROM dba_tab_privs
WHERE GRANTEE = 'debra';

-- list of system privs for every user
SELECT * FROM dba_sys_privs
WHERE grantee = 'debra'; 

-- list of roles for every user
SELECT GRANTEE, GRANTED_ROLE
FROM dba_role_privs
WHERE GRANTEE = 'debra'

-- list of roles for every role
SELECT ROLE, GRANTED_ROLE
FROM ROLE_ROLE_PRIVS
WHERE ROLE = 'BASIC';

-- list of sys privs for every role
SELECT *
FROM dba_sys_privs
WHERE grantee = 'RESOURCE';

-- list of object privs for every role
SELECT GRANTEE, TABLE_NAME, PRIVILEGE
FROM dba_tab_privs
WHERE GRANTEE = 'BASIC'

-- or we could just use OEM or external scripts such as
-- user_privs.sql
-- source : http://www.petefinnigan.com/tools.htm

-- DEFAULT / NON-DEFAULT ROLES
------------------------------

ALTER USER debra DEFAULT ROLE ALL;

ALTER USER debra DEFAULT ROLE NONE;

ALTER USER debra DEFAULT ROLE ALL EXCEPT basic;

conn debra/debra

SELECT username, granted_role, default_role
FROM   user_role_privs ;

select * from session_privs ;

SET ROLE basic;

select * from session_privs ;
                               
-- PASSWORD PROTECTED ROLES:
----------------------------

conn / as sysdba

ALTER ROLE basic IDENTIFIED BY mypass;

conn debra/debra

select * from session_privs ;

SET ROLE basic IDENTIFIED BY mypass;

select * from session_privs ;

ALTER ROLE basic NOT IDENTIFIED ;
                                                                  

---------------------------------------------------------------------------------
-- PROFILES
---------------------------------------------------------------------------------

-- Via OEM:
-- Server => Security => Profiles

PROFILES
--------
-- * A profile is an Oracle object that allows you to set both password management and
--   resource limits.
-- * In every Oracle database a single profile is created when you create the
--   database. This profile, called DEFAULT, places no limits on password and account
--   lockout, or on resource utilization.
-- * You can change the settings of the DEFAULT profile to conform to your requirements,
--   and they will then be applied to all users in thedatabase assigned the DEFAULT profile.
--   A database administrator may create additional profiles dealing with password or
--   account lockout issues, resource management settings, or both.
-- * Once created, a profile can be assigned to a user account as it is created, or it can be
--   assigned to the user with the ALTER USER command.
-- * Any settings in the profile will then apply to the user the next time he/she connects to the
--   database.
-- * A user may have only one profile active at one time, so you need to ensure that the
--   settings within the profile match the requirements of each user.

-- * PERFORMANCE
----------------
-- * CPU_PER_SESSION -
--   The total CPU time, measured in hundredths of a second, that a user
--   is allowed to consume during a session.
--   Once the limit is reached, the user’s session is terminated with
--   an Oracle server error message.
--   To reset this limit, the user needs to disconnect from the instance
--   and connect again.

-- * CPU_PER_CALL -
--   The total CPU time, measured in hundredths of a second, that a user
--   is allowed to consume for a single SQL statement.
--   Once the limit is reached, the SQL statement is aborted and the transaction it is
--   a part of is rolled back.
--   The user’s session remains connected.
--   The limit is reset on every call to the database.

-- * CONNECT_TIME -
--   The maximum amount of time, specified in minutes, that a user may remain
--   connected to the instance.

-- * IDLE_TIME -
--   The maximum amount of time, specified in minutes,
--   that a user’ssession may remain connected to the instance while not performing
--   any database activity.

-- * SESSIONS_PER_USER -
--   The maximum number of concurrent sessions that a user may have at one time.

-- * LOGICAL_READS_PER_SESSION -
--   The number of blocks (both physical—from disk—and logical—
--   from the database buffer cache) that the user is allowed to read
--   during their session.
--   Once the number of blocks specified by this parameter are read, the user will need to
--   start another session in order to access data in the database.

-- * LOGICAL_READS_PER_CALL -
--   The number of blocks (both physical—from disk—and logical—from
--   the database buffer cache) that the user is allowed to read when
--   executing a SQL statement.
--   Once the number of blocks specified by this parameter are read,
--   the SQL statement will be terminated and any transaction that it is a part
--   of will be rolled back.

-- * PRIVATE_SGA -
--   In a Multi-Threaded Server (MTS) environment, this parameter
--   specifies the maximum number of bytes that a user’s session can
--   occupy in the SGA.
--   If you are not connected to the database instance with an MTS connection,
--   this parameter is ignored.

-- Change the costs
-------------------

-- * Example

ALTER RESOURCE COST
CPU_PER_SESSION 100
CONNECT_TIME      1;

-- * The weights establish this cost formula for a session:
--   cost = (100 * CPU_PER_SESSION) + (1 * CONNECT_TIME)
-- * If you do not assign a weight to a resource, then the weight defaults to 0, and use of the
--   resource subsequently does not contribute to the cost.
--   The weights you assign apply to all subsequent sessions in the database.

-- The composite limit
-----------------------

--   Specifies a numerical value that is the weighed average of four resource limits:
--   1.CPU_PER_SESSION
--   2.CONNECT_TIME
--   3.LOGICAL_READS_PER_SESSION
--   4.PRIVATE_SGA
--   Setting COMPOSITE_LIMIT will allow Oracle to monitor all four
--   of these parameter values; when the combination of all exceeds the
--   value specified by COMPOSITE_LIMIT, the user’s session will be
--   terminated.

-- * Example :

ALTER RESOURCE COST
CPU_PER_SESSION 10
LOGICAL_READS_PER_SESSION 2
PRIVATE_SGA 6
CONNECT_TIME 1;

-- * When the session hits the composite limit, the session is terminated.

ALTER PROFILE app_developr
COMPOSITE_LIMIT 500;

-- * PASSWORD
-------------
-- * PASSWORD_LIFE_TIME
--   The number of days that will pass before the user is prompted to change his password.

-- * PASSWORD_GRACE_TIME
--   The number of days following a successful login after the expiration of PASSWORD_LIFE_TIME.
--   The grace time starts after the login, even if the password has already expired.

-- * PASSWORD_REUSE_MAX
--   Specify the number of password changes required before the current password can be reused.
--   If you set PASSWORD_REUSE_MAX to an integer value, then you must set
--   PASSWORD_REUSE_TIME to UNLIMITED.

-- * PASSWORD_REUSE_TIME
--   The time in days that must elapse before apassword can be reused.
--   If you set PASSWORD_REUSE_TIME to an integer value, then you must set
--   PASSWORD_REUSE_MAX to UNLIMITED.

-- * These two parameters must be set in conjunction with each other.
-- * For these parameter to have any effect, you must specify an integer for both of them.
--   Possible via sql*plus only.

ALTER PROFILE "DEFAULT" LIMIT
PASSWORD_REUSE_MAX 4
PASSWORD_REUSE_TIME 1/1440

-- * PASSWORD_VERIFY_FUNCTION
--   A PL/SQL function that will perform complexity checks (or any other checks
--   that may be needed) whenever a password is changed.

-- * FAILED_LOGIN_ATTEMPTS
--   After this number of consecutive connect requests with an incorrect password,
--   the account will be locked.
--   This will prevent hackers from trying numerous common passwords in an attempt
--   to guess the correct one.
--   The counter is reset after a successful connection.

-- * PASSWORD_LOCK_TIME
--   The number of days for which the account will be locked if the FAILED_LOGIN_ATTEMPTS
--   limit is reached. If days are unsuitable as units, use appropriate arithmetic.
--   For instance, one day has one thousand four hundred and forty minutes,
--   so setting the PASSWORD_LOCK_TIME to (30/1440) is equivalent to thirty minutes
--   enough to foil a hacker who is throwing a dictionary at your database, without inconveniencing --   forgetful users too much.

-- Current profiles - by default - all is unlimited:
col limit for a15
set lines 200
SELECT *
FROM dba_profiles
ORDER BY profile, RESOURCE_TYPE;

-- Users profiles
SELECT username , profile
FROM dba_users ;

-- Create a new profile:
CREATE PROFILE dev_prof
LIMIT
SESSIONS_PER_USER 2
FAILED_LOGIN_ATTEMPTS 5;

-- All the rest - depend on the Default values (Unlimited):
SELECT *
FROM dba_profiles
WHERE profile= 'DEV_PROF'
ORDER BY profile, RESOURCE_TYPE;

ALTER USER hr PROFILE DEFAULT;

ALTER PROFILE "DEFAULT"
LIMIT FAILED_LOGIN_ATTEMPTS 3 ;

conn hr/hr

conn hr/badpass

conn hr/badpass

conn hr/badpass

conn / as sysdba

ALTER USER hr ACCOUNT UNLOCK;

ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY hr;

ALTER USER sh ACCOUNT UNLOCK IDENTIFIED BY sh PASSWORD EXPIRE;

-- Complexity Function:

get ?\rdbms\admin\utlpwdmg.sql

ALTER PROFILE dev_prof
LIMIT
PASSWORD_VERIFY_FUNCTION my_func

ALTER USER hr PROFILE dev_prof;

-- Enabling resource limits
show parameter resource_limit

-- ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;                                                            
-- Change a default profile for all users
SELECT 'ALTER USER '||username||' PROFILE dev_prof;' FROM dba_users

-- for more complex management
-- Via OEM => Server => Resource Manager

-------------------------------------------------------
-- PROXY USERS
-------------------------------------------------------
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:21575905259251

-- Enable HR to connect to debra without a password:
ALTER USER debra GRANT CONNECT THROUGH hr;

conn hr[debra]/hr

show user

-- Can access HR?
SELECT * FROM hr.departments;

-- Try to connect to HR from debra:
conn debra[hr]/debra

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Basic Oracle storage principles in 1 hour

PROBLEM:

You want to learn some basic Oracle storage principles in 30 minutes:

1. Logical & Physical

• Database
• Datafiles
• Tablespace
• Segments
• Extents (and HWM)
• Oracle blocks
• OS blocks
• Schemas

2. Tablespace Types

• SYSTEM
• SYSAUX
• Temporary
• UNDO
• User Defined tablespaces

3. In which Tablespace a new table will be created?

• Explicitly specifing the Tablespace
• If the Tablespace not explicitly specified, new table will be created in user Default Tablespace
• If there is no User Defalt Tablespace, it will be created in the Database Default Tablespace (10g)
• If there is no Database Defalt Tablespace, the table will be created in the System Tablespace

4. Extent Management

• Locally managed VS. Dictionary managed
• Locally managed - Extent allocation: AUTOALLOCATE VS. UNIFORM

5. Resize Tablespace

• Add a new file
• Increase an existing datafile
• Manual Resize - RESIZE
• Automatic resize - AUTOEXTEND

6. Online/Offline, Read Only/Read Write

7. OMF Oracle-Managed Files

SOLUTION:

-- STORAGE - Logical & Physical
-- 1. Database
-- Database ---< Tablespace
-- Tablespace: Within a database the tablespace is the largest logical storage structure. Every database must consist of one or more tablespaces. Every tablespace must belong to one and only -- one database. Tablespace size = sum of data files size

-- Database ---< Datafiles
-- Every Database must consist of one or more datafiles. Each datafile must belong to one and only one Database.

-- 2. Tablespace
-- Tablespace ---< Segments
-- Segment: Within a tablespace, space is allocated to segments. A segment is an object in the database that requires storage, such as a table or an index. A view, on the other hand, is not a -- segment, since it does not store data; it is just a prewritten query that allows easy access to data stored in tables.
-- Every tablespace may contain one or more segments. Every segment must exist in one and only one tablespace.

-- Tablespace ---< Datafiles
-- Every tablespace must consist of one or more datafiles. Each datafile must belong to one and only one tablespace.

-- 3. Segments
-- Segment ---<  Extents
-- Extent: When space is allocated for segments in a tablespace, it is allocated in extent units. Every segment must consist of one or more extents. Each extent must belong to one and only one segment.

-- Segments >---< Datafiles
-- Every datafile consists of one or more segments. Each segmets can belong to one or more datafiles

-- 4. Extents
-- Extents  ---< Oracle blocks (Default 8k)
-- Oracle block: Oracle block is the smallest logical storage unit for every Oracle I/O request at least one Oracle block is being read. Every extent must consist of one or more Oracle blocks. Each Oracle block may belong to one and only one extent.

-- Extents >--- Datafiles
-- Every extent must be located in one and only one datafile. The space in the datafile may be allocated as one or more extents. Delete operations (with / without commit) won't change table High Water Mark (HWM)

-- 5. Oracle blocks
-- Oracle block ---< OS blocks
-- Every Oracle block must consist of one or more operating system blocks. Every operating system block may be part of one and only one Oracle block. It is a good idea to keep the operating -- system block size the same as the Oracle block size. This way, for every Oracle I/O request the operating system needs to retrieve only one block.

-- Datafiles ---< OS Blocks
-- OS Block: Operating system blocks are the minimum allocation unit for the file system. Each file system has its own minimum and default size. Every datafile must consist of one or more operating system blocks. Each operating system block may belong to one and only one datafile.

-- Schemas: There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.

-- Tablespace Types

-- The SYSTEM tablespace
-- * The SYSTEM tablespace contains the data dictionary—internal tables that describe the structure of the database itself, all of the objects in it, users, roles, and privileges.
-- * The SYSTEM tablespace also contains the SYSTEM undo or rollback segment (also called SYSTEM), which can be used only for operations on objects stored in the SYSTEM tablespace.
-- * Oracle Database creates The SYSTEM tablespace automatically when the database is created.
-- * User objects should be kept out of the SYSTEM tablespace in order to keep it operating efficiently.
-- * Cannot be taken offline

-- The SYSAUX tablespace (10g)
-- * The SYSAUX tablespace is also considered a SYSTEM tablespace and is used to store statistical and other Oracle tools information (AWR, OEM and many more)
-- * The SYSAUX tablespace is always created during database creation or database upgrade

-- The Temporary tablespaces (9i)
-- * Temporary tablespaces are used mainly to manage space for database sort operations (Server Process will try to sort data in the PGA by default, if it's not possible it will sort it in the Temporary tablespaces)
-- * When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database.
-- * A locally managed SYSTEM tablespace cannot be used for default temporary storage.
-- * If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage. However, you will receive a warning in ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.
-- * Cannot be taken offline

-- The UNDO tablespaces
-- * Holds information that is used to roll back, or undo, changes to the database
-- * A database can contain more than one undo tablespace, but only one can be in use at any time.
-- * Cannot be taken Offline

-- User Defined tablespaces
-- * User defined tablespaces created by the user

-- Viewing logical & physical storage using System Tables

-- Redo Log Files:
SELECT * FROM v$log;
SELECT * FROM v$logfile;

-- Manual log switch:
-- ALTER SYSTEM SWITCH LOGFILE;

-- Tablespaces and Data files
DESC DBA_TABLESPACES

-- Information about all tablespaces in the DB:
SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES;

-- Information about all data files in the DB:
DESC DBA_DATA_FILES

col file_name for a50
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB
FROM DBA_DATA_FILES;

-- Tablespace Size
-- Allocated Size:
SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb, COUNT(*) files
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME;

-- Free Space:
SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;

-- Via OEM: Server => (Storage) => Tablespaces / Datafiles

-- In which Tablespace a new table will be created?

-- Explicitly specifing the Tablespace
CREATE TABLE newtab (id NUMBER) TABLESPACE users;

-- If the Tablespace not explicitly specified, new table will be created in user
-- Defalt Tablespace
SELECT username, DEFAULT_TABLESPACE
FROM dba_users;

-- If there is no User Defalt Tablespace, it will be created in the
-- Database Defalt Tablespace (10g)
SELECT * FROM  database_properties
WHERE PROPERTY_NAME LIKE '%TABLESPACE%'

ALTER DATABASE DEFAULT TABLESPACE example;

-- If there is no Database Defalt Tablespace, the table will be created in the System Tablespace

-- Creating a new Tablespace

CREATE TABLESPACE MY_TBS
DATAFILE 'c:\my_tbs_file1.dbf' SIZE 10M ,
'c:\my_tbs_file2.dbf' SIZE 10M

DROP TABLESPACE MY_TBS ;

DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES ;

CREATE BIGFILE TABLESPACE BIGFILE_TBS
DATAFILE 'C:\BIGFILE.DBF'SIZE 100M ;

DROP TABLESPACE BIGFILE_TBS INCLUDING CONTENTS AND DATAFILES ;

-- Bigfile VS. Smallfile
-- * A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single datafile or tempfile is 32TB (for a tablespace with 8K blocks).
-- * A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks. (32 GB for 8k block)

-- Locally managed VS. Dictionary-managed:

-- Dictionary managed
-- * Dictionary-managed tablespaces are the historical way of managing extents requires any allocation or deallocation of an extent to update a table in the data dictionary.
-- * This means that any time an extent is allocated to a table, the data dictionary must be touched to record the change. When a table is dropped or truncated, the data dictionary must be changed. Because of the amount of work required on the data dictionary when using dictionary-managed extents, they are no longer recommended and exist primarily for backward compatibility.
-- * All tablespaces should be created withlocal extent management

CREATE TABLESPACE dict_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\dicttbs.dbf' SIZE 10m
EXTENT MANAGEMENT DICTIONARY

CREATE TABLE am05 (col1 number)
STORAGE (initial 100mb next 10mb pctincrease 50);

Dictionary managed Extent allocation
INITIAL - size of the first extent (100)
NEXT    - size for the next extent (10)
PCTINCREASE - (15)

-- If System TBS is Locally managed, It's not possible to create DM TBS

-- Locally managed
-- * Locally managed extents are more efficient and are recommended for all database data
-- * In a tablespace where extents are locally managed, free extents are stored in a bitmap in the tablespace
-- * As an extent is allocated to a segment or freed up because a segment was dropped, truncated, or resized, the bitmap is updated to reflect the change

CREATE TABLESPACE auto_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\auto_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL

-- Locally managed - Extent allocation: AUTOALLOCATE VS. UNIFORM
-- * AUTOALLOCATE, allocation method
-- * First 16  extents --> 64kb
-- * Next  63  extents --> 1MB
-- * Next  120 extents --> 8MB 
-- * Ramaining extents --> 64MB 

-- DROP TABLESPACE auto_tbs INCLUDING CONTENTS AND DATAFILES ;

CREATE TABLESPACE auto_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\auto_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES;

col FILE_NAME for a40
COL TABLESPACE_NAME for a15
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB
FROM DBA_DATA_FILES;

-- Create a table in the tablespace:
CREATE TABLE auto_tab (a VARCHAR2(3000))
   TABLESPACE auto_tbs;

SELECT TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLESPACE_NAME = 'AUTO_TBS';

-- See how much space is allocated to the table:
col SEGMENT_NAME for a30
SELECT SEGMENT_NAME, BYTES/1024, EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'AUTO_TAB';

-- See how many extents:
DESC DBA_EXTENTS
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'AUTO_TAB';

-- Fill up the table with data:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO AUTO_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

-- Check segment size and extents again...

-- How to drop Tablespace

DROP TABLESPACE auto_tbs
DROP TABLESPACE auto_tbs INCLUDING CONTENTS

-- but what about the datafiles ?
-- Creating the tablespace again with the [REUSE] option
CREATE TABLESPACE auto_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\auto_tbs.dbf' SIZE 25M
REUSE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

-- 1 [DROP TABLESPACE] and remove data files manually
-- 2 DROP TABLESPACE auto_tbs INCLUDING CONTENTS AND DATAFILES


CREATE TABLE [STORAGE]

CREATE TABLE mytab2 (id number)
STORAGE (INITIAL 100m);

SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'MYTAB2';

DROP TABLE MYTAB2

-- Advantages
-- 1. reduce I/O when the table is being scanned
-- 2. Improve performance when Oracle allocates the extents

-- Local - UNIFORM SIZE

DROP TABLESPACE uniform_tbs INCLUDING CONTENTS AND DATAFILES ;

CREATE TABLESPACE uniform_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\uni_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE, NEXT_EXTENT / 1024
FROM DBA_TABLESPACES;

-- Create a new table in it:
CREATE TABLE uniform_tab (a VARCHAR2(3000))
TABLESPACE uniform_tbs;

-- See how much space is allocated to the table:
SELECT SEGMENT_NAME, BYTES/1024, EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'UNIFORM_TAB';

-- See how many extents:
col segment_name for a15
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'UNIFORM_TAB';

-- Fill up the table with data:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

-- See how many extents:
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
FROM DBA_EXTENTS
WHERE segment_name = 'UNIFORM_TAB';

-- Other tablespaces:
SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES;

-- Check free space:
SELECT TABLESPACE_NAME, BYTES/1024/1024
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'UNIFORM_TBS';

RESIZE TABLESPACE
-- Fill up:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;
/

[ORA-01653: unable to extend table SYS.UNIFORM_TAB by 256 in tablespace]

-- Add a new file

ALTER TABLESPACE UNIFORM_TBS
 ADD DATAFILE  'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' size 25m;

ALTER TABLESPACE UNIFORM_TBS
 DROP DATAFILE  'C:\UNIFORM_TBS02.DBF' ;

-- Fill up:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

SELECT FILE_NAME, BYTES/1024/1024 File_Size, MAXBYTES/1024/1024 Max_Size,
AUTOEXTENSIBLE, INCREMENT_BY*8/1024 Growth
FROM dba_data_files
WHERE TABLESPACE_NAME = 'UNIFORM_TBS';

-- Fill up again:
BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;

-- Increase an existing datafile, Manual Resize  - RESIZE

ALTER DATABASE DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' RESIZE 50M;

ALTER DATABASE DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' RESIZE 30M;

SELECT FILE_NAME, BYTES/1024/1024 File_Size, MAXBYTES/1024/1024 Max_Size,
AUTOEXTENSIBLE, INCREMENT_BY*8/1024 Growth
FROM dba_data_files
WHERE TABLESPACE_NAME = 'UNIFORM_TBS';

SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb, COUNT(*) files
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME;

BEGIN
    FOR i IN 1..5000 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;
/

-- Automatic resize - AUTOEXTEND

ALTER DATABASE
 DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF'
 AUTOEXTEND ON NEXT 10m MAXSIZE 150m;

DROP TABLESPACE uniform_tbs INCLUDING CONTENTS AND DATAFILES ;

CREATE TABLESPACE uniform_tbs
 DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\uni_tbs.dbf' SIZE 25M
 AUTOEXTEND ON NEXT 10M ;

CREATE TABLE uniform_tab (a VARCHAR2(3000))
 TABLESPACE uniform_tbs;

SELECT SEGMENT_NAME, BYTES/1024, EXTENTS
 FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = 'UNIFORM_TAB';

col segment_name for a15
SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
 FROM DBA_EXTENTS
 WHERE segment_name = 'UNIFORM_TAB';

BEGIN
    FOR i IN 1..7500 LOOP
        INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*'));
    END LOOP;
END;
/

SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS
  FROM DBA_EXTENTS
  WHERE segment_name = 'UNIFORM_TAB';

col FILE_NAME for a40
COL TABLESPACE_NAME for a15
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB
FROM DBA_DATA_FILES;


ONLINE/OFFLINE  READ ONLY/READ WRITE

SELECT tablespace_name, status
FROM dba_tablespaces;

-- ONLINE/OFFLINE
-- You may want to take a tablespace offline for any of the following reasons:
-- * To make a portion of the database unavailable while allowing normal access to the remainder of the database
-- * To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)
-- * To rename or relocate tablespace datafiles
-- * When a tablespace is taken offline, the database takes all the associated files offline.
-- * You cannot take the following tablespaces offline: SYSTEM, The undo tablespace, Temporary tablespaces

ALTER TABLESPACE example OFFLINE;
ALTER TABLESPACE example ONLINE;

READ ONLY/READ WRITE

ALTER TABLESPACE example READ ONLY;
SELECT last_name from hr.employees WHERE employee_id = 100;
UPDATE hr.employees SET salary=salary;
ALTER TABLESPACE example READ WRITE ;

-- OMF Oracle-Managed Files

-- Oracle-Managed Files
-- * Using Oracle-managed files simplifies the administration of an Oracle Database.
-- * Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system -- files comprising an Oracle Database.
-- * You specify operations in terms of database objects rather than filenames.
-- * The database internally uses standard file system interfaces to create and delete files as needed.

-- Enabling the Creation and Use of Oracle-Managed Files

-- DB_CREATE_FILE_DEST
-- This initialization parameter allow Oracle use the Oracle-managed files for Datafiles
-- and Temporary files

show parameter db_create_file

ALTER SYSTEM SET db_create_file_dest = 'E:\app\Owner\oradata\orcl';

CREATE TABLESPACE tbs_3 ;
-- By default, OMF Files are 100MB, Autoextentsible (unlimited)
DROP TABLESPACE tbs_3  ;
CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M;
DROP TABLESPACE tbs_3  ;
CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M, SIZE 20M ;
DROP TABLESPACE tbs_3  ;

CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M, SIZE 20M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ALTER TABLESPACE tbs_3 ADD DATAFILE;

DROP TABLESPACE tbs_3  ;

-- Is it still possible to use Non-OMF?
CREATE TABLESPACE mytbs
DATAFILE 'C:\oracle\mytbs.dbf' SIZE 10m

-- Advantages
-- * DBAs don't need to specify file names, locations and sizes when creating a tablespace or database
-- * Automatic removal of files when a tablespace or log file is dropped
-- * Simplified creation of test and development systems

-- Disadvantages
-- * Can only be used with file systems, not with RAW Volumes
-- * Generated file names and locations might not be according to the site's naming standards
-- * Limited scope for file placement and I/O tuning may impact performance (although locations can be altered dynamically)

-- DEMO - MIGRATION
SELECT tablespace_name, block_size
FROM dba_tablespaces;

-- Create table
CREATE TABLE longrec_table (a varchar2(3000),
                            b varchar2(3000),
                            c varchar2(3000))
   TABLESPACE USERS;

BEGIN
FOR i IN 1..100 LOOP
    INSERT INTO LONGREC_TABLE(a)
     VALUES (LPAD('*',3000,'*'));
END LOOP;
END;

-- Check stats - How many blocks is the table taking and are there any migrated rows? (CHAIN_CNT):
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TABLE';

-- Still no data in dba_tables - must analyze the table:
-- Analyze
ANALYZE TABLE longrec_table COMPUTE STATISTICS;

-- Increase each row size from 3K to 6K:
UPDATE longrec_table
SET b=LPAD('*',3000,'*');

ANALYZE TABLE longrec_table COMPUTE STATISTICS;

-- Since block size is 8K, Half the rows have migrated (The first row in each block that was updated):
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TABLE';

-- Rebuild the table to fix the problem:
ALTER TABLE longrec_table MOVE TABLESPACE users;

-- ASSM - Automatic Segment Space Management - From 9i, Default from 10g
-- Managing block available for insert in bitmaps instead of free-lists (Manual)

SELECT tablespace_name, segment_space_management
FROM dba_tablespaces;

-- If the tablespace is Locally managed,
-- we can simply add the SEGMENT SPACE MANAGEMENT clause:
CREATE TABLESPACE uniform_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\uni_tbs.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
SEGMENT SPACE MANAGEMENT AUTO;

DEMO - Chaining
-- Add the third column:
UPDATE longrec_table
SET c=LPAD('*',3000,'*');

-- Analyze
ANALYZE TABLE longrec_table COMPUTE STATISTICS;

-- Check stats, and see that ALL rows are now split:
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TABLE';

-- Rebuilding the table - does not help... :
ALTER TABLE longrec_table MOVE TABLESPACE users;
-- (Check stats...)

Multiple Block Size Support
-- See current tablespaces and their block size
SELECT tablespace_name, block_size
FROM dba_tablespaces;

-- Try to create one with a block size of 16K
CREATE TABLESPACE longrec_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\longrec_tbs01.dbf' SIZE 40M
BLOCKSIZE 16K;

-- See current configured Database Buffer Caches:
SHOW PARAMETER cache_size

ALTER SYSTEM SET db_16k_cache_size=12M [scope=spfile];

CREATE TABLESPACE longrec_tbs
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\longrec_tbs01.dbf' SIZE 40M
BLOCKSIZE 16K;

-- Solve with moving the table to a tablespace with a bigger block size:
ALTER TABLE longrec_table MOVE TABLESPACE longrec_tbs;

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!