10g New Feature--Collect function
Collect Functio can use in sql query to convert multiple rows of data to a collection of data. Oracle create a SYS owned type for it.
-->tested it in Walmart Prod Env
SQL> select collect(tname) from tab;
select collect(tname) from tab
ORA-20900: User DEV_USER is not allowed to run DDL at any time.
ORA-06512: at line 76
From the error message we can see, Oracle try to launch DDL statement after we run the query. What is the DDL? That is try to create a sys-owned type.
-->tested it in Local pc.
13:09:38 scott@ORCL>select deptno, collect(ename) from emp group by deptno;
DEPTNO
----------
COLLECT(ENAME)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
SYSTPp1jeTzi6RhyW0a79+j6jpQ==('CLARK', 'KING', 'MILLER')
20
SYSTPp1jeTzi6RhyW0a79+j6jpQ==('CLARK', 'KING', 'MILLER', 'SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
30
SYSTPp1jeTzi6RhyW0a79+j6jpQ==('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD', 'ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
40
SYSTPp1jeTzi6RhyW0a79+j6jpQ==('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
60
SYSTPp1jeTzi6RhyW0a79+j6jpQ==('CLARK', 'KING', 'MILLER')
Obviously SYSTPp1jeTzi6RhyW0a79+j6jpQ== is new type. This new type is created when doing hard parse. Once two sql use the same execution plan, this type can be reused, Otherwise another new type will be created.
14:08:19 scott@ORCL>select collect(ename) from emp;
COLLECT(ENAME)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTPP9LRWdLHR3WjenOV17QwWQ==('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER', 'SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN',
'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER', 'SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'M
ILLER', 'SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')
After the new was created, even you clear shared pool or bounce the db, ti will persist. Oracle official said that it will be purged in 24 hours if it is not used. But if you have big shared pool, the time can be longer.
13:12:44 scott@ORCL>conn / as sysdba
13:13:11 sys@ORCL>select owner,type_name, type_oid from dba_types where type_name like '%SYSTPUE+PI2RYST6xWXI1s8F+Ng==%';
OWNER TYPE_NAME TYPE_OID
------------------------------ ------------------------------ --------------------------------
SCOTT SYSTPUE+PI2RYST6xWXI1s8F+Ng== 8A5621C5481146779EB6C7591E397360
13:13:32 sys@ORCL>shutdown immediate;
ORACLE 例程已经关闭。
13:14:10 sys@ORCL>startup;
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 260049796 bytes
Database Buffers 343932928 bytes
Redo Buffers 7135232 bytes
13:33:53 sys@ORCL>select owner,type_name, type_oid from dba_types where type_name like '%SYSTPUE+PI2RYST6xWXI1s8F+Ng==%';
OWNER TYPE_NAME TYPE_OID
------------------------------ ------------------------------ --------------------------------
SCOTT SYSTPUE+PI2RYST6xWXI1s8F+Ng== 8A5621C5481146779EB6C7591E397360