How to create a sequence and a trigger that will emulate "autonumber" in other RDBMS systems

If you have been working with other RDBMS systems, such as SQL Server from Microsoft, you may be used to a datatype called AUTONUMBER, which will automatically insert a freshly generated number and create a unique value in your table. Perfect for generation of primary key values.
In oracle, no such data type exist. To achieve the same functionality, you need to create two additional objects: a sequence and a trigger. The trigger will fire on certain events. The sequence will generate a new number. Here is a simple example on how to set it up.

CREATE TABLE ADDRESSES
(
 ADDRESS_ID NUMBER,
 EMLOYEE_ID NUMBER,
 STREET_ADDRESS VARCHAR2(40),
 STREET_ADDRESS2 VARCHAR2(40),
 CITY VARCHAR2 (30),
 STATE VARCHAR2(2),
 ZIP VARCHAR2(5),
 ZIP_PLUS VARCHAR2(4),
 COUNTRY VARCHAR2(20),
 CONSTRAINT PK_ADDRESSES PRIMARY KEY (ADDRESS_ID)-- "OUT-OF-LINE" CONSTRAINT CREATION
);

CREATE SEQUENCE ADDRESSES_SEQ
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 1000000
 NOCACHE
 NOCYCLE
 NOORDER;

CREATE OR REPLACE TRIGGER ADDRESSES_TRG
BEFORE INSERT
ON ADDRESSES
FOR EACH ROW
WHEN (NEW.ADDRESS_ID IS NULL)
BEGIN
 SELECT ADDRESSES_SEQ.NEXTVAL
 INTO   :NEW.ADDRESS_ID
 FROM   DUAL;
END;
/

How does the 11gR2 feature "deferred segment creation" work?

Starting from 11gR2 onwards, Oracle introduced a feature called deferred segment creation. This means that Oracle will not need to create any segments for a table until you actually insert rows into it.

Oracle creates all needed metadata about the new table in data dictionary, but doesn’t actually allocate any space from the tablespace. This applies to other segment types like index and table/index partitions as well.

Syntax:

-- Without deffered segment creation
CREATE TABLE TEST(tab_id number)
SEGMENT CREATION IMMEDIATE
TABLESPACE USERS;

-- With deffered segment creation
CREATE TABLE TEST (tab_id number)
SEGMENT CREATION DEFERRED
TABLESPACE USERS;


The parameter deferred_segment_creation controls the default behavior:

SQL> show parameter deferred

NAME_COL_PLUS_SHOW_PARAM     TYPE        VALUE
---------------------------- ----------- --------
deferred_segment_creation    boolean     TRUE

What is the difference between automatic segment space management and manual segment space management?

In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space: automatic and manual.

Manual segment space management uses linked lists called "freelists" to manage free space in the segment, while automatic segment space management uses bitmaps.

Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.

How to identify processes that are consuming lots of CPU power

Here is one way to identify processes that are consuming lots of CPU on your sever that I have used successfully in the past.

First, use the mpstat command, which will print the CPU statistics n times with n seconds interval.
For example, mpstat 5 5
would yield the following output:

CPU  minf  mjf  xcal  intr  ithr  csw  icsw  migr  smtx  srw  syscl  usr  sys  wt  idl 
0  1  0  0  345  224  589  220  0  0  0  799  29  1  0  70 
0  1  0  0  302  200  752  371  0  0  0  1191  99  1  0  0 
0  0  0  0  341  221  767  375  0  0  0  1301  100  0  0  0 
0  0  0  0  411  256  776  378  0 0  0  1313  99  1  0  0 
0  0  0  0  382  241  738  363  0  0  0  1163  97  3  0 0 

In the output sample above, 4 of the 5 samples have CPU 0 with

* a combined user time and system time at 100
and
* idle time at 0 (column headings usr, sys, idl).

This indicates that the CPU is completely consumed on this system.

After gathering the data from mpstat, which indicates that the system CPU resources are overtaxed, you can use prstat to identify which processes are consuming the CPU resources.

The prstat -s cpu -n 5 command is used to list the five processes that are consuming the most CPU resources.

* The -s cpu flag tells prstat to sort the output by CPU usage.
* The -n 5 flag tells prstat to restrict the output to the top five processes.

prstat -s cpu -n 5 

   PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
 25906 oracle   2692M 1912M cpu2     0    0   0:34:39 3.1% oracle/11
 25472 oracle    133M  130M cpu1     0    0   0:00:47 0.4% emagent/7
  8709 oracle     33M   19M cpu28   10    0   0:00:03 0.4% perl/1
 19404 root       88M   24M sleep   21    0 141:23:44 0.3% vxconfigd/1
  9767 root     5752K 5224K sleep   59    0   0:00:01 0.2% vmstat/1

How to find size, free space, used space, allocated space for the tablespaces in your database

SELECT 
        F.FILE_ID,
        F.FILE_NAME,
        ROUND(F.BYTES/1024/1024) "Allocated(MB)",
        NVL(TO_CHAR(ROUND(F.BYTES/1024/1024 - SUM(E.BYTES/1024/1024),1)),'File full') "Used (MB)",
        NVL(TO_CHAR(ROUND(SUM(E.BYTES/1024/1024),1)), '0') "Free (MB)",
        LPAD(DECODE(F.AUTOEXTENSIBLE,'YES', 'JA','NO','NEIN!'), 11,' ') "Autoextend?",
        LPAD(DECODE(ROUND(F.MAXBYTES/1024/1024),0,'N/A',ROUND(F.MAXBYTES/1024/1024)),15,' ') "Max size (MB)"
FROM    DBA_DATA_FILES F LEFT OUTER JOIN DBA_FREE_SPACE E
ON      E.FILE_ID = F.FILE_ID
GROUP BY F.FILE_ID,F.FILE_NAME,F.BYTES,AUTOEXTENSIBLE,MAXBYTES
ORDER BY 1;

For a specific tablespace:

alter session set nls_language='american';
SET LINES 400
COL "File ID" format 999
COL "Tablespace name" format a20
COL "File name" format a50
COL "Available space (MB)" format 9999999999
COL "Free space (MB)" format a20
COL "Percentage used" format a20
COL "Max size (MB)" format a20
COL "Can file grow?" format a20
 
WITH   free_spc AS (
                  SELECT FILE_ID, SUM(BYTES/1024/1024) AS MBFree
                  FROM DBA_FREE_SPACE fre
                  WHERE TABLESPACE_NAME = UPPER('&&tablespace')
                  GROUP BY FILE_ID
          )
SELECT  F.FILE_ID "File ID",
             F.TABLESPACE_NAME "Tablespace name",
             F.FILE_NAME "File name",
             ROUND(F.USER_BYTES/1024/1024) "Available space (MB)",
             NVL( TO_CHAR(ROUND(FS.MBFree)), 'NADA') "Free space (MB)",
             LPAD(NVL(ROUND(( ROUND(F.BYTES/1024/1024 - FS.MBFree)/ROUND(F.BYTES/1024/1024) )  * 100),100),15,' ') || ' %' "Percentage used",
             LPAD(DECODE(F.AUTOEXTENSIBLE,'YES', 'JA','NO','NEIN!'), 11,' ') "Can file grow?",
             LPAD(DECODE(ROUND(F.MAXBYTES/1024/1024),0,'N/A',ROUND(F.MAXBYTES/1024/1024)),15,' ') "Max size (MB)"
FROM DBA_DATA_FILES F LEFT JOIN free_spc FS
ON F.FILE_ID = FS.FILE_ID
WHERE F.TABLESPACE_NAME = UPPER('&&tablespace')
ORDER BY F.FILE_ID ASC;

How to compare the content of memory parameter and spfile parameters

### Compare static (pfile) and dynamic (spfile) parameter setting:

set pages 100
set lines 400
col init_value format a36
col spfile_value format a36
col name format a38
SELECT P.NAME, P.VALUE INIT_VALUE, S.VALUE SPFILE_VALUE 
FROM   V$PARAMETER P 
    JOIN V$SPPARAMETER S ON P.NAME=S.NAME 
    WHERE P.ISBASIC='TRUE' 
    ORDER BY NAME;

How to clean away control character from a text file

I found this little example on the internet and saved it as a shorthand - it has come in handy many times. Unfortunately I have forgotten the name of the original author.

When files are sent from windows to unix, you will sometimes see that the when you open the files in a unix text editor has ^M's in them.

For example

------------------------------------------------------------------
import java.util.Hashtable; ^M
import java.util.Properties; ^Mimport java.io.IOException;
import org.xml.sax.AttributeList; ^M
import org.xml.sax.HandlerBase; ^Mimport org.xml.sax.SAXException;

/**^M
  * XMLHandler: This class parses the elements contained^M
  * within a XML message and builds a Hashtable^M

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

Notice that some programs are not consistent in the way they insert the line breaks so you end up with some lines that have both a carrage return and a ^M and some
lines that have a ^M and no carrage return (and so blend into one). There are two steps to clean this up.

1. replace all extraneous ^M:

:%s/^M$//g

BE SURE YOU MAKE the ^M USING "CTRL-V CTRL-M" NOT BY TYPING "CARROT M"! This expression will replace all the ^M's that have carriage returns
after them with nothing. (The dollar ties the search to the end of a line)

2. replace all ^M's that need to have carriage returns:

:%s/^M/ /g

Once again: BE SURE YOU MAKE the ^M USING "CTRL-V CTRL-M" NOT BY TYPING "CARROT M"! This expression will replace all the ^M's that didn't have
carriage returns after them with a carriage return.

Voila! Clean file. Map this to something if you do it frequently.