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 https://oss.oracle.com/oraclevm/manager/RPMS/tightvnc-java-1.3.9-3.noarch.rpm).

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! 

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';

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.

Now:

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.

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.

How to grant select any table or view to another user

user1 is the current user.
user2 is the grantee.


sqlplus

conn user1/....


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

That's it! 

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:

FLASHBACK TABLE dropped_table TO TIMESTAMP SYSTIMESTAMP - INTERVAL '45' MINUTE;

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!