How to check if the database is started using spfile or pfile?

Way 1: If the following command returns a value then the database has been started using a spfile, else pfile was used during startup.

SQL> show parameter spfile
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
spfile                               string

Way 2: Alter the system using the scope=spfile clause, if it returns error then pfile is used. Please see the following example.

SQL> alter system set open_cursors=300 scope=spfile;
alter system set open_cursors=300 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

How to set column width in sqlplus?

Step1: Make a note of the columns you would like to set the width for.

Step2: Use the following syntax to set the column widths:
For varchar2 columns

SQL> column <column_name> format a<width no.>
For numeric columns

SQL> column <column_name> format <width no.>

Creating Database manually

Note: This article is biased towards Windows

Step 1: Specifying the Instance's SID
I:\app\WELCOME\product\11.1.0>set ORACLE_SID=PREETI

Step 2: Creating the initialization parameter file.

db_name='PREETI'
memory_target=1G
processes = 150
#audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
#audit_trail ='db'
db_block_size=8192
#db_domain=''
#db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
#db_recovery_file_dest_size=2G
diagnostic_dest='I:\app\WELCOME\oradata\PREETI'
#dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
os_authent_prefix=''
undo_management=auto
undo_tablespace=undotbs1
undo_retention=900
control_files = (I:\app\WELCOME\oradata\PREETI\control\control1.ctl,
                 I:\app\WELCOME\oradata\PREETI\control\control2.ctl)
compatible ='11.1.0'

Step 3: Starting the Instance

I:\app\WELCOME\product\11.1.0\db_1>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 29 20:45:06 2011

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

Connected to an idle instance.

SQL> startup nomount pfile=I:\app\WELCOME\product\11.1.0\db_1\dbs\initPREETI.ora

ORACLE instance started.

Total System Global Area  644468736 bytes

Fixed Size                  1335108 bytes

Variable Size             176160956 bytes

Database Buffers          461373440 bytes

Redo Buffers                5599232 bytes

Step 4: Database Creation

SQL>   create database PREETI

 logfile    group 1 ('I:\app\WELCOME\oradata\PREETI\redo\redo1a.log', 

                            'I:\app\WELCOME\oradata\PREETI\redo\redo1b.log') size 10M,

              group 2 ('I:\app\WELCOME\oradata\PREETI\redo\redo2a.log',

                            'I:\app\WELCOME\oradata\PREETI\redo\redo2b.log') size 10M

 national character set utf8

 datafile                'I:\app\WELCOME\oradata\PREETI\datafiles\system.dbf'

           size 50M

           autoextend on

           next 10M

           maxsize UNLIMITED

 undo tablespace undotbs1

          datafile       'I:\app\WELCOME\oradata\PREETI\datafiles\undotbs1.dbf'

          size 10M

 default temporary tablespace temp

          tempfile       'I:\app\WELCOME\oradata\PREETI\datafiles\temp.dbf'

          size 10M

 sysaux datafile      'I:\app\WELCOME\oradata\PREETI\datafiles\sysaux.dbf'

          size 10M

          autoextend on

          next 10M

          maxsize UNLIMITED;

Database created.

Step 5: Completing the Database Creation

SQL> @?/rdbms/admin/catalog.sql

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catproc.sql

SQL> @?/rdbms/admin/pupbld.sql

Errors while creating the Database:

ORA-01092: ORACLE instance terminated. Disconnection forced

Solution: the alert log should be checked for the errors occurred during the database creation and act accordingly.

Ways to check the version of an Oracle Database

WAY 1 (Using the v$version view)
SQL> select * from v$version;


BANNER
-------------------------------------------------------------------------------


Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

WAY 2 ( When you connect to the database)

C:\Documents and Settings\WELCOME>sqlplus '/ as sysdba'

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 29 18:44:08 2011

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

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Manually dropping an Oracle Database

Export the Oracle SID before dropping it, making sure you are working on that required database.

Follow the steps as below:

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1334380 bytes

Variable Size             167773076 bytes

Database Buffers          360710144 bytes

Redo Buffers                5844992 bytes

Database mounted.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Background Process Description

Process Monitor(PMON)

1. Process Monitor is an Oracle background process created when you start a database instance.
2. The process will free up resources if a user process fails.
3. It normally wakes up every 3 seconds to perform its housekeeping activities.
4. Pmon must always be running else the instance terminates.
5. To speed up the housekeeping the PMON can be manually wokeup as below:

           SQL> oradebug setmypid

           SQL> oradebug wakeup 2

           Assuming, PMON as process 2 here.

System Monitor(SMON)

1. Performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents and also monitors temporary segments.
2. Wakes up every 5 miuntes to perform activities.
3. In a non-failed instance can also perform failed instance recovery for other failed RAC instances.
4. In case of tablespace offline errors or any as such, the transactions are eventually recovered by SMON when taablespace or file is brought back online.
5. To check if the SMON process is running:

                      $ ps -ef | grep smon

                      oracle   31144     1  0 11:10 ?        00:00:00 ora_smon_orcl

 Archiver's Process (ARCH)

1. Created when you start an instance in ARCHIVE LOG MODE.
2. Writes filled redo logs to the archive log locations.
3. An instance can have upto 10 ARCn processes (ARC0 to ARC9).
4. The LGWR starts a new ARCn whenever current number of processes is insufficient. The alert file keeps a track of these.
5. The parameter used is: LOG_ARCHIVE_MAX_PROCESSES.

Log Writer (LGWR)
Created when you start the instance.
Writes the redo log buffers to the on-line redo log files.
If these files are mirrored, all the members of the group will be written out simultaneously.
LGWR writes:
A commit record when a user process commits a transaction.
Redo log buffers
Every three seconds
When the redo log buffer is one-third full.
When a DBWn process writes modified buffers to disk, if necessary.

Oracle DB Block Structure within the Database

Click on the image for a larger view

Row Migration and Row Chaining

Basic definitions for the impatient:

Row Chaining: Distribution of a single table row across multiple data blocks.

Row Migration: Relocation of an entire table row to a new place, without updating the indexes.

Row movement: Relocation of an entire table row to a new place and updating the indexes.

Please note: We are going to discuss only about Row Migration and Chaining and will discuss Row movement in other articles.

What is Row Migration and Row chaining?

Row Migration

·         Row migration refers to rows that were moved to another block due to an update making them too large to fit into their original blocks. Oracle will leave a forwarding pointer in the original block so indexes will still be able to find the row.

·         The rowid of the migrated row does not change. Thus, ROWID that is stored in the index still refers to the old location of the row. An additional block, the new location of the row, must be read to fetch the required data.

Row Chaining:

A chained row is a row that is too large to fit into a single database data block.

For example, if you use a 4KB blocksize for your database, and you need to insert a row if 8KB into it, Oracle will use 3 blocks and store the row in pieces.

Conditions that cause row chaining are:

•          Tables whose row size exceeds the blocksize.
•          Tables with long and long raw columns are prone to having chained rows.
•          Tables with more than 225 columns will have chained rows as Oracle break wide tables up into pieces.

Row chaining in such cases is unavoidable.

Few points about Row Migration and Row chaining.

• Oracle does not discriminate between chained and migrated rows, even though they have different causes.
•  When a row is chained or migrated, performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for that row.
• SELECTs that use an index to select migrated and chained rows must perform additional I/Os.

How do you detect Row Chaining and Row Migration?

Oracle has provided three ways to detect the above.

1. .       The ANALYZE command
2. .       The dynamic views
3. .       Report.txt method

The ANALYZE command

1.       Before doing this ANALYZE, create a table that can hold the chained rows.

2.       Execute UTLCHAIN.sql or UTLCHN1.sql  script found in $ORACLE_HOME/rdbms/admin/ directory. This creates the CHAINED_ROWS table.

cd $ORACLE_HOME/rdbms/admin

sqlplus scott/tiger

@utlchain.sql

3.       Else, create a similar table with same column and data types and sizes as the CHAINED_ROWS table.

create table CHAINED_ROWS (

  owner_name         varchar2(30),

  table_name         varchar2(30),

  cluster_name       varchar2(30),

  partition_name     varchar2(30),

  subpartition_name  varchar2(30),

  head_rowid         rowid,

  analyze_timestamp  date

);

4.       Analyze the table now as below:

ANALYZE TABLE <table_name> LIST CHAINED ROWS [INTO CHAINED_ROWS];

5.       View the Chained rows using the SQL statement as below:

SELECT owner_name,

table_name,

head_rowid

FROM chained_rows

OWNER_NAME                     TABLE_NAME                     HEAD_ROWID

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

SCOTT                          ROW_MIG_CHAIN_DEMO             AAAPVIAAFAAAAkiAAA

SCOTT                          ROW_MIG_CHAIN_DEMO             AAAPVIAAFAAAAkiAAB

Using the Dynamic Views

1.       V$sysstat tells how many times, since the system was started the rows are chained or migrated.

sqlplus system/<password>

SELECT 'Chained or Migrated Rows = '||value

  FROM v$sysstat

 WHERE name = 'table fetch continued row';

Chained or Migrated Rows = 31637

Note: You could have 1 table with 1 chained row that was fetched 31637 times or you could have 31637 tables, each with a chained row, each of which was fetched once. Any combo can work.

2.       User_Tables tells immediately after ANALYZE (will be null otherwise) how many rows in the table are chained.

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

SELECT chain_cnt,

       round(chain_cnt/num_rows*100,2) pct_chained,

       avg_row_len, pct_free , pct_used

  FROM user_tables

WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED

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

         3         100        3691         10         40

3.       Dba_tables show how many chained (and migrated) rows each table has:

SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt>0;

Using the REPORT.txt

1.       We can create a report.txt file using the utlbstat.sql and utlestat.sql scripts.

2.       Now, check the statistic named ‘table fetch continuous row’ in the report.txt file.

Simply, How to check the number of rows chained or migrated in a table?

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

SELECT chain_cnt

  FROM user_tables

 WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 CHAIN_CNT

----------

         3

How to avoid row chaining and migration?

Increasing PCTFREE can help to avoid migrated rows.

ALTER TABLE …. MOVE

Enables you to relocate data into a new segment, and optionally into a different tablespace for which you have quota.

This statement also lets you modify any of the storage attributes of the table or partition, including those which can not be modified using ALTER TABLE.

Procedure:

First count the number of rows per block before the ALTER TABLE MOVE.

SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"

  FROM row_mig_chain_demo

GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;

 Block-Nr        Rows

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

      2066          3

Now de-chain the table, the ALTER TABLE MOVE rebuild the table in a new segment.

ALTER TABLE row_mig_chain_demo MOVE

   PCTFREE 20

   PCTUSED 40

   STORAGE (INITIAL 20K

            NEXT 40K

            MINEXTENTS 2

            MAXEXTENTS 20

            PCTINCREASE 0);

Table altered.

Again count the number of Rows per block.

SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"

  FROM row_mig_chain_demo

GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;

 Block-Nr        Rows

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

      2322          1

      2324          1

      2325          1

Now, Rebuild the indexes for the Table.

Moving a table changes the row ids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error.

The indexes on the table must be dropped or re-built..

Likewise, any statistics become invalid and new statistics should be collected.

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

ERROR at line 1:

ORA-01502: index 'SCOTT.SYS_C003228' or partition of such index is in unusable

state

ALTER INDEX SYS_C003228 REBUILD;

Index altered.

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

Table analyzed.

SELECT chain_cnt,

       round(chain_cnt/num_rows*100,2) pct_chained,

       avg_row_len, pct_free , pct_used

  FROM user_tables

 WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED

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

         1       33.33        3687         20         40

A temporary solution which can avoid the migrated rows is as below:

1.       Analyze the table to get the row id.

2.       Copy those rows to a temporary table.

3.       Delete the rows from the original table.

4.       Insert into rows from step2 back to the original table.

  

partial content from : http://www.akadia.com/services/ora_chained_rows.html