The Oracle database is incredibly complex and most likely millions of lines of C code but at the deepest depths its just a set of files on disk that holds our data. The Oracle software allows us to query and retrieve or update our data from those files. BUT, at the simplest level the data is just stored in files with incredibly complex software to retrieve and work with that data.
At a high level we store data in tables and maybe we use indexes to make finding certain records faster. These structures are built into meta data that allows access to the right blocks of data. In general one object is stored in each data block but sometimes Oracle wants to access two objects in a block to make the access more efficient. This is achieved with a cluster. First a cluster is created and then multiple tables can be added to the clustered storage all indexed by a key; an indexed column. This is not an index the Oracle traditional sense; it is a column that all rows of each table type possess so that the rows from different tables can be accessed faster / together
The SYS.USER$ table is in a cluster. The cluster is created in dcore.bsq which you can find on the database server:
...
create cluster c_user#(user# number)
size 372 /* cluster key ~ 20, sizeof(user$) ~ 227, 5 * sizeof(tsq$) ~ 125 */
/
create index i_user# on cluster c_user#
/
...
create table user$ /* user table */
( user# number not null, /* user identifier number */
name varchar2("M_IDEN") not null, /* name of user */
...
)
cluster c_user#(user#)
/
create table tsq$ /* tablespace quota table */
( ts# number not null, /* tablespace number */
user# number not null, /* user number */
grantor# number not null, /* grantor id */
blocks number not null, /* number of blocks charged to user */
maxblocks number, /* user's maximum number of blocks, NULL if none */
priv1 number not null, /* reserved for future privilege */
priv2 number not null, /* reserved for future privilege */
priv3 number not null) /* reserved for future privilege */
cluster c_user# (user#)
/
There are 3 objects in this c_user# cluster; the i_user# index, user$ table itself and the tsq$ the tablespace quotas table.
We can check what clusters exist:
SQL> set serveroutput on
SQL> @sc_print 'select * from dba_clusters'
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_clusters','''','''''');
Executing Query [select * from dba_clusters]
...
-------------------------------------------
OWNER : SYS
CLUSTER_NAME : C_USER#
TABLESPACE_NAME : SYSTEM
PCT_FREE : 10
PCT_USED : 40
KEY_SIZE : 372
INI_TRANS : 2
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
FREELISTS : 1
FREELIST_GROUPS : 1
AVG_BLOCKS_PER_KEY :
CLUSTER_TYPE : INDEX
FUNCTION :
HASHKEYS : 0
DEGREE : 1
INSTANCES : 1
CACHE : N
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
SINGLE_TABLE : N
DEPENDENCIES : DISABLED
...
As we can see the c_user# cluster is an INDEX type. There are two types of cluster, an INDEX type which uses a B tree index or a HASH which uses a hash function which also must be provided. What is the purpose of a cluster? in a normal case a data block holds one object such as table. If there are regular joins between two tables such as USER$.USER# and TSQ$.USER# then creating a cluster allows rows from both tables to be stored in the same data block. This allows faster access to the linked data as only one block visit is required for both tables indexed on the USER# column.
We are interested in this as during forensics investigations we often focus on dictionary tables and in particular the bootstrap tables that are loaded from fixed positions. Oracle has to bootstrap itself into a running instance and most SQL involved recursive SQL that queries user$, obj$ and more. These tables have to be loaded first to allow any more SQL to run as it uses these tables first in recursive SQL. As these tables such as USER$ and OBJ$ are accessed in almost all SQL statements in the background then it makes sense to cluster them.
Can we create or own clusters?
SQL> connect c##acco/oracle1@//192.168.56.33:1539/xepdb1
Connected.
SQL> create user cluster_user identified by cluster_user;
User created.
SQL> grant create session to cluster_user;
Grant succeeded.
SQL> connect sys/oracle1@//192.168.56.33:1539/xepdb1 as sysdba
Connected.
SQL> grant create table to cluster_user;
Grant succeeded.
SQL> grant create cluster to cluster_user;
Grant succeeded.
SQL> grant unlimited tablespace to cluster_user;
Grant succeeded.
SQL>
NOTE: I have Database Vault enabled in this database hence the need to use c##acco to create a user
Now create a sample cluster:
SQL> connect cluster_user/cluster_user@//192.168.56.33:1539/xepdb1
Connected.
SQL> create cluster c_pete(id number) size 512;
Cluster created.
SQL> create table test_1 (id number, name varchar2(100)) cluster c_pete(id);
Table created.
SQL> create table test_2 (id number, address varchar2(100)) cluster c_pete(id);
Table created.
SQL>
Now check DBA_CLSUERS to see the entry:
SQL> connect sys/oracle1@//192.168.56.33:1539/xepdb1 as sysdba
Connected.
SQL> set serveroutput on
SQL> @sc_print 'select * from dba_clusters where cluster_name=''''C_PETE'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_clusters where cluster_name=''C_PETE''','''','''''');
Executing Query [select * from dba_clusters where cluster_name='C_PETE']
OWNER : CLUSTER_USER
CLUSTER_NAME : C_PETE
TABLESPACE_NAME : USERS
PCT_FREE : 10
PCT_USED :
KEY_SIZE : 512
INI_TRANS : 2
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
FREELISTS :
FREELIST_GROUPS :
AVG_BLOCKS_PER_KEY :
CLUSTER_TYPE : INDEX
FUNCTION :
HASHKEYS : 0
DEGREE : 1
INSTANCES : 1
CACHE : N
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
SINGLE_TABLE : N
DEPENDENCIES : DISABLED
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
We can see that the cluster is INDEX type and the index size is 512 as specified.
A table is still a table BUT we can group tables together in a cluster so that they are stored in the same data block for speed
What about the key we specified in the creation of the cluster and also used when we placed tables in a cluster?
SQL> col cluster_name for a10
SQL> col clu_column_name for a10
SQL> col table_name for a10
SQL> col tab_column_name for a10
SQL> set lines 220
SQL> select cluster_name,clu_column_name,table_name,tab_column_name from dba_clu_columns where owner='CLUSTER_USER';
CLUSTER_NA CLU_COLUMN TABLE_NAME TAB_COLUMN
---------- ---------- ---------- ----------
C_PETE ID TEST_2 ID
C_PETE ID TEST_1 ID
SQL>
This show the index column per table that are in the cluster.
The cluster key is not visible in DBA_CLUSTERING_KEYS or DBA_CLUSTERS, so how can we find the name of the key independent of the table definition?
We can use DBMS_METADATA.GET_DDL to get the cluster index column without respect to tables in the cluser:
SQL> SELECT DBMS_METADATA.GET_DDL('CLUSTER','C_PETE','CLUSTER_USER') from dual;
DBMS_METADATA.GET_DDL('CLUSTER','C_PETE','CLUSTER_USER')
--------------------------------------------------------------------------------
CREATE CLUSTER "CLUSTER_USER"."C_PETE" (
"ID" NUMBER )
SIZE 512
PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARALLEL (DEGREE 1 INSTANCES 1)
SQL>
Because the DDL can be re-constructed the index column of the cluster has to be stored somewhere in the data dictionary. The cluster index is not exposed in DBA_CLUSTERS and the DBA_CLU_COLUMNS so we have to get it from the base tables:
SQL> col owner# for 9999
SQL> col cluster_name for a10
SQL> col col# for 99
SQL> col cluster_key_column for a10
SQL> col type# for 999
SQL> SELECT o.owner#,
2 o.name AS cluster_name,
3 c.col#,
4 c.name AS cluster_key_column,
5 c.type#
6 FROM sys.obj$ o
7 JOIN sys.col$ c
8 ON o.obj# = c.obj#
9 WHERE o.type# = 3 -- cluster object
10 AND o.name = 'C_PETE';
OWNER# CLUSTER_NA COL# CLUSTER_KE TYPE#
------ ---------- ---- ---------- -----
513 C_PETE 1 ID 2
SQL>
Understanding the internals of how some data is stored and in this case for clustered objects is important for forensics analysis of an Oracle database that may have been breached or in general to understand how features work in the database
#oracleace #sym_42 #oracle #forensics #database #security #internals #clusters
