Month: June 2009

Export table and index DDL

As part of a project to reduce space within a cloned database it was decided that a large table could be excluded from the regular scripted copy.
I needed to extract the tables definition from the master, to create an empty version on the clone after the copy was complete.

Being Oracle version 9.2.0.9, I didn’t have to use the ‘exp’ command (the output of which needs a lot of tidying), but could instead use the <b>’get_ddl'</b> function of the <b>’dbms_metadata'</b> package – like so …

— Script to export the definition of table PROC_FIL and its index.

— Remember to add semi-colons, amend ‘storage (initial 1k next 1k minextents

— 1 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default);

— for example, and fix broken lines.
set heading off;

set echo off;

set pages 999;

set long 90000;
spool ddl_pf.sql

select dbms_metadata.get_ddl(‘TABLE’,’PROC_FIL’,’KIS’) from dual;

select dbms_metadata.get_ddl(‘INDEX’,’PF_PK’,’KIS’) from dual;

spool off;

Reinstalling HPOV to stop it freezing.

After a while using HPOV over a slow network the application may freezes when I try to perform any action that spawnes a new window (eg: ‘Closure Code’).

The cure is to re-install, but its not too difficult.

1) take copies of srv.DAT and Views.DAT from ‘C:Documents and Settingsrichard smithApplication DataHewlett-PackardOpenViewService Desk’

2) delete the folder ‘C:Documents and Settingsrichard smithApplication DataHewlett-PackardOpenViewService Desk’.

3) start up hpov and click ‘next’ within the connection wizard

4) paste the server name from srv.DAT (eg: ’nta-hpsd02.steria.co.uk:30999′), into the text-box.

5) type name and password (eg: ‘Richard Smith’, ‘password’).

6) type anything for the friendly name (eg: ‘hpov’), then click finish.

7) before loging-in copy the Views.DAT back into the folder deleted in step-2

8) login as normal (there will be an unusually long delay first time)

How to change an Oracle users password temporarily?

How to change an Oracle password temporarily?

In Oracle it is possible to change a password temporarily. This can be useful for DBAs who wish to act as a different user.

SQL> select username,password from dba_users where username='SCOTT';

USERNAME PASSWORD
-------- ----------------
SCOTT F894844C34402B67

Save the hash of the password somewhere safe. Then change it …

SQL> alter user scott identified by tmppassword;

Login with the credentials: scott/tmppassword, then after doing your work change the password back by using an undocumented feature called “by values”

SQL> alter user scott identified by values 'F894844C34402B67';