How to change properties/attributes of partition table?

At times one would want to change properties/attributes of a table so new partitions are affected by change not the partitions already created, like example compression, change PCTFREE, default tablespace.

-- enable compression for all operations and change PCTFREE
SQL> ALTER TABLE scott.part_table MODIFY DEFAULT ATTRIBUTES COMPRESS FOR ALL OPERATIONS PCTFREE 5;

-- Change default tablespace for new partitions
SQL> ALTER TABLE scott.part_table MODIFY DEFAULT ATTRIBUTES TABLESPACE NEW_TABLESPACE;

It can be done on partition indexes too.
SQL> ALTER INDEX scott.local_index MODIFY DEFAULT ATTRIBUTES TABLESPACE NEW_TABLESPACE_IDX;

Using sys.dbms_iob one can manage jobs in dba_jobs.

DBMS_JOB allows one to create/manage jobs under user who has logged but using sys.dbms_ijob one can manage jobs all jobs scheduled in DBA_JOBS.

Here are some of the functions available in sys.dbms_ijob.
To execute/run job: You don’t have to be an owner of the job
SQL> exec sys.dbms_ijob.run(5);

If one tried executing the job not owned by the user using DBMS_JOB, one can’t run it and you will get the following error “ORA-23421: job number 21 is not a job in the job queue”

To enable/disable job:
-- to disable a job. Note one should set this when the job is not running by checking DBA_JOBS_RUNNING, because if the job is running you will have to wait till it finishes before disabling it and it has to be followed by a commit.
SQL> exec sys.dbms_ijob.broken(5, true);
SQL> commit;

-- Note the next_date value it initialized when the job is broken.
SQL> select broken, next_date from dba_jobs where job = 5;

B TO_CHAR(NEXT_DATE,’D
– ——————–
Y 01-JAN-4000 00:00:00

-- to enable a job, note it has to be followed by a commit and after the job is enabled the NEXT_DATE is updated based on the value of interval.
SQL> exec sys.dbms_ijob.broken(5, false);
SQL> commit;

SQL> select broken, next_date from dba_jobs where job in ( 5 );

B TO_CHAR(NEXT_DATE,’D
– ——————–
N 11-MAR-2012 19:31:45

To change the next date the job runs, again note if the job is running when one modifies this, it will get reset to the next interval instead of the value specified.
SQL> exec sys.dbms_ijob.next_date(5, sysdate + 1);
SQL commit;

SQL> select next_date from dba_jobs where job in ( 5 );

B TO_CHAR(NEXT_DATE,’D
– ——————–
N 12-MAR-2012 19:41:27

To change the interval
Example below changes the interval to run every Monday at midnight
SQL> exec sys.dbms_ijob.interval(5, 'TRUNC(NEXT_DAY(sysdate, ''MONDAY''))');
PL/SQL procedure successfully completed.
SQL> commit;

A utility to change oracle schema password using Java

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

How to view/change statistics retention and space used to store stats history?

See the oldest statistics history available
SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

GET_STATS_HISTORY_AVAILABILITY
—————————————————————————
25-OCT-11 10.00.55.093000000 PM -07:00

See the stats retention
SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
—————————
31

Modify retention
SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60);

Space currently used to store statistics in SYSAUX in KBytes, so increasing the retention would affect it’s size
SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%';

OCCUPANT_DESC SPACE_USAGE_KBYTES
—————————————————————- ——————
Server Manageability – Optimizer Statistics History 17920

Changing Oracle password in 11g using ALTER USER IDENTIFIED BY VALUES

In 11g the Oracle the hash password is no longer stored in DBA_USERS, it is stored in SYS.USER$ table in the column “PASSWORD” and “SPARE4”. So there are different ways password can be set depending on if “PASSWORD” and “SPARE4” are set in SYS.USER$ and what you want PASSWORD_VERSIONS to be.

If only “SPARE4” is used to set the password the password version is 11g and in SYS.USER$ password value becomes NULL after ALTER statement
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
——————————————————————————–
F894844C34402B67
S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G 11G

SQL> alter user scott identified by values 'S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346';

User altered.

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
11G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
———————————————————————–

S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346

If both password and SPARE4 is used to restore the password, the PASSWORD_VERSIONS is “10G 11G”, to set this the IDENTIFIED BY VALUES is passed in as “SPARE4;PASSWORD” from sys.user$

SQL> alter user scott identified by values 'S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B;F894844C34402B67';

User altered.

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G 11G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
——————————————————————–
F894844C34402B67
S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B

If the value from PASSWORD is used from SYS.USER$ the password version is set to 10g and column SPARE4 is set to NULL
SQL> alter user scott identified by values 'F894844C34402B67';

User altered.

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
————————————————————————-
F894844C34402B67

How to find/modify SQLServer Agent logfile location?

To get the location of SQLServer Agent log file, the log file is called SQLAGENT.out
DECLARE @oem_errorlog nvarchar(255)
EXECUTE master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’,
N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’,
N’ErrorLogFile’,
@oem_errorlog OUTPUT,
N’no_output’

PRINT @oem_errorlog

Sample Output: C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT

To modify location and name of SQLServer Agent logfile
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N’C:\temp\SQLAGENT.OUT’
GO

To recycle SQLServer Agent logfile
EXEC msdb.dbo.sp_cycle_agent_errorlog
When SQLServer Agent recycles the logfile, SQLAGENT.out.1 is the 2nd most recent logfile and so on.

How to find/set max processes allowed per user on OS-level?

On AIX
— List number of max processes allowed per user
$ /home/oracle:$ /usr/sbin/lsattr -E -l sys0 -a maxuproc
maxuproc 2048 Maximum number of PROCESSES allowed per user True

The value returned by ulimit may not always match so use the value returned by lsattr.

To change the value of user process by running chdev
$ chdev -l sys0 -a maxumproc=new_limit

How to change unix prompt?

C-shell (csh)
Example: set prompt=”[%n@%m %c] \! %”
oracle@localhost ~] 39 %
In this above example sets prompt to display username, hostname, current working directory and history number.
%n – username, %m – hostname, %c – pwd, \! – history number

K-shell (ksh)
Example: $ export PS1=’$USER [$PWD] $ ‘
oracle [/home] $
In the above example, sets prompt to display username and current working directory.

ORA-28221: REPLACE not specified

When a user name has a profile that has a password verify function set and the user tries to change the password using “ALTER USER” without the REPLACE clause, and also the user doesn’t have ALTER USER privilege Oracle will generate the “ORA-28221: REPLACE not specified” error message. So the user needs to include the “REPLACE” clause.

Example:
ALTER USER <username> IDENTIFIED BY <new password> REPLACE <old password>

Global_names and global_name in Oracle

http://arjudba.blogspot.com/2008/05/globalnames-and-globalname-in-oracle.html

To view and change global_name
22:53:34 system@TESTPRD> select * FROM global_name;

GLOBAL_NAME
——————————————————————————-
TESTPRD.WORLD

22:53:43 system@TESTPRD> ALTER DATABASE RENAME GLOBAL_NAME TO TESTDV.WORLD;

22:54:22 system@ALOCPRD> select * FROM global_name;

GLOBAL_NAME
——————————————————————————-
TESTDV.WORLD