Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Strange ORA-10614

YusataMar 25 2012 — edited Mar 25 2012
Hi expert,
For me, it's really..really weird.
I created LOB segment (securefiles), inserted some data and check its space using DBMS_SPACE. But somehow it throws ORA-10614.
Oracle : 11.2.0.2
OS : RHEL 5
VARIABLE  UNFORMATTED_BLK         NUMBER;
VARIABLE  UNFORMATTED_BYT         NUMBER;
VARIABLE  FS1_BLK                 NUMBER;
VARIABLE  FS1_BYT                 NUMBER;
VARIABLE  FS2_BLK                 NUMBER;
VARIABLE  FS2_BYT                 NUMBER;
VARIABLE  FS3_BLK                 NUMBER;
VARIABLE  FS3_BYT                 NUMBER;
VARIABLE  FS4_BLK                 NUMBER;
VARIABLE  FS4_BYT                 NUMBER;
VARIABLE  FULL_BLK                NUMBER;
VARIABLE  FULL_BYT                NUMBER;
BEGIN
        DBMS_SPACE.SPACE_USAGE(
                SEGMENT_OWNER   => 'SF_DEMO',
                SEGMENT_NAME    => 'ASU',
                SEGMENT_TYPE    => 'LOB',
                UNFORMATTED_BLOCKS => :UNFORMATTED_BLK,
                UNFORMATTED_BYTES => :UNFORMATTED_BYT,
                FS1_BLOCKS      => :FS1_BLK,
                FS1_BYTES       => :FS1_BYT,
                FS2_BLOCKS      => :FS2_BLK,
                FS2_BYTES       => :FS2_BYT,
                FS3_BLOCKS      => :FS3_BLK,
                FS3_BYTES       => :FS3_BYT,
                FS4_BLOCKS      => :FS4_BLK,
                FS4_BYTES       => :FS4_BYT,
                FULL_BLOCKS     => :FULL_BLK,
                FULL_BYTES      => :FULL_BYT
        );
END;
/
BEGIN
*
ERROR at line 1:
ORA-10614: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_SPACE", line 190
ORA-06512: at line 2
I know that DBMS_SPACE is used only on segments in tablespaces with AUTO SEGMENT SPACE. And this is a prove that all things have been stored in ASSM tablespace.
SQL> SELECT
  2  A.SEGMENT_NAME,A.SEGMENT_TYPE,
  3  A.SEGMENT_SUBTYPE,A.TABLESPACE_NAME,B.SEGMENT_SPACE_MANAGEMENT
  4  FROM DBA_SEGMENTS A JOIN DBA_TABLESPACES B
  5  ON A.TABLESPACE_NAME=B.TABLESPACE_NAME
  6  WHERE A.OWNER='SF_DEMO';

SEGMENT_NAME              SEGMENT_TYPE       SEGMENT_SU TABLESPACE_NAME SEGMEN
------------------------- ------------------ ---------- --------------- ------
RESUME_SF                 TABLE              ASSM       SECF_TBS1       AUTO
SYS_IL0000018044C00002$$  LOBINDEX           ASSM       SECF_TBS1       AUTO
ASU                       LOBSEGMENT         SECUREFILE SECF_TBS1       AUTO
Any help appreciated.

Regards,
Yusata.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 22 2012
Added on Mar 25 2012
2 comments
816 views