ORA-00900: invalid SQL statement rebuilding lob segments

Toad for Oracle
1

ORA-00900: invalid SQL statement
I am using version 14.0.75.662
"Rebuild Multiple Objects". I select tables from a particular tablespace and highlight/select them, then use the LOB Segments tab, and "Load LOB segments of checked tables". this throws the error and does not retrieve any LOB objects.
This is strange as I frequently use this feature with no issues, including several times today.
I can still "Load indexes of checked tables" from the Indexes tab without a problem.

2

Turn on spool SQL to see the SQL that Toad is running which is causing the error, then post it here.

Main Menu -> Database -> Spool SQL -> Spool to Screen.

3

And, if I may go off topic a bit....

In less than 1 week, version 15.0 will be released. You should update to that version when it comes out. 14.0 had some bad problems with freezes and hangs.

4

Hi John - that was a prompt reply, thanks!

This is the trace:

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:32:38.019
select l.owner, l.table_name, l.column_name, l.cache, l.logging, l.tablespace_name, l.segment_name, null partition_name, null subpartition_name
from SYS.DBA_LOBS l
where 1=1
and l.owner = 'TOAD'
and l.table_name in ('TOAD_PLAN_TABLE','TOAD_PLAN_SQL')
and l.table_name not in (select table_name from DBA_PART_TABLES where owner = 'TOAD' and table_name in ('TOAD_PLAN_TABLE','TOAD_PLAN_SQL'));

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:32:38.206
select s.owner, s.segment_name, s.tablespace_name, null partition_name, null subpartition_name,
s.bytes, s.blocks, s.extents, s.initial_extent, s.next_extent, s.min_extents,
s.max_extents, s.pct_increase, s.freelists, s.freelist_groups, s.buffer_pool
from sys.dba_segments s
where S.segment_type = 'LOBSEGMENT';

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:32:38.851

-- O

The top SQL returns 0 rows - I wonder if that has something to do with it?
I had selected everything from tablespace PSWORK, which included the TOAD table.

5

I will upgrade to 15 once available

6

My guess is that there are no rows in the table. There have never been any rows in the table. Thus the lob segment has not been created yet.

7

I tried 2 tablespaces where some of the table have CLOB columns.. added to the initial trace:

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:32:38.019
select l.owner, l.table_name, l.column_name, l.cache, l.logging, l.tablespace_name, l.segment_name, null partition_name, null subpartition_name
from SYS.DBA_LOBS l
where 1=1
and l.owner = 'TOAD'
and l.table_name in ('TOAD_PLAN_TABLE','TOAD_PLAN_SQL')
and l.table_name not in (select table_name from DBA_PART_TABLES where owner = 'TOAD' and table_name in ('TOAD_PLAN_TABLE','TOAD_PLAN_SQL'));

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:32:38.206
select s.owner, s.segment_name, s.tablespace_name, null partition_name, null subpartition_name,
s.bytes, s.blocks, s.extents, s.initial_extent, s.next_extent, s.min_extents,
s.max_extents, s.pct_increase, s.freelists, s.freelist_groups, s.buffer_pool
from sys.dba_segments s
where S.segment_type = 'LOBSEGMENT';

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:32:38.851

-- ORA-00900: invalid SQL statement

-- Session: SYSADM@HRPROD
-- Timestamp: 15:33:44.009
select s.owner, s.segment_name, s.tablespace_name, null partition_name, null subpartition_name,
s.bytes, s.blocks, s.extents, s.initial_extent, s.next_extent, s.min_extents,
s.max_extents, s.pct_increase, s.freelists, s.freelist_groups, s.buffer_pool
from sys.dba_segments s
where S.segment_type = 'LOBSEGMENT';

-- Session: SYSADM@HRPROD
-- Timestamp: 15:34:00.611
select l.owner, l.table_name, l.column_name, l.cache, l.logging, l.tablespace_name, l.segment_name, null partition_name, null subpartition_name
from SYS.DBA_LOBS l
where 1=1
and l.owner = 'TOAD'
and l.table_name in ('TOAD_PLAN_TABLE','TOAD_PLAN_SQL')
and l.table_name not in (select table_name from DBA_PART_TABLES where owner = 'TOAD' and table_name in ('TOAD_PLAN_TABLE','TOAD_PLAN_SQL'));

-- Session: SYSADM@HRPROD
-- Timestamp: 15:37:33.492
select s.owner, s.segment_name, s.tablespace_name, null partition_name, null subpartition_name,
s.bytes, s.blocks, s.extents, s.initial_extent, s.next_extent, s.min_extents,
s.max_extents, s.pct_increase, s.freelists, s.freelist_groups, s.buffer_pool
from sys.dba_segments s
where S.segment_type = 'LOBSEGMENT';

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:40:00.406
select tablespace_name Tablespace, sum(cnt) "Table_and_Partition_Count"
from
(
select tablespace_name, count() cnt
from DBA_ALL_TABLES
where tablespace_name is not null
group by tablespace_name
union all
select tablespace_name, count()
from DBA_TAB_PARTITIONS
where tablespace_name is not null
group by tablespace_name
union all
select tablespace_name, count(*)
from DBA_TAB_SUBPARTITIONS
where tablespace_name is not null
group by tablespace_name
)
group by tablespace_name
order by 1;

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:40:10.993
select owner, segment_name, partition_name, segment_type, bytes, extents
from dba_segments
where segment_type like 'TABLE%'
and tablespace_name in ('PTTLRG_M','PTTLRG_S')
and segment_name not like 'SYS_IOT_TOP_%'
and segment_name not like 'SYS_IL%$$';

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:40:11.237
select owner, table_name, null partition_name, null subpartition_name, tablespace_name, pct_increase, initial_extent, next_extent, max_extents, logging, degree, instances, iot_type
from DBA_TABLES
Where partitioned = 'NO'
and cluster_name is null
and tablespace_name is not null
and table_name not like 'SYS_IOT%'
and not (((pct_free is not null) and (PCT_FREE = 0))
and ((pct_used is not null) and (PCT_USED = 0))
and CLUSTER_NAME IS NULL
and IOT_TYPE IS NULL)
and tablespace_name in ('PTTLRG_M','PTTLRG_S')
union all
select table_owner owner, table_name, partition_name, null subpartition_name, tablespace_name, pct_increase, initial_extent, next_extent, max_extent max_extents, logging, 'n/a' degree, 'n/a' instances, null
from DBA_TAB_PARTITIONS p
where tablespace_name in ('PTTLRG_M','PTTLRG_S')
and table_name not like 'SYS_IOT%'
and table_name not like 'BIN$%==$0'
and subpartition_count = 0
union all
select table_owner, table_name, partition_name, subpartition_name, tablespace_name, pct_increase, initial_extent, next_extent, max_extent, logging, 'n/a' degree, 'n/a' instances, null
from DBA_TAB_SUBPARTITIONS p
where tablespace_name in ('PTTLRG_M','PTTLRG_S')
and table_name not like 'SYS_IOT%'
and table_name not like 'BIN$%==$0'
order by 1,2;

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:40:11.847
select distinct owner, parent_table_name from DBA_NESTED_TABLES;

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:40:27.318
select l.owner, l.table_name, l.column_name, l.cache, l.logging, l.tablespace_name, l.segment_name, null partition_name, null subpartition_name
from SYS.DBA_LOBS l
where 1=1
and l.owner = 'SYSADM'
and l.table_name in ('PS_SAD_TAC_SUS_VCL','PS_SAD_TAC_CD_VCL','PS_PT_IM_CONFIGREC','PS_PTSF_STAT_ARCH','PS_PTSF_SCHED_STAT','PS_PTLT_FILE_ATT','PS_PTKEYDEFN','PS_PTINDEXDEFN','PS_PTAUDIT_AUTHITM','PS_MC_HIST_STMNT','PS_LM_XLATTBL_LANG','PS_LM_XLATTABLE','PS_DBFLD_LBL_VW','PSRECFIELD_TMP','PSRECFIELDDB','PSRECFIELD','PSQRYFIELD','PSQRYCRITERIA','PSPROJECTITEM','PSPCMNAME','PSMENUITEM','PSINDEXDEFN','PSDBFLDLABL','PSCUBDIMSTRCOUT','PSCOMPITEMDATA','PSCOMPITEM','PSCOMPDEFN','PSAUTHITEM')
and l.table_name not in (select table_name from DBA_PART_TABLES where owner = 'SYSADM' and table_name in ('PS_SAD_TAC_SUS_VCL','PS_SAD_TAC_CD_VCL','PS_PT_IM_CONFIGREC','PS_PTSF_STAT_ARCH','PS_PTSF_SCHED_STAT','PS_PTLT_FILE_ATT','PS_PTKEYDEFN','PS_PTINDEXDEFN','PS_PTAUDIT_AUTHITM','PS_MC_HIST_STMNT','PS_LM_XLATTBL_LANG','PS_LM_XLATTABLE','PS_DBFLD_LBL_VW','PSRECFIELD_TMP','PSRECFIELDDB','PSRECFIELD','PSQRYFIELD','PSQRYCRITERIA','PSPROJECTITEM','PSPCMNAME','PSMENUITEM','PSINDEXDEFN','PSDBFLDLABL','PSCUBDIMSTRCOUT','PSCOMPITEMDATA','PSCOMPITEM','PSCOMPDEFN','PSAUTHITEM'));

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:40:27.608
select s.owner, s.segment_name, s.tablespace_name, null partition_name, null subpartition_name,
s.bytes, s.blocks, s.extents, s.initial_extent, s.next_extent, s.min_extents,
s.max_extents, s.pct_increase, s.freelists, s.freelist_groups, s.buffer_pool
from sys.dba_segments s
where S.segment_type = 'LOBSEGMENT';

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:40:28.371
select owner, lob_name
from SYS.DBA_VARRAYS
where 1=1
and owner in ('SYSADM');

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:40:29.045
select owner, table_name, degree, instances
from sys.dba_tables
where 1=1
and owner = 'SYSADM'
and table_name in ('PS_SAD_TAC_SUS_VCL','PS_SAD_TAC_CD_VCL','PS_PT_IM_CONFIGREC','PS_PTSF_STAT_ARCH','PS_PTSF_SCHED_STAT','PS_PTLT_FILE_ATT','PS_PTKEYDEFN','PS_PTINDEXDEFN','PS_PTAUDIT_AUTHITM','PS_MC_HIST_STMNT','PS_LM_XLATTBL_LANG','PS_LM_XLATTABLE','PS_DBFLD_LBL_VW','PSRECFIELD_TMP','PSRECFIELDDB','PSRECFIELD','PSQRYFIELD','PSQRYCRITERIA','PSPROJECTITEM','PSPCMNAME','PSMENUITEM','PSINDEXDEFN','PSDBFLDLABL','PSCUBDIMSTRCOUT','PSCOMPITEMDATA','PSCOMPITEM','PSCOMPDEFN','PSAUTHITEM');

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:40:38.714
select s.owner, s.segment_name, s.partition_name, s.segment_type, s.bytes, s.extents
from dba_segments s, sys.dba_indexes i
where s.segment_type like 'INDEX%'
and s.owner = i.owner
and s.segment_name = i.index_name
and i.table_owner = 'SYSADM'
and i.table_name in ('PS_SAD_TAC_SUS_VCL','PS_SAD_TAC_CD_VCL','PS_PT_IM_CONFIGREC','PS_PTSF_STAT_ARCH','PS_PTSF_SCHED_STAT','PS_PTLT_FILE_ATT','PS_PTKEYDEFN','PS_PTINDEXDEFN','PS_PTAUDIT_AUTHITM','PS_MC_HIST_STMNT','PS_LM_XLATTBL_LANG','PS_LM_XLATTABLE','PS_DBFLD_LBL_VW','PSRECFIELD_TMP','PSRECFIELDDB','PSRECFIELD','PSQRYFIELD','PSQRYCRITERIA','PSPROJECTITEM','PSPCMNAME','PSMENUITEM','PSINDEXDEFN','PSDBFLDLABL','PSCUBDIMSTRCOUT','PSCOMPITEMDATA','PSCOMPITEM','PSCOMPDEFN','PSAUTHITEM')
and s.segment_name not like 'SYS_IOT_TOP_%'
and s.segment_name not like 'SYS_IL%$$';

-- Session: SYSTEM@HRPROD
-- Timestamp: 15:40:48.009
select owner, index_name, table_owner, table_name, null partition_name, null subpartition_name, tablespace_name, pct_increase, initial_extent, next_extent, max_extents, logging, degree, instances, status
from DBA_INDEXES
where table_owner = 'SYSADM'
and table_name in ('PS_SAD_TAC_SUS_VCL','PS_SAD_TAC_CD_VCL','PS_PT_IM_CONFIGREC','PS_PTSF_STAT_ARCH','PS_PTSF_SCHED_STAT','PS_PTLT_FILE_ATT','PS_PTKEYDEFN','PS_PTINDEXDEFN','PS_PTAUDIT_AUTHITM','PS_MC_HIST_STMNT','PS_LM_XLATTBL_LANG','PS_LM_XLATTABLE','PS_DBFLD_LBL_VW','PSRECFIELD_TMP','PSRECFIELDDB','PSRECFIELD','PSQRYFIELD','PSQRYCRITERIA','PSPROJECTITEM','PSPCMNAME','PSMENUITEM','PSINDEXDEFN','PSDBFLDLABL','PSCUBDIMSTRCOUT','PSCOMPITEMDATA','PSCOMPITEM','PSCOMPDEFN','PSAUTHITEM')
and index_name not like 'SYS_IOT_TOP_%'
and index_name not like 'SYS_IL%$$'
union all
select p.index_owner, p.index_name, i.table_owner, i.table_name, p.partition_name, null subpartition_name, p.tablespace_name, p.pct_increase, p.initial_extent, p.next_extent, p.max_extent, p.logging, 'n/a' degree, 'n/a' instances, p.status
from sys.DBA_IND_PARTITIONS p, sys.DBA_INDEXES i
where p.index_owner = i.owner
and p.index_name = i.index_name
and i.partitioned = 'YES'
and i.table_owner = 'SYSADM'
and i.table_name in ('PS_SAD_TAC_SUS_VCL','PS_SAD_TAC_CD_VCL','PS_PT_IM_CONFIGREC','PS_PTSF_STAT_ARCH','PS_PTSF_SCHED_STAT','PS_PTLT_FILE_ATT','PS_PTKEYDEFN','PS_PTINDEXDEFN','PS_PTAUDIT_AUTHITM','PS_MC_HIST_STMNT','PS_LM_XLATTBL_LANG','PS_LM_XLATTABLE','PS_DBFLD_LBL_VW','PSRECFIELD_TMP','PSRECFIELDDB','PSRECFIELD','PSQRYFIELD','PSQRYCRITERIA','PSPROJECTITEM','PSPCMNAME','PSMENUITEM','PSINDEXDEFN','PSDBFLDLABL','PSCUBDIMSTRCOUT','PSCOMPITEMDATA','PSCOMPITEM','PSCOMPDEFN','PSAUTHITEM')
and i.index_name not like 'SYS_IOT_TOP_%'
and i.index_name not like 'SYS_IL%$$'
and p.subpartition_count = 0
union all
select p.index_owner, p.index_name, i.table_owner, i.table_name, p.partition_name, p.subpartition_name, p.tablespace_name, p.pct_increase, p.initial_extent, p.next_extent, p.max_extent, p.logging, 'n/a' degree, 'n/a' instances, p.status
from sys.DBA_IND_SUBPARTITIONS p, sys.DBA_INDEXES i
where p.index_owner = i.owner
and p.index_name = i.index_name
and i.partitioned = 'YES'
and i.table_owner = 'SYSADM'

8

So does that mean that there are no other tables in the tablespace that may also have LOBs ?

9

I can reproduce the error in the latest version. I am not sure that it "means" anything other than there is a bug.

As a workaround, use one of the other methods for loading lobs (any of the other choices in that dropdown).

You can check all rows in the grid by doing a CTRL+A to select all. Then select the ones that you don't want to rebuild and hit the delete key to remove them from the grid. Then click the green triangle to rebuild.

Thank you for reporting the bug. I will fix this for version 15.1.

-John

10

Thanks, John
Actually I get the error when there are no LOBS in the grid.
When there are LOBS to populate the grid I do not get an error.
However.. using the option 'Load LOB segments by tablesace' does populate the grid.
Thanks for attending to it.