ORA-32773: operation not supported for smallfile tablespace ORA-01653: unable to extend

I faced 2 different kind of ORA error during the practice on oraclesome year ago. I found below script from my data. And think this to post on vlblog to help others as well.

In below example a tablespace is created of small size 1MB and created a user whose tablespace default set to newly created tablespace. THis is done so that what ever data is inserted for this user in object will be stored on test tablespace.

So create a object and below is the open PLSQL which will insert records in loop.

While inserting error "ORA-01653: unable to extend table" popout, below the solution also available for this ORA error.

create tablespace test datafile 'E:\APP\ORACLE\ORADATA\ORCL\test01.DBF' size 1M ;

create user C##denz identified by denz container=all;

grant dba to c##denz;

alter user c##denz default tablespace test;

create table loop_insert( a number , b varchar2(100));

declare
begin
for i in 1..20000 loop
  insert into loop_insert (a, b) (select i , 'test'||i from dual);
end loop;
end;
/
ERROR at line 1:
ORA-01653: unable to extend table C##DENZ.LOOP_INSERT by 8 in tablespace TEST
ORA-06512: at line 4


alter tablespace test autoextend on;


Error report:
SQL Error: ORA-32773: operation not supported for smallfile tablespace TEST
32773. 00000 -  "operation not supported for smallfile tablespace %s"
*Cause:    An attempt was made to perform an operation which is supported
           only for bigfile tablespaces, e.g. resize tablespace.
*Action:   Use the appropriate clause of the ALTER DATABASE DATAFILE
           command instead.
           
alter database datafile 'E:\APP\ORACLE\ORADATA\ORCL\TEST01.DBF' autoextend on;

Rebuild Linux RPM

Sometimes linux RPM damaged by default which comes with the build or OS.

Below is the example of reuilding syslinux rmp, so in that case we follow step as below:

We have to download the latest version of syslinux and build the rpm cause there are some bugs in the existing version that ships with centos.

{download syslinux}

cd /root
wget http://www.kernel.org/pub/linux/utils/boot/syslinux/syslinux-3.86.tar.gz

1.extract
-------------------------------------------------------------------------
#tar -zxvf syslinux-3.86.tar.gz
#cp syslinux-3.86/syslinux.spec /usr/src/redhat/SPECS/
#cp syslinux-3.86.tar.gz /usr/src/redhat/SOURCES


2.Now building the rpm>
-------------------->

cd /usr/src/redhat/SPECS
#sed -i 's/BuildPrereq: nasm >= 2.03, perl/BuildPrereq: perl/g' /usr/src/redhat/SPECS/syslinux.spec

#rpmbuild -ba syslinux.spec

3.Installing rpm
-------------->

cd /usr/src/redhat/RPMS/i386
rpm -Uvh syslinux-3.86-1.i386.rpm

Rebuild All Indexes using 1 SQL | Single query

Below is query to rebuild all indexes in Oracle in one  go. It will generate the dynamic queries as output which can be copy and execute in same SQL window and all indexes will be rebuild automatically.

select
distinct  'alter index "'|| o.owner || '"."' || o.object_name || '" rebuild '
|| decode(i.partition_name, NULL, '', ' PARTITION "'|| i.partition_name ||'"' )
||' online nologging;' as rebuild_list
from
(
select
owner, object_name, subobject_name
from dba_objects
where
(( object_type = 'INDEX PARTITION' and  subobject_name is not null)
OR
(object_type ='INDEX' and  subobject_name is null))
and
created > to_date('1900-01-01-00:00:00','YYYY-MM-DD-HH24:MI:SS')
) o,
(  select id.owner, id.index_name, ip.partition_name
  from
    dba_indexes  id
  where
      'NO' in (ip.logging, id.logging)
    and id.owner = ip.index_owner (+)
    and id.index_name  = ip.index_name (+)) i
where
  o.owner          = i.owner and
  o.object_name    = i.index_name and
  o.subobject_name = i.partition_name;