Oracle 11g: New sql.bsq structure

Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

Probably one of the oldest database scripts around is the sql.bsq file. First comments described in the heading of this file date from 1994 (“anori – 11/17/94 – ADT support tables and columns”). In the “old days”, the days when I started (Oracle version 6, 7), it was common to alter this file a little bit, regarding table storage options, so it fitted our data dictionary tablespace layout. The purpose of these alterations was to achieve an increase in performance and avoid fragmentation (see, for example, here, for some information about the approach in “those” days).

The sql.bsq file is called during database creation and creates, among others, the data dictionary.

During a discussion with Oracle Development I wanted to check how the preferred data types were defined during database creation. I got side-tracked. The sql.bsq resides in the “$ORACLE_HOME/rdbms/admin” directory.  In Oracle 11g the sql.bsq file has been redefined. It looks like a Dutch person had his hands on it (“jklein – 08/01/05 – diag 11g – split-up into units”); “klein” is small in Dutch. I guess this (“klein”) sounds somewhat appropriate in this context (splitting up sql.bsq).



As nowadays common for the catalog.sql script, the sql.bsq script contains references to other scripts. Most of them contain nothing new, just got a new

Files called from within the sql.bsq file are:

Creation of tablespace SYSTEM, rollback segment SYSTEM.
Creation of clu$, uet$, seg$, ts$, obj$, props$, edition$, defrole$, sysauth$, dual, etc.
dsqlddl.bsqCreation of link$, recyclebin$, duc$, trigger$, context$, etc
dmanage.bsqCreation of tablespace SYSAUX
Creation of sqllog$, sql$, sqltext$, sqlobj$, object_usage, etc.
dplsql.bsqCreation of procedure$, procedureinfo$, source$, error$, diana_version$, etc.
dtxnspc.bsqCreation of default UNDO, temporary, permanent tablespace
Creation of pending_trans$, pending_sessions$, etc.
Inserts of extra values in props$ 
dfmap.bsqCreation of map_file$, map_element$, map* etc
denv.bsqCreation of profile$, resource_cost$, resource*$, job$, etc.
Creation of profile “DEFAULT”
drac.bsqCreation of tsm_src$, service$, cluster_databases, dir$alert_history, etc.
dsec.bsqCreation of user sys, public, role connect, etc.
Creation of audit$, aud$, SYSTEM_PRIVILEGE_MAP, rls$, fga$, etc.
Inserting values into SYSTEM_PRIVILEGE_MAP, grant ora$base to public
doptim.bsqCreation of histgrm$, hist_head$, tab_stats$, ind_stats$, etc.
dobj.bsqCreation of dir$, type$, collection$, method$, operator$, etc
djava.bsqCreation of javaobj$, javasnm$, etc.
dpart.bsqCreation of partobj$, partcol$, lobfrag$, etc.
drep.bsqCreation of mlog$, slog$, snap$, snap_reftime$, cdc_system, streams$_capture_process, etc
daw.bsqCreation of aw$, aw_prop$, olap_mappings$, olap_models$, olap_primary_dimensions$
dsummgt.bsqCreation of sum$, sumagg$, dim$, etc
dtools.bsqCreation of role exp_full_database, select_catalog_role, etc
Creation of expimp_tts_ct$, metaview$, metascript$, etc
dexttab.bsqCreation of external_tab$, external_location$, etc
ddm.bsqCreation of model$, modeltab$, modelset$, etc.

I guess everyone has his favorites. I didn’t name them all, nor do I guess this will be the final state. It is a good habit to take a little time and wander through the files and look-up what you find interesting. It will be an nice journey through the dictionary.


The information demonstrated and shared here is based on Oracle beta software. The following is intended to outline Oracle’s general product direction.  It is intended for information purposes only, and may not be incorporated into any contract.  It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.  The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.


Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

About Author

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance.He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).