Birth of a New Oracle User Group (AIOUG)

A new Oracle user group takes birth and this time it’s in India. The new group is named All India Oracle User Group. I think this a very nice initiative taken by Murali Vallath and his team to come up with this idea. This has been long awaited and it will really help Oracle users in India to communicate and network with each other and share their experience. I have registered myself with the group. I wish the AIOUG team a very success.

Inconsistency between DBA_DATA_FILES, DBA_FREE_SPACE, and DBA_SEGMENTS views

Dear all,

A question has been raised on Technet forum that inconsistent information is being returned from Oracle dictionary views. OP is trying to relate used/free bytes returned by these dictionary views.

Information provided by OP:

Total bytes occupied by data files is: 11,010,048,000 Total bytes available are: 220,200,960 Total bytes used by objects: 10,989,076,480

Free Space + Used Space = 220,200,960 + 10,989,076,480 = 11,209,277,440

So, the sum of “Free Space” and “Used Space” is more than the “Total Space” available to the datafiles. The question is “How can I get more space than the existing one?”

Answer to the question goes here:

I have TEST user in my database and the default tablespace of this user is TEST_TS. I create couple of tables in this schema:

SQL> conn test/test
Connected.
SQL>
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMPDATA TABLE
TEST_ERRORS TABLE

SQL> create table test1 as select * from tab;

Table created.

SQL> create table test2 as select * from tab;

Table created.

SQL> create table test3 as select * from tab;

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1 TABLE
TEST2 TABLE
TEST3 TABLE
EMPDATA TABLE
TEST_ERRORS TABLE

Now, I will drop these newly created tables:

SQL> drop table test1;

Table dropped.

SQL> drop table test2;

Table dropped.

SQL> drop table test3;

Table dropped.

Now, query the dictionary views for used, free and allocated bytes/blocks information:

SQL> select blocks from dba_data_files where tablespace_name = 'TEST_TS';

BLOCKS
----------
3712

SQL> select sum(blocks) from dba_free_space where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
3680

SQL> select sum(blocks) from dba_segments where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
48

Opps, we get more space (3680 + 48 = 3728) than being allocated to the datafiles. Probably, by now you might have arrived to the answer, but let me reveal it to you.

OP hasn’t mentioned the Oracle database version but I am pretty sure it’s Oracle 10g or above. With Oracle database 10g, Oracle has added a new feature of recycle bin. When you drop an object it goes and stays in your recycle bin and will occupy the same amount of space. You need to purge the object to reclaim the space.

This is what is happening in this case. Dropped objects are still lying in the TEST_TS tablespace and being counted by the DBA_SEGMENTS view, whereas, DBA_FREE_SPACE correctly report the free space available.

Let me purge the dropped objects out of the recycle bin and rerun the queries:

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------

TEST1 BIN$BEExbY8xS0aXH3U+e9XPDg==$0 TABLE 2007-12-18:13:42:36

TEST2 BIN$WGV0P4B4TaCUukiPyctTPg==$0 TABLE 2007-12-18:13:42:38

TEST3 BIN$1P4aTA1IR8ijw4btdRkmzw==$0 TABLE 2007-12-18:13:42:39

SQL> purge recyclebin;

Recyclebin purged.

SQL> select blocks from dba_data_files where tablespace_name = 'TEST_TS';

BLOCKS
----------
3712

SQL> select sum(blocks) from dba_free_space where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
3680

SQL> select sum(blocks) from dba_segments where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
24

Wow, Oracle now reports correctly. The sum of “Free Space” and “Used Space” (3680 + 24 = 3704) is less than the “Total Space” (3712) available to the datafiles.

Regards

Issues with COMPATIBLE Initialization Parameter

Hello,

I was trying to transport a tablespace from one database (Prod_A) to another (Prod_B), both of which are running on Oracle 10g Release 2 (10.2.0.3). I took metadata export and ftp'ed both export dump and datafiles to the destination database server. While trying to import into Prod_B, I received "ORA-00721: changes by release 10.2.0.3.0 cannot be used by release 10.2.0.0.0" error.

I cross checked version of both the databases by querying V$VERSION and found that both were on 10.2.0.3, but still Oracle complains about different versions. Then, I had a look at COMPATIBLE initialization parameter of both the databases. This parameter was set to "10.2.0.3" on Prod_A, but on Prod_B it was "10.2.0". I changed this parameter on Prod_B and bounced database. Everything went fine and I was able to import tablespace into Prod_B.

Following is the import log where import terminated unsuccessfully.

Import: Release 10.2.0.3.0 - Production on Mon Dec 3 13:01:09 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in AR8MSWIN1256 character set and UTF8 NCHAR character set
import server uses AR8ISO8859P6 character set (possible charset conversion)
export client uses AR8ISO8859P6 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 721:
 "BEGIN   sys.dbms_plugts.checkCompType('COMPATSG','10.2.0.3.0'); END;"
IMP-00003: ORACLE error 721 encountered
ORA-00721: changes by release 10.2.0.3.0 cannot be used by release 10.2.0.0.0
ORA-06512: at "SYS.DBMS_PLUGTS", line 2004
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully

Regards