Real Life Oracle Fiction over a Large data set

After being SQL DBA 4+ years now since 2 years I am trying to be a DBA mean cross platform database administrator. So trying multiple technologies. MySQL, mongodb and Oracle too. My today's post is on oracle, true fiction working on large data-set. Actually I was testing CRUD and aggregations on oracle 11g over 21 millions records (21326826).

I had a table employee having huge amount of data and there is a column city which have null values on 21 millions records. The story begins when I was trying to update these null value with some information. Column have already secondary index we have created on city column because result of group by was slow on city so for better response time we have created index on city column. Physical architecture of database and tables space are default . Table is present in user tablespace. We have not changed any configuration of oracle before, I am totally new on oracle.

update hr.EMP set city='India' where city is NULL;

ERROR

ORA-01654: unable to extend index sys.emp_id by 1024 in tablespace system .

SELECT tablespace_name, SUM(BYTES)/1024/1024 "Free Space (MB)" FROM dba_free_space GROUP BY tablespace_name;

ALTER TABLESPACE USERS ADD DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\lmtbsb02.dbf' SIZE 1024M;

Tablespace altered.

ALTER TABLESPACE SYSTEM ADD DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\lmtbsb03.dbf' SIZE 1024M;

Tablespace altered.

update hr.EMP set city='India' where city is NULL;

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

Cause: the specified undo tablespace has no more space available

SQL> ALTER TABLESPACE UNDOTBS1 ADD DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\lmtbsb04.dbf' SIZE 1024M;

Tablespace altered.

update hr.EMP set city='India' where city is NULL;

18:28

then I tryed updates in chunk like this

update hr.EMP set city='India' where city is NULL and emp_id between 100000 and 200000;

update hr.EMP set city='India' where city is NULL and emp_id between 200000 and 300000;

update hr.EMP set city='India' where city is NULL and emp_id between 1000000 and 1200000;

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

then I tried to on auto-extend

alter database datafile 'C:\oraclexe\app\oracle\oradata\XE\lmtbsb04.dbf' autoextend on maxsize 2000M;

update hr.EMP set city='India' where city is NULL and emp_id between 1000000 and 1200000;

executed successful “1355064 rows updated.”

but story is still going....my friend when I tried to see the result of below query .. select city,count(*) from hr.emp group by city;

the query was running and running and running … I tried to execute query from sqlplus, toad and myora, everywhere result was not reachable. Then I completed it with COMMIT. Finally result was ok.

select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by 1;

After that I started auditing & monitoring system files and data-files. Do you know what I found. The newly added DATAFILE “lmtbsb04.dbf” size reached to 2.5 GB which belongs to UNDOTBS1 table-space.