Home » RDBMS Server » Server Administration » Confusing result of a query.
Confusing result of a query. [message #284875] Sun, 02 December 2007 04:31 Go to next message
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
Re: Confusing result of a query. [message #284876 is a reply to message #284875] Sun, 02 December 2007 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing strange, Oracle needed some space, it took it.

Regards
Michel
icon7.gif  Re: Confusing result of a query. [message #284879 is a reply to message #284876] Sun, 02 December 2007 06:03 Go to previous messageGo to next message
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

Re: Confusing result of a query. [message #284895 is a reply to message #284879] Sun, 02 December 2007 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it took 8 blocks in the tablespace.

Regards
Michel
Re: Confusing result of a query. [message #284944 is a reply to message #284895] Sun, 02 December 2007 23:49 Go to previous message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Thankyou Very much,

Thankyou, Michel for clarification. Smile
Previous Topic: Unable to access DB
Next Topic: Shrink tablespace and files (3 merged from same poster)
Goto Forum:
  


Current Time: Sun Dec 11 02:40:47 CST 2016

Total time taken to generate the page: 0.10299 seconds