marți, 3 decembrie 2013

The old Oracle Forms and Reports between 9 and 10 is not working with JRE 7


Just put a system variable on your operating system


-Djava.vendor="Sun Microsystems Inc."

You can use the same value in Java options under the hood of the java engine, in control panel.

BE AWARE! Dont't forget the leading "-" , before of the "Djava...."!

Good luck!

luni, 18 noiembrie 2013

Oracle Application Server: How to change the name of the instance

Just replace the actual name in the following files 

• opmn.xml 
• sysman/emd/targets.xml
• webcache.xml

Change the instancename there and restart the opmn.

opmnctl stopall
opmnctl startall

Be aware of the report server instance name, which is another thing, with another changing procedure. 

The Oracle virtual machine doesn't start, doesn't stop, could not be killed, it is locked and you just get annoyed and don't know what to do

Time to time, inherently, one of your Oracle virtual machines, or maybe not just one, goes crazy and loops indefinitely into starting or stopping procedure. If you try to shutdown, is useless, if you try to kill, either. All you can do is to restart the machine properly from the command line, by remote access on the proper vm cluster node (if you're not in the situation of having a cluster, hust enter your server).
First of all, display your domains (this is an Oracle occult name for what you know being a virtual machine), using the allmighty 

xm list
Name                               ID   Mem VCPUs      State   Time(s)
0004fb0000060000abb053eabca73f42   5  6147     2     -b---- 906557.4
0004fb0000060000c9fc96601d88f77c   8 32768    16     -b---- 8030521.5
0004fb0000060000f5785dc8a18f8578   28  8195     4     -b---- 1419442.5
0004fb0000060000ff2c2bc7ef1b5d9c   33 32768     4     -b----   2036.7
Domain-0                           0  3119    16     r----- 7885714.2

Well, these are your domains (english translation: virtual machines).

Now, you have to identify the guilty machine.Go to OVM Manager, click on the name of the machine, and look in the right panel, in the Info section:

ID: 0004fb0000060000ff2c2bc7ef1b5d9c

As you can see, the ID appear to be easy to find.
Now, you can try the shutdown command:

xm shutdown 0004fb0000060000ff2c2bc7ef1b5d9c

but, from my experience, it's the same thing you just tried to do from the manager interface. Won't work...

In this case, just apply a heavier punch:

xm destroy 0004fb0000060000ff2c2bc7ef1b5d9c

This will power off the domain (!) and, also, will take off from the list of domains. Don't panic! If you try to display the domains:

xm list
Name                               ID   Mem VCPUs      State   Time(s)
0004fb0000060000abb053eabca73f42   5  6147     2     -b---- 906557.4
0004fb0000060000c9fc96601d88f77c   8 32768    16     -b---- 8030521.5
0004fb0000060000f5785dc8a18f8578   28  8195     4     -b---- 1419442.5
Domain-0                           0  3119    16     r----- 7885714.2

the domain is not anymore visible.

Just provide the command:

xm create /OVS/Repositories/0004fb0000030000de61d1/VirtualMachines/0004fb0000060000ff2c2bc7ef1b5d9c/vm.cfg

and your machine will start properly. If not, let me know :)

miercuri, 13 noiembrie 2013

ORA-02069: global_names parameter must be set to TRUE for this operation

Sometime, this error message occurs somehow without any reasonable meaning.


Insert into table@dblink select * from view;

ORA-02069: global_names parameter must be set to TRUE for this operation

Apparently, there is no reason to panic, you have to set that parameter appropriately and everything is working again. I don't care why, but when i did that:

alter session set global_names=true
Insert into table@dblink select * from view;

[Error] Execution (9: 28): ORA-02085: database link dblink.domain connects to XE

What can i do? The sid is correct, the global name of the database is:

select * from global_name;


Well, the answer is inside of that view. Some objects suitable to be called by views cannot be "smuggled" through the dblink and executed among of a normal insert in the target database.
My workaround was:

create table_temp as select * from view;
insert into table@dblink select * from table_temp;

Now, the records are static, no computing is necesary, and the insert command will behave normally.

vineri, 25 octombrie 2013

How to delete and add network interfaces in Linux

Besides of taking them off, you have to delete, also. the following file:


where 0 can be any other number, accordingly.

In order to properly add a network interface, besides of putting it into the slot (virtual or physical), you need to add and edit the same file as above. Example:

vi /etc/sysconfig/network-scripts/ifcfg-eth0


After you edit the text, with the right parameters, you can save it and:



miercuri, 23 octombrie 2013

Let's properly config the VNC servers

First step: put the right code into vncservers file

cat >> /etc/sysconfig/vncservers <<EOF
 VNCSERVERS="1:root 2:oracle"
 VNCSERVERARGS[1]="-geometry 1152x864 -depth 16"
 VNCSERVERARGS[2]="-geometry 1152x864 -depth 16"

(i assume we all need the user oracle)

mkdir /root/.vnc
chmod -R 775 /root/.vnc

Be aware, in the next step, comment all the lines in /root/.vnc/xstartup and then:

cat >> /root/.vnc/xstartup <<EOF
exec /etc/X11/xinit/xinitrc

[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup
[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources
xsetroot -solid grey
vncconfig -iconic &
xterm -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &
#twm &
gnome &


vncserver  :1

chmod 777 /root/.vnc/xstartup

su - oracle
mkdir /home/oracle/.vnc
chmod -R 775 /home/oracle/.vnc

cat >> /home/oracle/.vnc/xstartup <<EOF
exec /etc/X11/xinit/xinitrc

[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup
[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources
xsetroot -solid grey
vncconfig -iconic &
xterm -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &
#twm &
gnome &

vncserver  :2

 chmod 777  /home/oracle/.vnc/xstartup

su -

chkconfig vncserver on
service vncserver stop
service vncserver start

/etc/init.d/vncserver restart

and all you have to do now is to use the VNC client in order to have a nice remote desktop with the server, either under oracle or root user. Enjoy!

The OVM console is not working on clients after OVM Manager was installed

99% the root cause is the tightvnc.
Uninstall the tightvnc:

> yum -erase tightvnc

Then, install the tightvnc-java on OVM Manager Server (you can download from

Guess what, your browser will be glad to open, now, the OVM Console.

Legend: OVM=Oracle Virtual Machine

Linux: How to ls order by size

You know, i am sure, about the lack of a command that can list the content of a partition or a folder, arranged by size and displaying the size of every single subfolder or file found in the path.

Well, your salvation is (be aware, the ">" sign is the command prompt):

>  alias dfs='du -sk * | sort -n | perl -ne '\''($s,$f)=split(m{\t});for (qw(K M G)) {if($s<1024) {printf("%.1f",$s);print "$_\t$f"; last};$s=$s/1024}'\'

and then:

>  cd director_name
>  dfs

Don't forget about the user rights over the folders, it has a total impact on the command, you will not receive any information about a folder you are not entitled to read.
Enjoy! The command above is GOLD nugget!

How to catch the blocker of a record in a table

Hypothesis: i try yo update a record in a table and i receive, let's say, the following error message:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

or something is telling me the record is locked. If the issue is spanning over too much time, i have to overcome somehow the situation.
As a DBA, i use this sql bullet:
select a.sid, a.serial#, a.inst_id, c.object_name, a.username,d.sql_text
from gv$session a, gv$locked_object b, dba_objects c , gv$sqlarea d
where b.object_id = c.object_id 
and a.sid = b.session_id and a.sql_id=d.sql_id and upper(object_name)='tablename';

As you can see, the environment is a clusterized one, the views are gv$, and there is some information in there that you can consider it redundant: sql_text. Not everytime  that column will point to the actual sql command guilty of locking. So, you can filter a bit:

select a.sid, a.serial#, a.inst_id, c.object_name, a.username
from gv$session a, gv$locked_object b, dba_objects c 
where b.object_id = c.object_id 
and a.sid = b.session_id and a.sql_id=d.sql_id and upper(object_name)='tablename';

and, for standalone environment:

select a.sid, a.serial#, a.inst_id, c.object_name, a.username,d.sql_text
from v$session a, v$locked_object b, dba_objects c , v$sqlarea d
where b.object_id = c.object_id 
and a.sid = b.session_id and a.sql_id=d.sql_id and upper(object_name)='tablename';

luni, 16 septembrie 2013

I query the locking sessions, i see all of them, but i can't kill some of them because of ORA-00030:User Session ID does not exist (RAC database)

First of all, let's see the corect syntax:

alter system kill session '393','17149' immediate;

Pay attention, the syntax can be tricky sometimes.
Let's see, also, an example of locking sesssions hunter script:

select a.inst_id,a.sid,a.serial# serial1,a.sql_id,a.event,a.seconds_in_wait,
a.blocking_session,b.serial# serial2,a.status,a.state 
from gv$session a, 
(select sid,serial# from gv$session) b where wait_class not in 'Idle' and seconds_in_wait>10 and a.blocking_session=b.sid order by seconds_in_wait;

2 393 17149 aaxj0qygjyw8f enq: TX - row lock contention 968915 177 19301 ACTIVE WAITING
2 415 7759 aaxj0qygjyw8f enq: TX - row lock contention 969799 177 19301 ACTIVE WAITING
2 175 8825 aaxj0qygjyw8f enq: TX - row lock contention 970125 177 19301 ACTIVE WAITING

You can change the value of 10 seconds as you wish. As you can notice, the script was executed against a RAC architecture, the gv$session object states this.


alter system kill session '177,19301' immediate;

ORA-00030:User Session ID does not exist

Oops! How that so?

Solution: 99,99% the cause is we are logged on the wrong instance. Oracle doesn't let you to kill a session asigned to an instance, being logged on another instance. This is because the kill session statement has to do with kill -9 process from UNIX or orakill from Windows.
So: log on the right instance (the first column from that sql  tells you the wich it is) and the command will work. Be aware, the locking sessions sql does not tell anything about the instance of the locking session, just about the instance of the locked ones. So, you have to query the gv$session again, with the SID of the locking session in your hand.

sâmbătă, 24 august 2013

Infamous "The installer is unable to instantiate the file C:\Users\oracle\AppData\Local\Temp\{CF7ACE29-F720-4702-8739-0DEB3758625D}\KEY_XE.reg" error, during Oracle XE installation

It happens, when install de XE on Windows 7 64 bit, to receive this error from the title. Don't panic, has nothing to do with user rights or folder permissions. It's just another bug.
When error appears, just avoid to click on it, go to folder C:\Users\oracle\AppData\Local\Temp\{CF7ACE29-F720-4702-8739-0DEB3758625D}\, of course, accordingly with your path, copy  the file OracleMTSRecoveryService.reg to KEY_XE.reg, then return to the installing program, click the error and go further. Nothing will bother you again.

miercuri, 21 august 2013

How to grant select any table or view to another user

user1 is the current user.
user2 is the grantee.


conn user1/....

nume varchar2(50);
FOR nume IN (SELECT * FROM user_views )
  EXECUTE IMMEDIATE 'GRANT SELECT ON ' || nume || ' TO user2';
FOR nume IN (SELECT * FROM user_tables )
  EXECUTE IMMEDIATE 'GRANT SELECT ON ' || nume || ' TO user2';

That's it! 

joi, 11 iulie 2013

A table seems to be emptied, you don't know how. How to bring back the records?

First of all, don't say "it's imposible to occur in my database". Possible scenario: you have a large number of records in one table and, by mistake, are running against the table its recreation script (after some modifications made by yourself), taken from Toad, let's say. Well, inside of that script, you can easily observe the first command: drop table ..... If the table is dropped and recreated then, needless to say the table will be empty. Your records are gone to the wind. The most optimistic scenario is:

create table test as select * from dropped_table as of timestamp systimestamp-1;
insert into dropped_table select * from test;

where systimestamp-1 is the actual time minus 1 hour. The problem is, if you changed the structure of the table, you will receive the error 
ORA-01466: Unable to read data -- Table definition has changed
So, that is, if the structure is changed since the moment you dropped the table, the data cannot be restored. Next step:


Now, the things seem to come to normal. But, even so, the problems could be still there.

ORA-08189: cannot flashback the table because row movement is not enabled

So, you cannot flashback a table if the row movement is not enforced for it. Now, you've got only one way left:

select object_name, original_name, type, can_undrop , can_purge , droptime  from recyclebin where original_name like '%DROPPED%' ;

BIN$4TtAyuo6IpvgQwylqMDArw==$0 TABLE_DROPPED_PK INDEX NO YES 2013-07-11:06:56:58
BIN$4TtAyuo/IpvgQwylqMDArw==$0 TABLE_DROPPED   TABLE YES YES 2013-07-11:06:56:58
BIN$4TtFHS7RIuvgQwylqMBjAA==$0 TABLE_DROPPED_PK INDEX NO YES 2013-07-11:06:58:10
BIN$4TtFHS7WIuvgQwylqMBjAA==$0 TABLE_DROPPED   TABLE YES YES 2013-07-11:06:58:10
BIN$4TtHsm3pIyDgQwylqMBOnw==$0 TABLE_DROPPED_PK INDEX NO YES 2013-07-11:06:58:54
BIN$4TtHsm3uIyDgQwylqMBOnw==$0 TABLE_DROPPED   TABLE YES YES 2013-07-11:06:58:54
BIN$4TtLw7UlI9LgQwylqMCDgQ==$0 TABLE_DROPPED_PK INDEX NO YES 2013-07-11:07:00:02
BIN$4TtLw7UqI9LgQwylqMCDgQ==$0 TABLE_DROPPED   TABLE YES YES 2013-07-11:07:00:02
BIN$4TtMLcnUI+7gQwylqMBMsw==$0 TABLE_DROPPED_PK INDEX NO YES 2013-07-11:07:00:09
BIN$4TtMLcnZI+7gQwylqMBMsw==$0 TABLE_DROPPED   TABLE YES YES 2013-07-11:07:00:09

As you can see, in this scenario the Toad script deleting the records was executed more than one time. But you can imagine, the first run is important for us, because after that, anyway, the table is empty. So, what we will try to do is to bring back the version of the table before the first drop, and this will be the second row in the recordset from above. 

create table test as select * from dropped_table;

(meanwhile, maybe some users have had some input of data)

drop table dropped_table; --make some room for the table resurrection
flashback table "BIN$4Ts8ZZrlIkDgQwylqMAp5A==$0" to before drop;
insert into dropped_table select * from test;

(maybe the last command will give you some error, because of the table structure diferencies, but i am sure you can overcome this problem)

That's it! Now the table is back, with the right structure, from the moment in time, before the first drop, and, hope so, with the data submitted in the meantime, on top of the data "lost" after drop. Of course, your modifications made in the table structure will be  no retrievable by flashback, you can make them manually again, but, take the good part, your data is back! 

vineri, 28 iunie 2013

Don't try to expdp with include option before read this, you will live a sadistic game of errors

Now, i will imagine how  an expdp command should look, at the first touch of keyboard, for any of us, mortal human beings. Maybe somehow like bellow?

expdp schemas=myname include=TABLE:"IN ('table1','table2')" dumpfile=some1.dmp

As you can see, i try to export, using datapump, two tables. Just two tables.

expdp   \'/ as sysdba\'  schemas=myname include=TABLE:"IN ('table1','table2')" dumpfile=some1.dmp

You can use the "as sysdba" option or not, if not, then you can type "as sysdba" when requested.
Don't get astonished, the expdp command requests escape characters, that's it. First of all, you will get the following error:

ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression

and this is just the beginning. I will short your efforts, the problem is: missing escape characters. After correction:

expdp   \'/ as sysdba\'  schemas=myname include=TABLE:\""IN ('table1','table2')\"" dumpfile=some1.dmp

As you can notice, UNIX demands about all the characters, except letters, being escaped. But don't cheer yet, you will see, of course, an error:

ORA-39165: Schema MYNAME was not found.
ORA-39168: Object path TABLE was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_SCHEMA_02" completed with 3 error(s) at 05:28:00

Now, this is what i call to be frustrated. Thehis error message is more than ambigous. Useless to say, the user exists, the tables also. Well, you wanna know what is the problem? The lowercase letters composing the tables names. Anyway,

expdp   \'/ as sysdba\'  schemas=myname include=TABLE:\""IN ('table1','table2')\"" dumpfile=some1.dmp

won't do the work either, because the name of tables should be uppercase.

expdp   \'/ as sysdba\'  schemas=myname include=TABLE:\""IN ('TABLE1','TABLE2')\"" dumpfile=some1.dmp

Now, everything is ok.

There is another way to acomplish the task: parameter file. The command is:

expdp   \'/ as sysdba\' parfile=myfile.par

and an example of a posible content of the parameter file is:

SCHEMAS =myname

Notice two things: the uppercase tables names is still mandatory and you don't need, anymore, escape characters. This can be a reason to preffer this approach, be aware about the number of tables, can be tens of them, and you should escape a lot of commas if you choose the alternative without parameter file. 

sâmbătă, 4 mai 2013

The OracleOraDb11g_home1TNSListener service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs

Sometime, we are forced to use the Windows version of Oracle Database. After installed the binaries, the best approach is to create the listener (netca command). Well, if you already have another Oracle home wherever on you disk, maybe the ORACLE_HOME environment variable is already set to a value that cannot be accomodated with the newly created listener. In this case, the lsnrctl start will lead to an error code  0. Obvious, the frustrating telling nothing oracle errors. So, i am sure you will rush to the Services, so that to start the listener service. Well, the error will show more complicated and more frustrating:

The OracleOraDb11g_home1TNSListener service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

Useless to say, it's pretty tough to decrypt the situation. Well, the problem is a simple one, you should set all the appropriate environment variable and all of the problem will vanish.Remember the environment variables, at minimum:
set ORACLE_BASE=d:\oracle
set TNS_ADMIN=%ORACLE_HOME%\network\admin
You should set these variables through the Advanced System Settings -> Environment Variables, not using a command window.

Anyway, if you don't create and start the listener and you will proceed straight to the creation of the database, there will be another errors shortly after the propper start of the db creation.

joi, 25 aprilie 2013

How to exactly match the name of a database listener service

The services of a listener are not given by default, the same with the database name. And, more so, they can be a lot, created subsequently of the database and the first listener creation. So, sometimes, we should address that service we want to use in order to connect to a certain schema and we can't figure out the precise name of the service. Useless to say that in this situation you cannot connect to that given schema by any chance. So, what do you have to do is:

host1 >lsnrctl services

LSNRCTL for Linux: Version - Production on 25-APR-2013 11:13:22

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
      "DEDICATED" established:9367 refused:0 state:ready
Service "mydb.infra" has 1 instance(s).
  Instance "mydb1", status READY, has 1 handler(s) for this service...
      "DEDICATED" established:25443 refused:0 state:ready
Service "mydbXDB.infra" has 1 instance(s).
  Instance "mydb1", status READY, has 1 handler(s) for this service...
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: host1, pid: 21623>
Service "db.infra" has 1 instance(s).
  Instance "db1", status READY, has 1 handler(s) for this service...
      "DEDICATED" established:2021 refused:0 state:ready
Service "dbXDB.infra" has 1 instance(s).
  Instance "db", status READY, has 1 handler(s) for this service...
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: host1, pid: 25637>
Service "db_DGB.infra" has 1 instance(s).
  Instance "db1", status READY, has 1 handler(s) for this service...
      "DEDICATED" established:2021 refused:0 state:ready
The command completed successfully

Notice that the listener above is by some complexity. It services two databases, db and mydb, the two databases are clustered, the two instances, db_name and db_name1 are registered, the XDB service, meant to ensure the connection to the APEX features (htmldb part of the database) it appears as well in the ecuation.
So if we want to use, let's say the mydb database, we will conect like this:

vi tnsnames.ora

# tnsnames.ora Network Configuration File: \oracle\32\11.2.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.


Notice that the host is not found from the lsnrctl command from above, is a scan name, 11g database specific, but it doesn't matter for the purpose here.

miercuri, 24 aprilie 2013

How to find the Oracle AS ports

Sometimes, we can find in a situation when are supposed to manage some servers that are not belonging to our aria of administration. You're familiar with this type of situation: anxious and searching addresses and users and schemas and ports and so on. Shortly, this post is about not knowing the Oracle AS ports.

cd $ORACLE_HOME/install
vi portlist.ini

Now you'll see a value or a range of values for the Oracle HTTP Server Listen Port parameter. This is all you should know.
Be aware, you can be easely seduced by another parameter: Oracle HTTP Server Port, the more so as is anterior of the above one. If you will use this one IN ORDER TO RUN ORACLE DEPLOYED APPLICATION you'll get the error:

FRM-92050 failed to connect to Server /forms/servlet -1

That's because this port is meant only to server Apache purposes, not OC4J.

vineri, 19 aprilie 2013

Lock held by in-doubt distributed transaction n.n.nnnn

The title is self explanatory and I'll get quick to the solution:


Normally, you'll have to see the n.n.nnnn number under the local_tran column

rollback force 'n.n.nnnn';    (replace n.n.nnnn)

Copy/paste from/to MyMachine/VMWareESXGuest

I can't imagine what the VMWare developer who "forgot" to beatify us  with copy/paste superhuman power between local machine and esx infrastructure can have in mind. Especially this issue is maintained over a lot of versions past.
I know you can find the solution maybe in about 10000 places over the internet, but, from my point of view, this issue is so annoying, that i wish to have the solution on every single web site on the net. Of course, not quite on every one, if you understand, but almost like.

Select the virtual machine and click the Summary tab.
Click Edit Settings.
Navigate to Options > Advanced > General and click Configuration Parameters.
Click Add Row and type the following values in the Name and Value columns: – false – false

I know, the above text is not about Oracle or Linux, but, you're agree with me, there is no dba nowadays not using virtualisation.

Prerequisites: Forms 9.0. Matter: where's dissapeared the StringComparator.class from f90all.jar!

I don't know exactly all the java versions having this problem related to Forms 9.0, but i am pretty certain that java 6 is. 
The thing is that nasty class is in another archive, share.jar, the one who can be founded under $ORACLE_HOME/jlib
I recommend you to work clean, something like this:

mkdir ~/test
cd ~/test
mkdir share
mkdir f90all

copy the two archives under its everyone folder

cd share
cp $ORACLE_HOME/forms90/java/share.jar
jar -xvf share.jar
cd ../f90all
cp $ORACLE_HOME/forms90/java/f90all.jar
jar -xvf f90all.jar

(i assume that your java bin executables are traceable by  PATH)

find the /oracle/bali/share/sort/StringComparator.class  file under share director and copy to the same named place under f90all folder.

cp ../share//oracle/bali/share/sort/StringComparator.class ../f90all///oracle/bali/share/sort/

Now u have to repackage again your upgraded class. First, be aware, mind the f90all.jar file, not to be arround, because the archiver might include it either in the archive. Also, delete the folder META-INF prior to repackage.

cd ~/test/f90all
jar -cvf  ~/test/f90all.jar *

And now all you have to do is signing the whole thing:

$ORACLE_HOME/forms90/webutil/ ~/test/f90all.jar
cp ~/test/f90all.jar $ORACLE_HOME/forms90/java

Et voila! The error vanished!

ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], []

Of course, you know the 00600 fateful error has  tons of posible  faces. And of course, you know, 9 dba colleagues out of 10 will urge you to the famous Metalink. I won't debate here about Metalink, i've never made a single SR. Just downloaded some updates or patches. One of the 000600 erros, the one having 19004 as argument can be easely overcome like this:


Too easy? Sometimes, time is more than money.

joi, 18 aprilie 2013

Keep it simple! Schedule a job in no time!

Very often, the Oracle DBAs are in the middle of tons of tasks, running out of time, craving for speedy answers for their problems. One of the big time eaters issues related to db admining is the confusing syntax of scheduling a job. Shortly, use the following plsql code and leave the theory for those who are entitled to do it.
First, let's create a table just for test purposes, keep in  mind you don't  necessarily  have to do it.

create table table_of_moments (moment date);


/* note that the above code (the 3 commented lines) has to be uncommented when you will, eventually, run for the second or more than second time*/

  DBMS_SCHEDULER.create_schedule (
    schedule_name   => 'test_schedule',
    start_date      => SYSDATE,
    repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',

/*well, here let's discuss just a bit about the posibilities, i am sure the needs of scheduling various times and intervals are really infinite:
START_DATE could be, of course, SYSDATE or SYSDATE+1 for a day past, or +1/24 for an hour past, or +1/24/120 for 30 seconds past, choose what it fits your needs.
repeat_interval could be:
FREQ: DAILY, HOURLY, MINUTELY, SECONDELY, i don't know if can be anything else
INTERVAL: this parameter is pretty straight forward, you can imagine by yourselves how can you use it
The syntax for these two fields is the key of scheduling and is enormously rich but, remember, the actual scope here is to schedule a job quick and move on, to another tasks

    end_date        => NULL,
    comments        => 'This schedule test will trigger every 30 seconds from NOW .');

DBMS_SCHEDULER.create_program (
    program_name   => 'test_program',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN 
                                   insert into table_of_moments values (sysdate)
                           commit; END;',
    enabled        => TRUE,
    comments       => 'This program will fill the test table with the exact value of moment time the program was triggered .');

(Job_name => 'test_job',
Program_name => 'test_program',
Schedule_name => 'test_schedule',
Enabled => TRUE,
Comments => 'This job will make the things working .');


Now, let's check the things are working:

select * from table_of_moments; --repeat this command how many times you                                          

These being said, i remind you that the ways of scheduling a job are so various than you can study days in order to encompass them entirely. You can use dbms_scheduler or dbms_post and you can call many methods to submit or schedule a job. The present tip from this post is just about speed of doing right things.

miercuri, 17 aprilie 2013

I hate annoying "U'v got n more days before your password will expire"

I am sure 75% of you are not so interested in keeping the passwords fresh and sneaking-from-behind-careless, thus it's a very good idea to change all the parameters defining the lifespan of a user password. I am sure u agree with me that the most annoying expiration of any oracle password in this world, the never disputed champion of unlikeability is expiration of the DBSNMP user password.
As far as I could understand from the entirely oracle web community, the general way of overcoming this problem is to make a new profile and to assign any user you want to that profile. From my point of view this is not a perfect approach. First of all, maybe I don't know which the users are, until I won't query the dba tables, and maybe i won't eighter, after. Second, maybe two users can't share the same profile, in some scenarios. My proposal here is to treat equally all the users, and run a script careless of any information from dba_users  and dba_profiles views, a script that will modify all the profiles that are in your database. If the fact having the same passwords related profiles parameters for all the profiles is not a weakness for you, my aproach will be what you were searching for. So, the major advantage is provided by the tiny amount of time involved in the action, also the zero amount of implication in knowing any information about any user or profile in database. Check out the script:

nume_profil  dba_profiles.profile%type;

nume_user dba_users.username%type;
for nume_profil in (select distinct profile from dba_users)
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit failed_login_attempts UNLIMITED';
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_life_time  UNLIMITED';
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_grace_time unlimited';
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_lock_time unlimited';
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_reuse_max unlimited';
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_reuse_time unlimited'; 
end loop;

/*for nume_user in (select username from DBA_USERS where default_tablespace not in ('SYSTEM','SYSAUX') and instr(username,'$')=0 and instr(account_status,'EXPIRED')<>0) -- avoid system users and the ones whose password is not expired
    execute immediate 'ALTER user '||nume_user.username||' identified by '||nume_user.username;
end loop;*/ 

for nume_user in (select username from DBA_USERS where default_tablespace not in ('SYSTEM','SYSAUX') and instr(username,'$')=0) -- avoid system users
    execute immediate 'ALTER user '||nume_user.username||' account unlock';

end loop;

and BINGO, problem solved effortless and careless. Notice the third loop, which is useful by unlocking any user that is IN THIS MOMENT locked. Unfortunately, it's not a very good ideea to change, also, the EXPIRED state of the account, because you can alter accounts of other people, or critical accounts, used in some applications, etc. Every user of the database should change the password of their managed accounts, following the situation that  after the script will be executed, some accounts will be unlocked for ever but expired. Anyway, if you want to rip the account from the expired state and give it a password identically with the username, you can uncomment the loop above, the only one commented. But, i repeat, 90% it will prove to be a silly thing.
Check out the DISTINCT keyword, it's not dramatically important, but is there in honor of performance.
Don't forget to check once again, and make this a generale rule of your dba way of thinking: Check allways twice!!

select * from DBA_PROFILES order by profile,resource_name;

and carefully observe the results (ALL the profiles should have the appropriate parameters)!

Just for the record, let's enumerate the parameters:

  • failed_login_attempts - the number of failed login attempts before locking the account. It defaults to 10 times.

  • password_grace_time - the grace period after the password_life_time limit is exceeded. After that, your account will be EXPIRED & LOCKED. It defaults to unlimited.
  • password_life_time - how long an existing password is valid. 180 days is the default value.
  • password_lock_time – how long to lock the account after the failed login attempts is met. Don't mention its value and you will end having 1 day of relax, looking for the next login attempt to pe considered. Of course, this was a joke, the problem is not YOU, the dba, but the regular users, and they may not be happy waiting for the database to take them into consideration.
  • password_reuse_max – the number of times that you can reuse a password. Default = unlimited.
  • password_reuse_time – a time limit before a previous password can be reused. Default = unlimited.

Well, let's just remember that you've got one more parameter in this category, PASSWORD_VERIFY_FUNCTION, but, don't forget, we're talking here about some kind of security relaxation, so having an enforced function in order to treat the login attempts is not a goal for us right now.

miercuri, 3 aprilie 2013

I want a user copy of a user

Well, now let's practice something little bit more tricky and not very well documented.
As DBA, you wonder, pretty often, how to create a user different as name and schema from some existent user, but the same as all of the other characteristics and parameters of a user. This is it, your new user will connect using the same password, will use the same tablespace and will share the very same grants and rights as his original ancestor.

The next command will do the trick:

select dbms_metadata.get_ddl( 'USER', 'SCOTT' ) from dual
   select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SCOTT' ) from dual
   select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SCOTT' ) from dual
  select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SCOTT' ) from dual;

Of course, you have to specify your own desired username as a function parameter up there.

I warn you about something that can lead to an error: if your user hasn't got a granted resource, included in any of above categories (it doesn't have granted, let's say,  an object), an error of bellow form will be thrown:

ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7737
ORA-06512: at line 1
31608. 00000 -  "specified object of type %s not found"
*Cause:    The specified object was not found in the database.
*Action:   Correct the object specification and try the call again.

And let's see the correct answer (actually, in one of my databases, the user scott really wasn't granted with any right to any object, just has got some system grants and roles):


Of course, the result must be a little bit edited (some leading and trailing quotation marks should be deleted and semicolons added at any end of different command. So, the final code should be look like this:

GRANT "CONNECT" TO "newuser";
GRANT "RESOURCE" TO "newuser";