OraFAQ Forum: Server Administration » Confusing result of a query.

Home » RDBMS Server » Server Administration » Confusing result of a query.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Confusing result of a query. [message #284875]

Sun, 02 December 2007 04:31

IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA

Member

Hello all,

This is my first post, please excuse for any errors.

Actually I have doubt with the following query, it gives different results upon sequencial execution. I am not so good at DBA, but want to be a good dba.

SQL> select tablespace_name,bytes/(1024*1024),blocks from dba_free_space where t
ablespace_name='UNDOTBS1';

TABLESPACE_NAME                BYTES/(1024*1024)     BLOCKS
------------------------------ ----------------- ----------
UNDOTBS1                                   .0625          8
UNDOTBS1                                    .875        112

SQL> select tablespace_name,FILE_ID,bytes/(1024*1024),blocks from dba_free_space
 where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                   FILE_ID BYTES/(1024*1024)     BLOCKS
------------------------------ ---------- ----------------- ----------
UNDOTBS1                                2             .0625          8
UNDOTBS1                                2              .875        112

SQL> select tablespace_name,FILE_ID,bytes/(1024*1024),blocks,RELATIVE_FNO from d
ba_free_space where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                   FILE_ID BYTES/(1024*1024)     BLOCKS
------------------------------ ---------- ----------------- ----------
RELATIVE_FNO
------------
UNDOTBS1                                2              .875        112
           2


SQL> SELECT TABLESPACE_NAME, FILE FROM DBA_DATA_fILES WHERE TABLESPACE_NAME='UND
OTBS1';
SELECT TABLESPACE_NAME, FILE FROM DBA_DATA_fILES WHERE TABLESPACE_NAME='UNDOTBS1
'
                        *
ERROR at line 1:
ORA-00936: missing expression


SQL> SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_fILES WHERE TABLESPACE_NAME
='UNDOTBS1';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------

UNDOTBS1
D:\ORA92\ORADATA\MYDATA\UNDOTBS01.DBF


SQL> select tablespace_name,bytes/(1024*1024),blocks from dba_free_space where t
ablespace_name='UNDOTBS1';

TABLESPACE_NAME                BYTES/(1024*1024)     BLOCKS
------------------------------ ----------------- ----------
UNDOTBS1                                    .875        112

Please Kindly Explain me the reason behind this.

Regards

Report message to a moderator

Re: Confusing result of a query. [message #284876 is a reply to message #284875]

Sun, 02 December 2007 05:50

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Nothing strange, Oracle needed some space, it took it.

Regards
Michel

Report message to a moderator

Re: Confusing result of a query. [message #284879 is a reply to message #284876]

Sun, 02 December 2007 06:03

IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA

Member

Thanks for Reply,

And you mean?, oracle needed undo space thats why it shows first this result

SQL> select tablespace_name,FILE_ID,bytes/(1024*1024),blocks from dba_free_space
 where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                   FILE_ID BYTES/(1024*1024)     BLOCKS
------------------------------ ---------- ----------------- ----------
UNDOTBS1                                2             .0625          8
UNDOTBS1                                2              .875        112

and then this one

SQL> select tablespace_name,bytes/(1024*1024),blocks from dba_free_space where t
ablespace_name='UNDOTBS1';

TABLESPACE_NAME                BYTES/(1024*1024)     BLOCKS
------------------------------ ----------------- ----------
UNDOTBS1                                    .875        112

Regards

[Updated on: Sun, 02 December 2007 06:03]

Report message to a moderator