How to use PRAGMA EXCEPTION_INIT?
PRAGMA EXCEPTION_INIT – allows one to map ORA- error and it can be raised in PL/SQL code. The SQL Error number passed in “EXCEPTION_INIT” is the same as error code except for “NO_DATA_FOUND” ORA-01403 which is 100.
See http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm#BABGIIBI – Summary of Predefined PL/SQL Exceptions
Example:
declare
no_rows_found exception;
pragma exception_init(no_rows_found, 100);
begin
raise no_rows_found;
end;
/
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
declare
too_many_rows exception;
pragma exception_init(too_many_rows, -1422);
begin
raise too_many_rows;
end;
/
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5
How to fix “Error accessing PRODUCT_USER_PROFILE”?
When logging in SQL*PLus if one receives the following message.
SQL> connect scott/tiger
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
This can be fixed by executing as SYSTEM
SQL> @?/sqlplus/admin/PUPBLD.SQL
..
And after the script is run reconnect as the user.
SQL> connect scott/tiger
Connected.
How to schedule a snap for STATSPACK?
One can automate taking of snapshots of STATSPACK by executing DBMS_JOB or by scheduling it through crontab.
Schedule automatic STATSPACK through DBMS_JOB – By executing @?/rdbms/admin/spauto.sql it will schedule to run statspack once every hour which is the default.
Output:
SQL> connect perstat@TESTDB
Password: **********
SQL> @?/rdbms/admin/spauto.sql
PL/SQL procedure successfully completed.
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
JOBNO
———-
2
Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter – the value for this parameter must be greater
than 0 to use automatic statistics gathering:
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC
———- ——— ——–
2 27-DEC-11 19:00:00
One can view the job by executing the following SQL.
SQL> select * FROM User_jobs WHERE job = 2;
JOB LOG_USER PRIV_USER
———- —————————— ——————————
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
—————————— ——— ——– ——— ——– ———
NEXT_SEC TOTAL_TIME B
——– ———- -
INTERVAL
——————————————————————————–
FAILURES
———-
WHAT
——————————————————————————–
NLS_ENV
——————————————————————————–
MISC_ENV INSTANCE
—————————————————————- ———-
1 SYS SYS
SYS 27-DEC-11
19:00:00 0 N
trunc(SYSDATE+1/24,'HH')
statspack.snap;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000 1
To change the snap interval one can execute dbms_job.interval in the example below it changes to collect every 1/2 hr
SQL> exec dbms_job.interval(1, 'trunc(SYSDATE+1/48,''HH'')');
PL/SQL procedure successfully completed.
SQL> select * FROM User_jobs;
JOB LOG_USER PRIV_USER
———- —————————— ——————————
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
—————————— ——— ——– ——— ——– ———
NEXT_SEC TOTAL_TIME B
——– ———- -
INTERVAL
——————————————————————————–
FAILURES
———-
WHAT
——————————————————————————–
NLS_ENV
——————————————————————————–
MISC_ENV INSTANCE
—————————————————————- ———-
1 SYS SYS
SYS 27-DEC-11
19:00:00 0 N
trunc(SYSDATE+1/48,'HH')
statspack.snap;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000 1
To schedule it through crontab: One would need to invoke the package “statspack.snap” via SQL*Plus
How to delete or truncate Statspack data?
One can truncate data in STATSPACK or delete range of snaps stored.
To truncate STATSPACK data
Using $ORACLE_HOME/rdbms/admin/sptrunc.sql, one can truncate STATSPACK data. To run the script you will need to connect as PERFSTAT user.
perfstat@TESTDB> @?/rdbms/admin/sptrunc.sql
Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables. You may
wish to export the data before continuing.
About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. &'exit&'), otherwise if you would like to begin
the truncate operation, press
Enter value for begin_or_exit:
To delete STATSPACK data – This will prompt for starting snap id and end snap id.
One can run the following SQL that will show all the snap ids on the current instance.
select s.snap_id
, to_char(s.snap_time,&' dd Mon YYYY HH24:mi:ss&') snap_date
, s.baseline
, s.snap_level #level#
, di.host_name host_name
, s.ucomment
from perfstat.stats$snapshot s
, perfstat.stats$database_instance di
, v$database v
, v$instance i
where s.dbid = v.dbid
and di.dbid = v.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.startup_time = s.startup_time
order by di.db_name, i.instance_name, s.snap_id
/
SNAP_ID SNAP_DATE B level
———- ——————— – ———-
HOST_NAME
—————————————————————-
UCOMMENT
——————————————————————————–
1 27 Dec 2011 18:30:56 5
MACHINE
2 27 Dec 2011 18:30:58 6
MACHINE
How to gather stats without histograms?
When passing method_opt as 'FOR ALL COLUMNS SIZE 1', it means no histogram is collected for columns on the table(s).
exec dbms_stats.gather_schema_stats('SCOTT', method_opt=>'FOR ALL COLUMNS SIZE 1');
One can find the default value by executing as follows:
select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
A standalone utility to change oracle password
The following java standalone program can be used to change Oracle password. It uses the OCI calls to change the password.
/*
Copyright (C) 2010-2011 Amin Jaffer
This program is free software: you can redistribute it and/or modify.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
*/
import java.util.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.*;
import javax.swing.GroupLayout.*;
public class PasswordChanger extends JFrame {
private JTextField pfldDB; // db name
private JTextField pfldUser; // db username
private JPasswordField pfldCurrent; // current password
private JPasswordField pfldNewPsw; // new password
private JPasswordField pfldChkPsw; // new confirm password
private Cursor normalCursor = new Cursor(Cursor.DEFAULT_CURSOR); // normal cursor
private Cursor hourglassCursor = new Cursor(Cursor.WAIT_CURSOR); // wait cursor
public PasswordChanger() throws Exception {
super("Oracle Password Change utility");
// Locale.setDefault(new Locale("us","EN"));
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
setDefaultCloseOperation(EXIT_ON_CLOSE);
Toolkit tk = Toolkit.getDefaultToolkit();
Dimension scr = tk.getScreenSize();
Dimension size = new Dimension(480,300);
int x = (scr.width – size.width) / 2;
int y = (scr.height – size.height) / 2;
setLocation(x,y);
//setPreferredSize(size);
pfldDB = new JTextField(10);
pfldUser = new JTextField(30);
pfldCurrent = new JPasswordField(15);
pfldNewPsw = new JPasswordField(15);
pfldChkPsw = new JPasswordField(15);
JLabel
lablDB = new JLabel("Database:"),
lablUser = new JLabel("Username:"),
lablCurrent = new JLabel("Current Password:"),
lablNewPsw = new JLabel("New Password:"),
lablChkPsw = new JLabel("Confirm New Password:");
JPanel pane = new JPanel();
GroupLayout glay = new GroupLayout(pane);
pane.setLayout(glay);
SequentialGroup hgrp = glay.createSequentialGroup();
hgrp.addGroup(glay.createParallelGroup(Alignment.TRAILING)
.addComponent(lablDB)
.addComponent(lablUser)
.addComponent(lablCurrent)
.addComponent(lablNewPsw)
.addComponent(lablChkPsw))
.addGroup(glay.createParallelGroup(Alignment.LEADING)
.addComponent(pfldDB)
.addComponent(pfldUser)
.addComponent(pfldCurrent)
.addComponent(pfldNewPsw)
.addComponent(pfldChkPsw));
SequentialGroup vgrp = glay.createSequentialGroup();
vgrp.addGroup(glay.createParallelGroup(Alignment.BASELINE)
.addComponent(lablDB).addComponent(pfldDB))
.addGroup(glay.createParallelGroup(Alignment.BASELINE)
.addComponent(lablUser).addComponent(pfldUser))
.addGroup(glay.createParallelGroup(Alignment.BASELINE)
.addComponent(lablCurrent).addComponent(pfldCurrent))
.addGroup(glay.createParallelGroup(Alignment.BASELINE)
.addComponent(lablNewPsw).addComponent(pfldNewPsw))
.addGroup(glay.createParallelGroup(Alignment.BASELINE)
.addComponent(lablChkPsw).addComponent(pfldChkPsw));
glay.setHorizontalGroup(hgrp);
glay.setVerticalGroup(vgrp);
glay.setAutoCreateContainerGaps(true);
glay.setAutoCreateGaps(true);
add(pane, BorderLayout.CENTER);
JPanel paneCmd = new JPanel();
paneCmd.add(new JButton(new OKAction()));
paneCmd.add(new JButton(new CancelAction()));
add(paneCmd, BorderLayout.SOUTH);
pack();
}
private class OKAction extends AbstractAction {
public OKAction() {
putValue(NAME, "Change Password");
}
private String appendMessage(String src, String msg) {
if ( src.equals("") )
return msg;
else
return src + "\n" + msg;
}
private String checkField(String field, String sErrMessage, String msg) {
if ( field.equals("") ) {
sErrMessage = appendMessage(sErrMessage, msg);
}
return sErrMessage;
}
public void actionPerformed(ActionEvent e) {
String sErrMessage = "";
String sDB, sUser, sCurrent, sNewPassword, sNewConfirmPassword;
sDB = pfldDB.getText();
sErrMessage = checkField(sDB, sErrMessage, "Database cannot be blank");
sUser = pfldUser.getText();
sErrMessage = checkField(sUser, sErrMessage, "Username cannot be blank");
sCurrent = new String(pfldCurrent.getPassword());
sErrMessage = checkField(sCurrent, sErrMessage, "Current Password cannot be blank");
sNewPassword = new String(pfldNewPsw.getPassword());
sErrMessage = checkField(sNewPassword, sErrMessage, "New Password cannot be blank");
sNewConfirmPassword = new String(pfldChkPsw.getPassword());
sErrMessage = checkField(sNewConfirmPassword, sErrMessage, "Confirm New Password cannot be blank");
if ( ! sNewPassword.equals(sNewConfirmPassword) ) {
sErrMessage = appendMessage(sErrMessage, "New Password does not match Confirm New Password");
}
if ( ! sErrMessage.equals("") ) {
JOptionPane.showMessageDialog(new JFrame(), sErrMessage, "Error", JOptionPane.ERROR_MESSAGE);
}
else {
Properties props;
Connection newconn = null;
String url = "jdbc:oracle:oci:@";
setCursor(hourglassCursor);
props = new Properties();
try {
url = url + sDB;
props.put("user", sUser);
props.put("password", sCurrent);
props.put("OCINewPassword", sNewPassword);
newconn = DriverManager.getConnection(url, props);
JOptionPane.showMessageDialog(new JFrame(),
"Password changed successfully", "Oracle Password changer",
JOptionPane.INFORMATION_MESSAGE);
int response = JOptionPane.showConfirmDialog(new JFrame(),
"Would you like to change your password for another database?",
"",
JOptionPane.YES_NO_OPTION);
if ( response == JOptionPane.NO_OPTION )
System.exit(0);
else {
pfldDB.setText(""); // db
pfldCurrent.setText(""); // current password
pfldNewPsw.setText(""); // new password
pfldChkPsw.setText(""); // new confirm password
}
}
catch (Exception exp) {
setCursor(normalCursor);
JOptionPane.showMessageDialog(new JFrame(),
exp.getMessage(), exp.getClass().getSimpleName(),
JOptionPane.WARNING_MESSAGE);
}
setCursor(normalCursor);
try {
if ( newconn != null )
newconn.close();
}
catch (Exception exp) {
JOptionPane.showMessageDialog(new JFrame(),
exp.getMessage(), exp.getClass().getSimpleName(),
JOptionPane.WARNING_MESSAGE);
}
}
}
}
private class CancelAction extends AbstractAction {
public CancelAction() {
putValue(NAME, "Cancel");
}
public void actionPerformed(ActionEvent e) {
System.exit(0);
}
}
/**
* @param args
*/
public static void main(String[] args) throws Exception {
(new PasswordChanger()).setVisible(true);
}
}
To compile:
C:> set CLASSPATH=c:\Oracle\product\10.2.0\client_1\jdbc\lib\classes12.zip
C:> javac.exe PasswordChanger.java
To execute: One would need the Oracle instant client to execute, in the following example it uses the 11g instant client
@set ROOT_DIR=c:\passwordchanger
@set TNS_ADMIN=\\sharefolder\tnsadmin
@set NLS_LANG=american_america.we8iso8859p1
@set CLASSPATH=%ROOT_DIR%\oraclient11g\ojdbc6.jar;%ROOT_DIR%
@set PATH=%ROOT_DIR%\oraclient11g;%PATH%
java PasswordChanger
