What do do if SET SERVEROUTPUT ON doesn't work

If you have an anonymous piece of pl/sql code and you have turned on serveroutput, like this:

set serveroutput on

You may need to enable output first:

exec dbms_output.enable();
set serveroutput on

In a multitenant environment, you will need to enable serveroutput after every time you switch container.

So, if you do

BEGIN
V_SQLSTRING := 'ALTER SESSION SET CONTAINER=' || '' || V_PDBNAME || '';
DBMS_OUTPUT.PUT_LINE(V_SQLSTRING);
EXECUTE IMMEDIATE V_SQLSTRING;
END;
/

you will need to repeat the enabling of serveroutput again, if you want to perform addition actions further down in your script.

Simple PL/SQL script to alter parameter in database

At my current workplace, we use emcli for mass-updates of database parameters in groups of databases.

Below is a simple PL/SQL script that can be used to change parameter if needed. If the parameter is already set, nothing happens.

In my case, it was the parameter "recyclebin" that was needed to be switched from OFF to ON for some databases, but it could be any parameter.

Surely there are many ways to solve such a problem, this was how I solved it with very little effort :-)

set serveroutput on
set feedback off
set echo off
set verify off

DECLARE

 v_db_name v$database.name%%TYPE;
 v_rb v$system_parameter.name%%TYPE;


BEGIN
 execute immediate 'alter session set nls_language=''american''';
 select name
 into v_db_name
 from v$database;

 select value
 into v_rb
 from v$system_parameter
 where name = 'recyclebin';
 
--dbms_output.put_line('v_db_name: ' || v_db_name);
--dbms_output.put_line('v_rb:      ' || v_rb);

CASE
   WHEN v_rb = 'ON' THEN
    dbms_output.put_line('Database ' || v_db_name || ': recyclebin already enabled.');
   ELSE
    dbms_output.put_line('Database ' || v_db_name || ': recyclebin is currently disabled. Turning it on now...');
    execute immediate 'alter system set recyclebin=ON scope=spfile';
     dbms_output.put_line('Database ' || v_db_name || ' now has recyclebin=ON in spfile. Database must be bounced in in order to enable the setting');
   END CASE;
END;
/
exit

When we execute the script through emcli, the syntax comes to:

emcli execute_sql -sql="FILE" -iemcli execute_sql -sql="FILE" -input_file="FILE:/scripts/chk_and_alter_param.sql" -targets="TEST_DBS:composite"

How to change an Oracle instance parameter based on database version and container type

Here is a simple pl/sql script that will set parameters based on version and container type.

set serveroutput on
DECLARE

v_version     sys.v_$instance.version%type;
v_param_value sys.v_$system_parameter.value%type;
v_pdb boolean;
v_con_id number;
v_con_type varchar(7);

BEGIN

-- find current setting of optimizer_dynamic_sampling
select value
into   v_param_value
from   v$parameter
where  name = 'optimizer_dynamic_sampling';

-- find current(major) version
select version
into   v_version
from   sys.v_$instance;

-- find container type
select 
    case when (sys_context('USERENV','CON_ID') = 0 ) then 'NON-CDB' 
       when (sys_context('USERENV','CON_ID') = 1 ) then 'CDB'
       when (sys_context('USERENV','CON_ID') > 1 ) then 'PDB'
    end
into v_con_type
from DUAL;

--dbms_output.put_line('v_con_type: ' || v_con_type);
--dbms_output.put_line('version: ' || v_version);
--dbms_output.put_line('optimizer_dynamic_sampling: ' || v_param_value);

v_pdb := FALSE;

IF v_con_type = 'PDB' THEN
  v_pdb := TRUE;
ELSE
  v_pdb := FALSE;
END IF;


CASE WHEN v_version IN ('18.0.0.0.0','19.0.0.0.0') AND v_param_value = '0' THEN
    dbms_output.put_line('version is >= 18 and optimizer_dynamic_sampling = 0');
    
    IF v_pdb = FALSE THEN
      dbms_output.put_line('execute immediate alter system set optimizer_dynamic_sampling=4 scope=both;');
      execute immediate 'alter system set optimizer_dynamic_sampling=4 scope=both';
    ELSIF v_pdb = TRUE THEN
      dbms_output.put_line('execute immediate alter system set optimizer_dynamic_sampling=4 container=current scope=both;');
      execute immediate 'alter system set optimizer_dynamic_sampling=4 container=current scope=both';
    END IF;
    
WHEN v_version IN ('18.0.0.0.0','19.0.0.0.0') AND v_param_value <> '0' THEN   
     dbms_output.put_line('version is >= 18 and optimizer_dynamic_sampling is already set.');
ELSE
  dbms_output.put_line('version is < 18');
END CASE;

END;
/

How to bulk-grant privileges to a selection of users using PL/SQL

This anonymous PL/SQL script will select all the desired users, and then grant SELECT on a number of performance views to these.

set serveroutput on

DECLARE

 CURSOR c1 IS
  select username
  from dba_users
  where username like 'IT%'
  or username like 'MAITD%';

BEGIN
 FOR x IN c1 LOOP
    dbms_output.put_line('user ' || x.username || ' processed.' );
    execute immediate('GRANT SELECT ON SYS.IND$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.OBJ$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.TAB$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.USER$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$DATABASE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$INSTANCE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$LATCH TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$LIBRARYCACHE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$MYSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$PROCESS TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$ROWCACHE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESSION TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESS_IO TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SGASTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$STATNAME TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SYSSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SYSTEM_EVENT TO ' || x.username);
 END LOOP;
END;
/

How to check for table partitions using PL/SQL

I created the following piece of code a long time ago.
It checks if a table is partitioned or not, and list its partition.
It takes the table name and its owner as parameters, and list it.

If you need a template for a simple script using an anonymous PL/SQL block, I assume it could serve a purpose.

For what it's worth, here it is:

set serveroutput on
DECLARE
  p_table_name  varchar2(100) := '&table_name';
  p_table_owner varchar2(100) := '&owner';
  p_count       number        := 0;
BEGIN
    SELECT count(*) into p_count
    FROM   dba_tab_partitions
    WHERE  table_name   = p_table_name
    AND    table_owner  = p_table_owner;
  IF (p_count = 0) THEN
    dbms_output.put_line('There were no partitions on table ' || p_table_name );
  ELSE
     For l_rec in ( 
     
        select p.owner, p.table_name, t.partition_name, t.TABLESPACE_NAME ,p.partitioning_type
        from  dba_part_tables p, dba_tab_partitions t
        where p.table_name = p_table_name
        and   p.owner = t.TABLE_OWNER
        and   t.TABLE_NAME = p.TABLE_NAME
        )
     LOOP
     dbms_output.put_line(l_rec.partition_name || '    ' || l_rec.tablespace_name || '  ' || l_rec.partitioning_type);
     END LOOP;
  END IF;
END;
/

How to use ROWTYPE to fetch entire rows for insert into an interim table

Here is how I used a PL/SQL record to insert rows from a table into an interim table, using parallel slaves and direct path:

alter session force parallel dml;
alter session force parallel query;

DECLARE
  --declare a table-based record for the table "document"
  v_document_rec document%ROWTYPE;

  CURSOR document_cur IS
    SELECT *
    FROM document
    WHERE trunc(created_date) between TO_DATE('01.01.2017','dd.mm.yyyy') AND to_date(to_char(SYSDATE,'dd.mm.yyyy'))
    FETCH FIRST 100000 ROWS ONLY;

   -- declare a cursor-based record
   v_document_rec document_cur%ROWTYPE;

BEGIN
   OPEN document_cur;
   LOOP
   -- fetch the content of the cursor into the record
   FETCH document_cur into v_document_rec;
   EXIT WHEN document_cur%NOTFOUND;
   INSERT /*+ APPEND PARALLEL 8 */ INTO document_interim VALUES( v_document_rec.ID,
                                                 v_document_rec.document_id,
                                                 v_document_rec.document_name,
                                                 v_document_rec.created_date,
                                                 v_document_rec.security_level,
                                                 v_document_rec.content );
   END LOOP;
   COMMIT;
   CLOSE document_cur;
END;
/

What is the package SYS.USER_LOCK?

Recenty I was migrating a database with a package that wouldn't compile because it couldn't find the package called upon in its code:

SQL> ALTER PACKAGE mypackage compile body; 

Warning: Package body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY MVA3.MYPACKAGE:

PLS-00201: identifier 'USER_LOCK.SLEEP' must be declared

According to PSOUG, there are "four separate ways to induce a sleep into PL/SQL."

•dbms_backup_restore
•dbms_drs
•dbms_lock
•user_lock


PSOUG continues: "Note that USER_LOCK package is not part of the default Oracle installation"

Install as follows:

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba @userlock.sql

How to create a stored procedure for partition maintenance, executed by the Oracle Scheduler.

I needed to setup a small job that would remove partitions from a table with records older than 35 days.
The procedure should be flexible enough to accept different tables in different schemas, and there should be some basic logging, too.

Below is how I solved it.


First, grant permission, setup the logging table and add a sequence:

connect / as sysdba
grant select on dba_tab_partitions to scott;

connect scott/tiger

whenever sqlerror continue
drop sequence part_maint_seq;

create sequence part_maint_seq
increment by 1
start with 1
nomaxvalue
nominvalue
nocycle;

drop table partition_maintenance;

create table partition_maintenance(
    opid           number not null,
    table_owner    varchar2(8),
    table_name     varchar2(30),
    partition_name varchar2(30),
    operation      varchar2(10),
    success        char(1),
    exe_date       date,
    exec_by        varchar2(30) default 'automatic',
    errormsg       varchar2(512)
);

alter table partition_maintenance
add constraint part_maint_pk primary key (opid);

Then, create the stored procedure that will do the job. It requires two parameters to be passed to it: table owner and table name. Note that the procedure will need to be called with invoker's right, since it is accessing DBA_TAB_PARTITION:

connect scott/tiger

set serveroutput on

CREATE OR REPLACE PROCEDURE DROP_PARTITIONS ( towner IN VARCHAR2, tname IN VARCHAR2) AUTHID CURRENT_USER AS

  PNAME DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE;
  L_DATA LONG;
  V_HVD DATE;
  V_PURGE_DATE DATE;
  SQLTORUN VARCHAR2(200);

  CURSOR PART_NAME
  IS
  SELECT PARTITION_NAME
    FROM DBA_TAB_PARTITIONS
    WHERE TABLE_NAME =  UPPER(tname)
    AND   TABLE_OWNER = UPPER(towner)
    AND   INTERVAL='YES'
    ORDER BY partition_position;

BEGIN

    OPEN PART_NAME;
    FETCH PART_NAME INTO PNAME;
    IF PART_NAME%ROWCOUNT = 0 THEN
        dbms_output.put_line('no such table as ' || UPPER(towner) ||'.'||UPPER(tname) );
            INSERT INTO PARTITION_MAINTENANCE (OPID, TABLE_OWNER,TABLE_NAME,PARTITION_NAME, OPERATION, SUCCESS, EXE_DATE, ERRORMSG)
                VALUES (PART_MAINT_SEQ.NEXTVAL,UPPER(towner),UPPER(tname),PNAME ,'DROP','N', SYSDATE, 'No table such as '|| towner || '.' || tname );
                COMMIT;
    END IF;
    WHILE PART_NAME%FOUND
        LOOP
            --DBMS_OUTPUT.PUT_LINE( pname );
            SELECT HIGH_VALUE
            INTO L_DATA
            FROM DBA_TAB_PARTITIONS
            WHERE TABLE_OWNER= UPPER(towner)
            AND   TABLE_NAME = UPPER(tname)
            AND PARTITION_NAME = PNAME;

            --dbms_output.put_line('partition name: ' || pname || ' ' ||  trim (substr ( l_data, 12, 10 )  ) );
             V_HVD := TO_DATE(TRIM (SUBSTR ( L_DATA, 12, 10 ))  ,'YYYY-MM-DD');
            -- dbms_output.put_line('high value date: ' || v_hvd);
           V_PURGE_DATE := TRUNC(SYSDATE)-35;
            --dbms_output.put_line('purge date: ' || v_purge_date);
            IF V_HVD < V_PURGE_DATE THEN
               DBMS_OUTPUT.PUT_LINE('partition ' || PNAME ||' too old, high value date for partition is: ' || V_HVD);
               -- drop statement goes here
               SQLTORUN := 'alter table ' || towner || '.' || tname || ' drop partition ' || PNAME || ' UPDATE INDEXES';
               DBMS_OUTPUT.PUT_LINE(SQLTORUN);
               EXECUTE IMMEDIATE SQLTORUN;
               -- logging to a separate table goes here
               INSERT INTO PARTITION_MAINTENANCE (OPID, TABLE_OWNER,TABLE_NAME,PARTITION_NAME, OPERATION, SUCCESS, EXE_DATE, ERRORMSG)
               VALUES (PART_MAINT_SEQ.NEXTVAL,UPPER(towner),UPPER(tname),PNAME ,'DROP','Y', SYSDATE, 'Partition ' || pname || ' of table ' || towner || '.' || tname || ' was dropped.');
               COMMIT;
            END IF;
            FETCH PART_NAME INTO PNAME;
        END LOOP;

    CLOSE PART_NAME;

END;
/

Finally, create a program of type "STORED_PROCEDURE", define its arguments, and create a job that executes the program on a regular basis:

whenever sqlerror continue
set serveroutput on

BEGIN
DBMS_SCHEDULER.DROP_JOB(
    job_name        => 'DROP_PARTITION_5WEEK'
  );
  DBMS_SCHEDULER.DROP_PROGRAM(
     program_name       => 'DROP_PARTITION_PROG'
  );
  DBMS_SCHEDULER.create_program (
    program_name        => 'DROP_PARTITION_PROG',
    program_type        => 'STORED_PROCEDURE',
    program_action      => 'DROP_PARTITIONS',
    number_of_arguments => 2,
    enabled             => FALSE,
    comments            => 'Program to drop partitions with contents older than 35 days..');

  DBMS_SCHEDULER.define_program_argument (
    program_name      => 'DROP_PARTITION_PROG',
    argument_name     => 'towner',
    argument_position => 1,
    argument_type     => 'VARCHAR2',
    default_value     => 'SCOTT');

  DBMS_SCHEDULER.define_program_argument (
    program_name      => 'DROP_PARTITION_PROG',
    argument_name     => 'tname',
    argument_position => 2,
    argument_type     => 'VARCHAR2',
    default_value     => 'SALES');

  DBMS_SCHEDULER.enable( name => 'DROP_PARTITION_PROG');

  DBMS_SCHEDULER.CREATE_JOB
    (
    job_name        => 'DROP_PARTITION_5WEEK',
    program_name    => 'DROP_PARTITION_PROG',
    start_date      => TO_TIMESTAMP_TZ('2015/05/26 21:45:00.000000 +02:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
    repeat_interval => 'FREQ=WEEKLY;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job will execute the program drop_partitions.');
END;
/

How to convert stored outlines to use SQL Plan Baselines

If you have been using stored outlines for plan stability in versions prior to Oracle 11g, you should migrate them to use SQL Plan Baselines instead. Stored outlines is, according to oracle, "a legacy technique for providing plan stability".

My database had a number of stored outlines:

SELECT OWNER, CATEGORY, USED, MIGRATED,COUNT(*) 
FROM   DBA_OUTLINES
GROUP BY OWNER,CATEGORY,USED,MIGRATED
ORDER BY MIGRATED;

OWNER	CATEGORY	USED	MIGRATED	COUNT(*)
USER1	DEFAULT	USED	MIGRATED	3
USER2	DEFAULT	USED	MIGRATED	1
USER3	DEFAULT	USED	NOT-MIGRATED	7
USER1	DEFAULT	USED	NOT-MIGRATED	7
USER4	DEFAULT	USED	NOT-MIGRATED	1
USER2	DEFAULT	USED	NOT-MIGRATED	36

I created the following pl/sql to convert them to SQL Plan Baselines using the package DBMS_SPM.MIGRATE_STORED_OUTLINE:

SET TRIMSPOOL ON
SET LINES 200
SET PAGES 200
ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; <-- to get English messages during execution
SPOOL CONVERT_OUTLINES.LOG
SET SERVEROUTPUT ON
DECLARE
  L_CLOB            CLOB; -- will display the resulting report

  CURSOR C1 IS
    SELECT OWNER,NAME
    FROM DBA_OUTLINES
    WHERE MIGRATED = 'NOT-MIGRATED';

    C1_REC C1%ROWTYPE;

 BEGIN
    DBMS_OUTPUT.ENABLE( 1000000 );

    IF NOT C1%ISOPEN THEN
       OPEN C1;
    END IF;
 
     LOOP
       FETCH C1 INTO C1_REC;
       EXIT WHEN C1%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('Now converting: ' || C1_REC.OWNER || '.' || C1_REC.NAME);
       L_CLOB := DBMS_SPM.MIGRATE_STORED_OUTLINE( ATTRIBUTE_NAME=>'OUTLINE_NAME', ATTRIBUTE_VALUE=>C1_REC.NAME, FIXED=>'NO');
       DBMS_OUTPUT.PUT_LINE( L_CLOB );
     END LOOP;
    CLOSE C1;
END;
/
EXIT

The resulting log files shows that several of the stored outlines could not be converted:

Now converting: USER2.SYS_OUTLINE_11021513055564321
-------------------------------------------------------------------------------

               Migrate Stored Outline to SQL Plan Baseline

Report
-------------------------------------------------------------------------------

Summary:
--------

Number of stored outlines to be migrated: 1
Stored outlines migrated successfully: 0
Stored outlines failed to be migrated: 1

Summary of Reasons for failure:
-------------------------------

Number of invalid stored outlines: 1

Details on stored outlines not migrated or name changes during migration:
-------------------------------------------------------------------------

* Notes on name change:
* New SQL plan baselines are assigned the same names as their original stored 
* outlines. If a stored outline has the same name as an existing
* SQL plan baseline, a system generated name is used for the new
* SQL plan baseline.

I then checked with the developers. It turned out that the outlines that didn't convert properly were remnants from the database when it was totally different laid out - the schemas had by now diverged and I could simply ignore these obsolete outlines.

So the last step was simply to generate a drop-script for the non-migrated outlines and then execute these:

SELECT 'DROP OUTLINE ' || NAME || ';'
FROM DBA_OUTLINES
WHERE MIGRATED = 'NOT-MIGRATED';

A convenient way to declare variables in PL/SQL

In PL/SQL programming, a convenient (and programmatically safe) way to specify data types for your variables is to let them be inherited from the table columns:

v_tab_name DBA_TABLES.TABLE_NAME%TYPE;
v_tabspc_name DBA_TABLES.TABLESPACE_NAME%TYPE;

How to save a value in a bind variable

Useful for tracing or explaining queries that use bind variables:

connect scott/tiger
var leom char(10);
var eod char(10);

exec :leom := to_date('30.09.2013','DD.MM.YYYY');
exec  :eod := to_date('31.10.2013','DD.MM.YYYY');

SELECT col1, col2 ....col n
FROM   TABLE1
WHERE  START_DATE <= :eod
AND    END_DATE   <= :leom;

How to use the DBMS_FILE_TRANSFER.PUT_FILE procedure

BEGIN
  SYS.DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object      => 'DPUMP',
   source_file_name             => 'myfile.txt',
   destination_directory_object => 'REMOTE_DPDUMP',
   destination_file_name        => 'myfile.txt',
   destination_database         => 'REFRESH.MYDOMAIN.COM');
END;
/

Used in a script:

export DMP_NAME=`echo $1 | tr '[a-z]' '[A-Z]'`
export DPDIR=`echo $4 | tr '[a-z]' '[A-Z]'`

#####################################################
# Transfer to remote server using DBMS_FILE_TRANSFER
#####################################################

cat << EoF > ${DBA_ADMIN}/sql/copy_file.sql
set trimspool on
spool copy_file.log
Prompt Transferring Dumpfiles;
define file_name=&1


BEGIN
  SYS.DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object      => 'DPUMP',
   source_file_name             => '&file_name',
   destination_directory_object => '${DPDIR}',
   destination_file_name        => '&file_name',
   destination_database         => 'REFRESH.MYDOMAIN.COM');
END;
/
exit
EoF

for dmpfile in $(ls /oracle/datapump/${DMP_NAME}_*.dmp); do
 file_name=`echo $dmpfile | cut -d / -f 7`

sqlplus -s / as sysdba @${DBA_ADMIN}/sql/copy_file.sql ${file_name} > ${BATCHDIR}/file_name.log 2> ${BATCHDIR}/file_name.err &
done
wait

rm -f  ${DBA_ADMIN}/sql/copy_file.sql

How to use SYS_CONTEXT to display user information

Oracle provides a built-in namespace called USERENV, which describes the current session.

The function SYS_CONTEXT can be used to return the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

You can use the SYS_CONTEXT to retreive userinformation from the namespace USERENV, some examples below:

SELECT SYS_CONTEXT ('userenv','OS_USER') "OS user", 
       SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
       SYS_CONTEXT('userenv','IDENTIFICATION_TYPE') "Identification type",
       SYS_CONTEXT('userenv','IP_ADDRESS') "IP",
       SYS_CONTEXT('userenv','HOST') "Host name",
       SYS_CONTEXT('userenv','SID') "SID",
       SYS_CONTEXT('userenv','SERVICE_NAME') "Service"
FROM DUAL;

Executed from a remote session, logged into the database as user SCOTT, the output from the query above will be:

OS user	Current schema	Identification type	IP	Host name	SID	Service
SCOTT	SCOTT	LOCAL	192.168.1.2	MYDOMAIN\MYCLIENT	170	myservice.mydomain.com

To find the serial# of your session, you will need to query the v$session dynamic performance view. you need to have SELECT privileges on the V$SESSION view to use this query, so first, a grant is needed.

SQL> show user
USER is "SYS"

GRANT SELECT ON V_$SESSION TO SCOTT;

Now, as user SCOTT, you can execute the query

SELECT SID, SERIAL#
FROM V$SESSION
WHERE SID=SYS_CONTEXT('USERENV','SID');


Source: Oracle Documentation The 12.2 SYS_CONTEXT documentation can be found here

How to retreive information about CPUs on a Solaris server

For analytic purposes when dealing with installations, system validations, database design and performance tuning, you will often need to know hard facts about the CPUs of a server hosting an oracle database. Here are some of my notes on the matter.

Use the psrinfo utility. The flag -p displays the number of physical processors, and the -v flag is for verbose output:

oracle@host1[PRODDB11] psrinfo -pv
The physical processor has 8 virtual processors (0-7)
SPARC64-VII (portid 1024 impl 0x7 ver 0x91 clock 2400 MHz)
The physical processor has 8 virtual processors (8-15)
SPARC64-VII (portid 1032 impl 0x7 ver 0x91 clock 2400 MHz)

Without any argument, psrinfo prints a tabular output of the CPUs(or cores), as follows:
oracle@host1[PRODDB11] psrinfo
0 on-line since 07/18/2011 18:18:57
1 on-line since 07/18/2011 18:19:58
2 on-line since 07/18/2011 18:19:58
3 on-line since 07/18/2011 18:19:58
4 on-line since 07/18/2011 18:19:58
5 on-line since 07/18/2011 18:19:58
6 on-line since 07/18/2011 18:19:58
7 on-line since 07/18/2011 18:19:58
8 on-line since 07/18/2011 18:19:58
9 on-line since 07/18/2011 18:19:58
10 on-line since 07/18/2011 18:19:58
11 on-line since 07/18/2011 18:19:58
12 on-line since 07/18/2011 18:19:58
13 on-line since 07/18/2011 18:19:58
14 on-line since 07/18/2011 18:19:58
15 on-line since 07/18/2011 18:19:58

The utility uname can also be helpful, when executed with the -X flag, which prints expanded system information:

oraoracle@host1[PRODDB11] uname -X
System = SunOS
Node = zus60h-0034
Release = 5.10
KernelID = Generic_137111-04
Machine = sun4u
BusType =
Serial =
Users =
OEM# = 0
Origin# = 1
NumCPU = 16

The prtdiag utility, likewise:

oraoracle@host1[PRODDB11] prtdiag |more
System Configuration: Sun Microsystems sun4u Sun SPARC Enterprise M4000 Server
System clock frequency: 1012 MHz
Memory size: 32768 Megabytes

==================================== CPUs ====================================

CPU CPU Run L2$ CPU CPU
LSB Chip ID MHz MB Impl. Mask
--- ---- ---------------------------------------- ---- --- ----- ----
00 0 0, 1, 2, 3, 4, 5, 6, 7 2530 5.5 7 160
00 1 8, 9, 10, 11, 12, 13, 14, 15 2530 5.5 7 160

How to add a sleep statement to your pl/sql programs

Humans will often need some time to react to the output from your scripts. To briefly "pause" the output from a pl/sql script, add the following directive:

dbms_lock.sleep(no_of_seconds);

How to print the predefined error messages in the error number in pl/sql

dbms_output.put_line(sqlerrm(sqlcode));