July 29, 2006 at 12:21 am · Filed under Database
If you've enjoyed this post, you might want to subscribe my RSS feed or email alerts for free updates. Thanks for visiting and come back again.
If you’re a freak who work around with Oracle as backend, you would face the sitution where the oracle temporary space would be exhausted.
The usage temporary tablespace can’t be found out exactly using DBA_FREE_SPACE. To find out the true value of temporary table space we may need to use V$TEMP_SPACE_HEADER data dictonary.
SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;
Read the rest of this entry »
July 26, 2006 at 8:21 am · Filed under Database
if you’re wondering why particular query or procedure is talking a long such a long time to run; then make sure you check out whether the object you’re accessing is locked or not.
there are quite a few dba views available to make our task less complicated. you can use the below query which will return you the list of locked objects.
SELECT
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
FROM
v$locked_object a ,
v$session b,
dba_objects c
WHERE
b.sid = a.session_id
AND
a.object_id = c.object_id;
you can now decide whether to kill the job/ process or someother roundaway to remove the lock obtained on the particular object.
July 23, 2006 at 12:33 am · Filed under Database
The following query works well with Oracle 9i. i’ve no clue whether this works with others too or not.
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance;
even there is possiblity to find the startup time by quering logon_time from sys.v_$session view.
July 19, 2006 at 12:57 am · Filed under Database
Oracle initialization or INIT.ORA parameters with an underscore in front are hidden or unsupported parameters. One can get a list of all hidden parameters by executing this query:
select *
from SYS.X$KSPPI
where substr(KSPPINM,1,1) = '_';
The following query displays parameter names with their current value:
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
order by a.ksppinm;
July 19, 2006 at 12:44 am · Filed under Database
No, this is not available till now in Oracle & been noted down as a enhancement request.
Still here is a workaround way of doing that:
- Do a user-level export of user A
- create new user B
- import system/manager from user=A to user=B
- drop user A
