Oracle – Popular Misconceptions: Tables without Segments

Perhaps some of you know the SAP note #825653 Oracle: Common errors in which Martin Frauendorfer collected many “myths” and all-time misconceptions about Oracle databases.
Here’s another one.
“For all tables in an Oracle database, there is a segment in DBA_SEGMENTS.”
This is true for the vast majority of tables in an SAP installation, but not for all.
Let’s take a look at my test instance:
I want to get all tables that DON’T appear in DBA_SEGMENTS:

select owner, table_name from dba_tables 
    where (owner, table_name) not in 
                    (select owner, segment_name from dba_segments 
                     where segment_type='TABLE');

I was quite surprised to find that there are over 100 tables matching the query, even in a small test database:

OWNER     TABLE_NAME
--------- ------------------------------
SYS       CDEF$
SYS       CCOL$
SYS       FET$
SYS       TS$
SYS       SEG$
SYS       UET$
SYS       TSQ$
SYS       USER$
[...]
SYS       WRH$_SERVICE_STAT
SYS       WRH$_TABLESPACE_STAT
SYS       WRH$_ACTIVE_SESSION_HISTORY
SYS       WRH$_INST_CACHE_TRANSFER
SYS       WRH$_DLM_MISC
[...]
SYSTEM    LOGMNR_COLTYPE$
SYSTEM    LOGMNR_TYPE$
SYSTEM    LOGMNR_TABCOMPART$
SYSTEM    LOGMNR_TABSUBPART$
SYSTEM    LOGMNR_TABPART$
SYSTEM    LOGMNR_IND$
SYSTEM    LOGMNR_TS$
SYSTEM    LOGMNR_ATTRCOL$
[...]
SYS       AQ$_SCHEDULER$_EVENT_QTAB_G
SYS       AQ$_SCHEDULER$_EVENT_QTAB_H
SYS       AQ$_SCHEDULER$_EVENT_QTAB_T
SYS       AQ$_SCHEDULER$_JOBQTAB_I
SYS       AQ$_SCHEDULER$_JOBQTAB_G
SYS       AQ$_SCHEDULER$_JOBQTAB_H
SYS       AQ$_SCHEDULER$_JOBQTAB_T
SYS       RULE_SET_ROP$
[...]
SYS       CLUSTER_INSTANCES
SYS       CLUSTER_NODES
SYS       CLUSTER_DATABASES
SYS       MAP_OBJECT
SYS       ATEMPTAB$
173 rows selected.

Obviously, I left most of the tables out, for clarity reasons here.

So, what might be ‘wrong’ with those tables?
Are these tables all *special* Oracle dictionary objects for which the normal rules don’t apply?
Not really.
In fact, displaying a bit more information from the DBA_TABLES view will immediately disclose, what is going on here:

select owner, table_name, cluster_name, partitioned, temporary, iot_type 
    from dba_tables 
    where (owner, table_name) not in 
            (select owner, segment_name from dba_segments 
            where segment_type='TABLE')

This query gives the following result:

OWNER        TABLE_NAME                     CLUSTER_NAME    PAR T IOT_TYPE
------------ ------------------------------ ------------------- - ------------
SYS          CDEF$                          C_COBJ#         NO  N
SYS          CCOL$                          C_COBJ#         NO  N
SYS          FET$                           C_TS#           NO  N
SYS          TS$                            C_TS#           NO  N
SYS          SEG$                           C_FILE#_BLOCK#  NO  N
SYS          UET$                           C_FILE#_BLOCK#  NO  N
SYS          TSQ$                           C_USER#         NO  N
SYS          USER$                          C_USER#         NO  N
[...]
SYS          WRH$_SERVICE_STAT                              YES N
SYS          WRH$_TABLESPACE_STAT                           YES N
SYS          WRH$_ACTIVE_SESSION_HISTORY                    YES N
SYS          WRH$_INST_CACHE_TRANSFER                       YES N
SYS          WRH$_DLM_MISC                                  YES N
[...]
SYSTEM       LOGMNR_COLTYPE$                                YES N
SYSTEM       LOGMNR_TYPE$                                   YES N
SYSTEM       LOGMNR_TABCOMPART$                             YES N
SYSTEM       LOGMNR_TABSUBPART$                             YES N
SYSTEM       LOGMNR_TABPART$                                YES N
SYSTEM       LOGMNR_IND$                                    YES N
SYSTEM       LOGMNR_TS$                                     YES N
SYSTEM       LOGMNR_ATTRCOL$                                YES N
[...]
SYS          AQ$_SCHEDULER$_EVENT_QTAB_G                    NO  N IOT
SYS          AQ$_SCHEDULER$_EVENT_QTAB_H                    NO  N IOT
SYS          AQ$_SCHEDULER$_EVENT_QTAB_T                    NO  N IOT
SYS          AQ$_SCHEDULER$_JOBQTAB_I                       NO  N IOT
SYS          AQ$_SCHEDULER$_JOBQTAB_G                       NO  N IOT
SYS          AQ$_SCHEDULER$_JOBQTAB_H                       NO  N IOT
SYS          AQ$_SCHEDULER$_JOBQTAB_T                       NO  N IOT
SYS          RULE_SET_ROP$                                  NO  N IOT
[...]
SYS          CLUSTER_INSTANCES                              NO  Y
SYS          CLUSTER_NODES                                  NO  Y
SYS          CLUSTER_DATABASES                              NO  Y
SYS          MAP_OBJECT                                     NO  Y
SYS          ATEMPTAB$                                      NO  Y
173 rows selected.

As we see, every table that does not occur in DBA_SEGMENTS uses some special storage feature in use.

Tables, where column CLUSTER_NAME is not null, are part of a cluster – so the cluster itself as a segment.

For those tables that are partitioned only the table partitions will have segments – the table itself is only there as an access entity.

Oracle supports the use of SQL temporary tables – which are stored only as temporary segments. So no entry in DBA_SEGMENTS as long as no session is filling it with data.

And finally there are IOT (index organized tables) that DO get a directly related segment in the DBA_SEGMENT but it is an index segment named SYS_IOT_TOP_<TABLE OBJECT ID>.

Of course there are even more ways to create tables that don’t appear in DBA_SEGMENTS (e.g. external tables), but I just wanted to demonstrate that it is important to keep in mind the subtle difference between a TABLE as an entity you can access via SQL and a SEGMENT which is an Oracle-specific storage representation of database objects.
From a database user perspective, you never work with segments but only with TABLES, VIEWS and other SQL objects.

Questions to SAP Support: “How to generate a SQL Script via SQL on MaxDB?”

A customer was facing a problem when generating SQL scripts by using SQL. As this is a common habit in the Oracle world he tried to apply it to his MaxDB.

The script in this specific case should drop views from a specified schema. Anyhow the same technique may be used to change the default sample sizes of tables or perform update statistics on tables etc.

So the customers query looked like this:

select 'DROP VIEW "' || view_name || '" ' as TEXT from user_views

Running this command gives the following result (on my testdb of course):

TEXT
DROP VIEW "F_VIEW"
DROP VIEW "VAR_ZYKLEN"
DROP VIEW "VAR_ZYK_BUKRS"
DROP VIEW "VAR_ZYK_MAX"
DROP VIEW "VAR_ZYK_MAX_BUKRS"
DROP VIEW "VAR_ZYK_VERSION"
DROP VIEW "VZYKLEN"
DROP VIEW "VZYKLUS_MAX"
DROP VIEW "VZYKLUS_MAX_BUKRS"
DROP VIEW "VZYK_BUKRS"
DROP VIEW "VZYK_VERSION"

Unfortunately to run this script there needs to be an end-of-command delimiter after each single DROP command. In Oracle this is the ‘;’ character and can be placed just behind the statement.
So the correct and easy statement in Oracle would look like this then:

select 'DROP VIEW "' || view_name || '";' as TEXT from user_views

Leading to a result like this:

TEXT
DROP VIEW "F_VIEW";
DROP VIEW "VAR_ZYKLEN";
DROP VIEW "VAR_ZYK_BUKRS";
DROP VIEW "VAR_ZYK_MAX";
DROP VIEW "VAR_ZYK_MAX_BUKRS";
DROP VIEW "VAR_ZYK_VERSION";
DROP VIEW "VZYKLEN";
DROP VIEW "VZYKLUS_MAX";
DROP VIEW "VZYKLUS_MAX_BUKRS";
DROP VIEW "VZYK_BUKRS";
DROP VIEW "VZYK_VERSION";

In MaxDB the end-of-command delimiter is somewhat more complicated: it’s the sequence of ‘newline’ + ‘//’ (‘newline’ can be read as ASCII value 13)

So how do we get a newline with two slashes after each SQL command?

Pretty much straight forward!

First of all we need to select a line containing ‘//’ for each command. This would look like this than:

select '//' as TEXT from user_views

Giving us the exact same number of rows as our original query:

TEXT
//
//
//
//
//
//
//
//
//
//
//

Now we need to have both the command lines and the slash lines together – we use the UNION ALL command for this and get the following result:

select 'DROP VIEW "' & view_name & '" ' as TEXT from user_views
union all
select '//' as TEXT from user_views

 

TEXT
//
//
//
//
//
//
//
//
//
//
//
DROP VIEW "F_VIEW"
DROP VIEW "VZYKLEN"
DROP VIEW "VAR_ZYKLEN"
DROP VIEW "VZYK_BUKRS"
DROP VIEW "VAR_ZYK_MAX"
DROP VIEW "VZYKLUS_MAX"
DROP VIEW "VZYK_VERSION"
DROP VIEW "VAR_ZYK_BUKRS"
DROP VIEW "VAR_ZYK_VERSION"
DROP VIEW "VAR_ZYK_MAX_BUKRS"
DROP VIEW "VZYKLUS_MAX_BUKRS"

Not too bad – but we need to have the slashes right after the SQL commands… so we need to order them. For that I employ the rowno (or rownum in SQL mode “ORACLE”) pseudo column. It adds a number to each row found, when it’s found.
As we have basically two queries here, both queries will get their own result row numbering:

select rowno as line, 'DROP VIEW "' & view_name & '" ' as TEXT from user_views
union all
select rowno as line, '//' as TEXT from user_views

Running this statement unfortunately leads to an error message:

General error;-7036 POS(90) ROWNO specification not allowed in this context

This error seems to occur because the two statements are unrestricted – they have no where clause. I could not yet figure out why this is an issue here, but the ROWNO implementation of MaxDB is a bit odd in general. So let’s workaround this by adding a dummy where clause:

select rowno, line, TEXT FROM 
      select rowno as line, 'DROP VIEW "' & view_name & '" ' as TEXT from user_views
      where view_name is not null
union all
      select rowno as line, '//' as TEXT from user_views
     where view_name is not null
) order by line

This gives us the following result:

ROWNO  LINE  TEXT
1      1     //
12     1     DROP VIEW "F_VIEW"
4      2     //
14     2     DROP VIEW "VAR_ZYKLEN"
5      3     //
19     3     DROP VIEW "VAR_ZYK_BUKRS"
6      4     //
16     4     DROP VIEW "VAR_ZYK_MAX"
7      5     //
21     5     DROP VIEW "VAR_ZYK_MAX_BUKRS"
8      6     //
20     6     DROP VIEW "VAR_ZYK_VERSION"
9      7     //
13     7     DROP VIEW "VZYKLEN"
10     8     //
18     8     DROP VIEW "VZYKLUS_MAX"
11     9     //
22     9     DROP VIEW "VZYKLUS_MAX_BUKRS"
2      10    //
15     10    DROP VIEW "VZYK_BUKRS"
3      11    //
17     11    DROP VIEW "VZYK_VERSION"

As you can see, each sub-select got its own rowno a.k.a. line number so that we can use it to order the rows.  For the UNION ALL the both single result set had to be materialized internally to deliver the rows for the outer query where I selected rowno again.
It’s pretty easy to spot, that the result rows of the outer query got their rowno before the resultset was sorted.

Leving out the ‘line’ and the ‘rowno’ fields and adding another sort by argument finally delivers a useable SQL script:

SELECT TEXT FROM (
     select rowno as line, 'DROP VIEW "' & view_name & '" ' as TEXT from user_views
     where view_name is not null
union all
     select rowno as line, '//' as TEXT from user_views
     where view_name is not null
) order by line, text desc

 

TEXT
DROP VIEW "F_VIEW"
//
DROP VIEW "VAR_ZYKLEN"
//
DROP VIEW "VAR_ZYK_BUKRS"
//
DROP VIEW "VAR_ZYK_MAX"
//
DROP VIEW "VAR_ZYK_MAX_BUKRS"
//
DROP VIEW "VAR_ZYK_VERSION"
//
DROP VIEW "VZYKLEN"
//
DROP VIEW "VZYKLUS_MAX"
//
DROP VIEW "VZYKLUS_MAX_BUKRS"
//
DROP VIEW "VZYK_BUKRS"
//
DROP VIEW "VZYK_VERSION"
//

Although it does not look obvious at first sight, it’s not too difficult to adapt established DBA/Developer habits from Oracle to MaxDB.

Best regards,

Lars