ORA-01655: unable to extend ...

ORA-01655: unable to extend cluster SYS.C_FILE#_BLOCK# by 128 in tablespace
SYSTEM

I encountered the above error while creating the partitioned tables. In my partitioned table I was creating LOCAL indexes for the partitioned tables. While creating the indexes, Oracle was throwing the above error. The script was organized to create a partitioned tables and then create associated indexes (Local or Global) and that repeats for all of the table and partition creation statements.

The error confused me initially as the tables were creating okay with GLOBAL indexes but all the LOCAL indexes were getting failed. I checked the SYSTEM tablespace and that really went out of space, it had just 1 MB free space. As there are no enough spaces it makes sense that while creating tables or indexes, Oracle was not able to get enough spaces to insert the data dictionary entries. But the question came to my mind that in that case how come my table and and GLOBAL indexes were creating fine but soon after the LOCAL indexes creation were getting failed. I checked the LOCAL indexes so far created with no issue and found that they're residing in the schema's default tablespace not in the SYSTEM tablespace. Then after some research and R&D with SYSTEM tablespace I came to the understanding that partitioned table's metadata are stored in the SYSTEM tablespace and which was not available out off 1 MB free SYSTEM tablespace. I know this is not a great discovery and expert people already know about that but as I've started to play around with partitioned table recently, for me it's a new exploration.

Migration to Oracle Partitioned Table with very large table

11/12/2009

ORA-01655: unable to extend cluster SYS.C_FILE#_BLOCK# by 128 in tablespace
SYSTEM

I encountered the above error while creating the partitioned tables. In my partitioned table I was creating LOCAL indexes which is actually using the SYSTEM tablespace. So even though I had enough free space on my schema tablespace but eventually it took up the entire SYSTEM tablespace and threw that error.

I tried with partition names and tablespace names mentioned explicitly in the index creation script but still getting the same error. Interesting thing is that in the DBA_IND_PARTITIONS view it shows that the indexes are created inside the expected tablespace but in practical, that's not true. But the GLOBAL indexes works with no issue i.e. taking the default tablespace. I went with GLOBL option as I don't see any advantages with LOCAL index as my system is a OLTP system.

Tips: Oracle Database Adminstration

Note: you should have the below session privilege to be able to execute the statements mentioned in this blog.

'SELECT ANY DICTIONARY'

1. Monitor the running sql statements

SELECT b.SID "Oracle Sid",
a.spid "Unix PID",
b.serial# "Oracle Serial",
c.sql_text "SQL Text",
b.username "Oracle User",
b.osuser "OS User",
b.machine "Computer",
b.program "Program",
TO_CHAR (logon_time, 'HH24:MI:SS,yyyy/mm/dd') "Login Time",
b.action "Session Action",
c.action "SQL Action",
lockwait "LockWait",
status "Status",
optimizer_cost "Optimizer_cost"
FROM v$process a, v$session b, v$sql c
WHERE a.addr = b.paddr
AND b.sql_address = c.address
AND b.sql_hash_value = c.hash_value;

2. Find the size of tablespace

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name

3. Size of tables

SELECT s.owner,
ref.table_name,
s.segment_type,
CAST(SUM(s.bytes)/1048576 AS NUMBER(17,5)) MB
FROM dba_segments@database s,
(SELECT t.table_name,
t.table_name seg_name,
'TABLE' seg_type
FROM dba_tables@database t
WHERE t.owner = 'schema_name'
UNION
SELECT i.table_name,
i.index_name seg_name,
'INDEX' seg_type
FROM dba_indexes@database i
WHERE i.table_owner = 'schema_name'
) ref
WHERE s.segment_name = ref.seg_name
AND s.segment_type = ref.seg_type
GROUP BY GROUPING SETS((s.owner, ref.table_name, s.segment_type),(ref.table_name))
ORDER BY 1, 2, 3 DESC

Size of individual database objects (e.g. Indexes,Tables etc.)

SELECT sum(bytes)/1048576 Megs, segment_name
FROM user_extents
WHERE segment_name = ''
GROUP BY segment_name

4. Associated tablespace name of tables

select table_name, tablespace_name
from user_tables
where table_name = 'table_name'

5. Default tablespace name of the schema

select username, default_tablespace
from dba_users
where username = 'schema_name'

6. All locked objects:

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,

b.osuser, b.machine

FROM v$locked_object a, v$session b, dba_objects c

WHERE b.SID = a.session_id AND a.object_id = c.object_id;

7. Frequently used DBA views:

DBA_TABLESPACES - To view the configuration of the tablespces in the database. e.g. name, size, free and used space, etc.
DBA_DATA_FILES - Data files used by the tablespaces and current statistics
DBA_SEGEMENTS - Holds the Segments information. e.g. Segment name, tablespace name, used and free bytes, etc.
DBA_INDEXES - Information of all indexes (except partitioned indexes) e.g. Index name, tablespace name, etc.
DBA_PART_INDEXES - Holds the information of the partitioned indexes. e.g. Index name, partition type, index partition type etc.
DBA_IND_PARTITIONS - Holds the partitioned index detail (including the tablespace name)
DBA_EXTENTS - Contain information on database extents for each database objects i.e. tables, indexes etc.
DBA_CONSTRAINTS - Holds the definition and detail of constraints created in the system. uses example: find the table to which foreign keys referencing to
V$PARAMETER - All database parameters e.g. recyclebin; sga_max_size
V$NLS_PARAMETERS - All NLS parameters like NLS_LANGUAGE; NLS_CHARACTERSET etc.

8. Kill an Oracle session
search for the session you want to kill: select s.sid, s.serial#, s.username from v$session
kill the session: alter system kill session 'sid, serial#' -- in 11g, use 'sid,serial#,@inst_id'
Resources:

9. Setting ORACLE_HOME in Solaris

ORACLE_HOME=
export ORACLE_HOME
echo $ORACLE_HOME

10. to run Operating System/Shell command from sqlplus console use host keyword
e.g. host set ORACLE_HOME to check oracle home directory on MS Windows

11. Add space to tabalespace

To existing datafile:

ALTER DATABASE
DATAFILE ‘[../data. dbf]’
RESIZE [size in M];
Add new datafile:
ALTER TABLESPACE [tablespace_name]
ADD DATAFILE ‘[../data. dbf]’
SIZE [Size in M];


12. How to get the version of Oracle database installed -

i. SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

ii. check SQLNET.LOG file

# Frequently used User management commands:
i. alter user [user_name] account lock/unlock
ii. select password from dba_users where user_name = [user_name]
iii. alter user [user_name] identified by [newpass]
iv. select_any_dictionary privilege is required to be able to view dba views

http://www.experts-exchange.com/Database/Oracle/Q_23065829.html - Killing a running statement
http://www.benh.org/techblog/2006/07/finding-locked-objects-in-oracle/ - locked session
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/start001.htm - starting/stopping Oracle database 11g
http://mhabib.wordpress.com/2007/03/31/increase-the-size-of-tablespace/ - increase tablespace size