I was working with one of our Oracle 10g databases the other day when I tried to do an data pump export using the OEM.
It seemed to be doing it, but then spat out this error:
ORA-20204: User does not exist:
EXP ORA-06512: at "SYSMAN.MGMT_USER", line 122
ORA-06512: at "SYSMAN.MGMT_JOBS", line 139
ORA-06512: at "SYSMAN.MGMT_JOBS", line 78
ORA-06512: at line 1
The error has something to do with the permissions for the user you are logged in as when doing the export.
Continue reading Export Problems in 10g OEM
I had to rebuild my Windows7 Media Center PC a little while ago, that all went OK and its working fine now. I can watch all the media OK and add new media to it from me Windows Vista PC OK. So I thought everything was OK with.
However, I built and XP laptop over the weekend for testing, and couldn’t connect to the Windows7 PC at all. It was freaking me out for a while. It all looked fine, I could connect from my VistaPC, what the heck was going on!
So, as you do I turned to Google for the answer. I found the answer at Windows7 Forums.
Continue reading XP to Windows7 networking
There are two ways to export data from an Oracle 10g database. Data Pump or the Export utility.
The export utility has been around for quite some time, and prior to 10g was the only way to export data. But 10g introduced a new method called data pump.
Data Pump seems to be allot faster and more useful, but export is still necessary if you plan to then import the data into an older database. Although I have found you might have to use the version of export utility that is compatible with the database you plan to import into, rather than the ‘new’ version that comes with 10g.
The other thing to note is, that the command line syntax is very different.
Continue reading Exporting data from Oracle Databases
I was doing some system checking the other day and I needed to find out what a certain stored procedure did. But I couldn’t readly see how to view the code of the stored procedure. After some digging of google I found how to use SQL command to show code of stored procedure in Oracle at AjaxApp.com.
I had to make a few changes to view what I wanted and came up with this
SELECT text FROM dba_source WHERE name=’proc_name';
Of course you will need to replace proc_name with the name of the stored procedure you want to view the code of.
I’m running a project at work at the moment to clean up some of our old version 8 and 9 databases. To do this I had to somehow track if anyone was logging into them and who. Luckly the team at Burleson consulting had some good information about Auditing and reporting Oracle user activity.
It consists of a table to store the information and a trigger that fires when anyone logs into the database.
create table stats$user_log ( user_id varchar2(30), session_id number(8), host varchar2(30), last_program varchar2(48), last_action varchar2(32), last_module varchar2(32), logon_day date, logon_time varchar2(10), logoff_day date, logoff_time varchar2(10), elapsed_minutes number(8) );
create or replace trigger logon_audit_trigger AFTER LOGON ON DATABASE BEGIN insert into stats$user_log values( user, sys_context('USERENV','SESSIONID'), sys_context('USERENV','HOST'), null, null, null, sysdate, to_char(sysdate, 'hh24:mi:ss'), null, null, null ); END; /
To view who has logged into the database you simply need to query the stats$user table like this.
select distinct user_id, logon_day from sys.stats$user_log;
I found it very helpful and was able to identify 4 databases that weren’t being used.
NOTE : I would be careful how long you leave the trigger in place. If the database is allot busier than you think it is, then the table could grow is size quite rapidlly.