|
| SessionId | 13 April 1999 |
| When selecting from UserEnv('SessionId'), does it return a unique number for all time (as it is actually the AuditSid) or a unique number since instance startup? | ||
 | It is generated from the data dictionary sequence AUDSES$. |
|
| IDL_UB1$ | 22 April 1999 |
| We encountered an "ORA-01653: unable to extend table SYS.IDL_UB1$" error. What is SYS.IDL_UB1$ used for? | ||
| IDL stands for Interface Definition Language. It is an intermediate language in which the structure of database tables and the logic of PL/SQL program units can be consistently represented as attributed trees. Oracle uses the DIANA IDL, which comes from compilers for the Ada programming language. DIANA stands for Descriptive Intermediate Attributed Notation for Ada. Anyway, this is one of four tables in the data dictionary used to store the DIANA for PL/SQL program units, and the database objects that they reference. |
|
| CACHE segment | 22 July 1999 |
| I was just curious about the segment types in Oracle (7.3.4), and found that there is a CACHE segment called 1.377. What is this CACHE segment all about? | ||
| In the old days this segment was used to bootstrap the data dictionary cache - hence the name. However, that job is now done with BOOTSTRAP$, so the only thing left in the CACHE segment is the compatibility information. That is, the list of features in use in the database that might prevent the database being opened with an earlier release of Oracle. The name of the CACHE segment, as for temporary segments, is derived from the file and block numbers of its segment header block. |
|
| Renaming a tablespace | 10 August 1999 |
| I know that Oracle does not allow you to rename a tablespace. I also know that there is an enhancement request 148742 submitted a while back and still waiting in the queue. Still . . . is it possible to rename an Oracle tablespace? | ||
|
Yes, you can update TS$ directly, but be warned that if users have the tablespace as their DEFAULT or TEMPORARY tablespace,
or if they have quota on the tablespace, then you will have to track down those references to the tablespace name and change them too.
Please note that this is unsupported. So do not do it on a production system, unless you get special dispensation from Oracle Support first. |
|
| Changing a column name | 31 August 1999 |
| I would like to change a column name in a table without having to drop it. Can I just update SYS.COL$ directly? | ||
|
No, if the table is referred to in any stored PL/SQL program units, you must first drop those program units
and then manually delete the rows for that table (based on its OBJ# from OBJ$) from the 4 data dictionary tables beginning with IDL.
The IDL tables contain the DIANA for the table, which includes a knowledge of the old column name.
That is why you need to get rid of it.
If you do this, it is perfectly safe to change column names directly, even with stored PL/SQL.
Please note, however, that it is also perfectly unsupported. So think twice before doing this on a production system. The supported way to solve this problem is to rename the table, and interpose a view to translate the column name. |
|
| Dropped tablespaces in TS$ | 8 September 1999 |
| I had a look at TS$, and it still got records for old dropped tablespaces. | ||
| This is normal. Records are only logically deleted from TS$, by changing the ONLINE$ column to 3 (INVALID). They are not physically deleted. The rows have to be left in TS$ in case a user has that tablespace as their default or temporary tablespace or has quota on the tablespace. When a tablespace is dropped these references to it are not deleted, lest the tablespace is about to be recreated elsewhere. So, you always have the history of all past tablespaces in TS$ (unless you delete them manually!). The significance of this is that if you create and drop tablespaces with unique names frequently (eg. SALES_1999_09) then the C_TS# cluster will become sparse and inefficient to query. |
|
| SYS.AUD$ | 7 October 1999 |
|
Oracle sent me an article (attached) on how to move SYS.AUD$ out of the SYSTEM tablespace.
However, the article says this practice is "unsupported",
"because the Oracle code makes implicit assumptions about the data dictionary tables such as SYS.AUD$,
which could cause problems with upgrades and backup/recovery scenarios".
What's the big deal here? What problems with upgrades and backup/recovery scenarios could one expect to face? | ||
| There is no B&R risk. However, if the structure of AUD$ is changed in a future release, you will not be able to use migrate.bsq to do the upgrade if SYS.AUD$ is just a view as proposed. |
|
| UNDEFINED datatype | 1 November 1999 |
| When describing the data dictionary tables, the datatype of some of the columns is shown as UNDEFINED. What is this datatype? Also, some of the dictionary views, such as DBA_OBJECTS, show UNDEFINED object types. | ||
| The string UNDEFINED is used extensively in decode statements in $ORACLE_HOME/rdbms/admin/catalog.sql as a catch all for values not explicitly catered for in the decode statement. In this case, the value being decoded is the numeric code for the internal data type. There are about 200 internal datatypes, of which only a small proportion are intended for customer use. These are documented in the chapter on datatypes in the OCI manual. Most of the rest are variations on raw; some are obsolete, and some are reserved for future functionality. In the case of these data dictionary columns you can see what the datatypes really are by looking in the sql.bsq file. |
|
| Datafile size | 13 November 1999 |
| Do the data dictionary views like DBA_DATA_FILES and V$DATAFILE go out to the control file for the size of the datafiles? | ||
| DBA_DATA_FILES reflects what is in SYS.FILE$, whereas V$DATAFILE reads directly from the controlfile (every time it is referenced). |
|
| V$XATRANS$ | 7 January 2000 |
I want to know the meaning of attributes in V$XATRANS$.
SQL> desc v$xatrans$ Name Null? Type ------------------------------- -------- ---- K2GTIFMT NUMBER K2GTITID_EXT RAW(64) K2GTIBID RAW(64) | ||
| It is created during XA installation by $ORACLE_HOME/rdbms/admin/xaview.sql. Each TP monitor has its own global transaction id format. The first field is the format id for the TPM managing the transaction. The second is the external global transaction id, and the third is the transaction id of the local branch transaction. |
|
| Putting an OPTIMAL setting on the SYSTEM rollback segment in sql.bsq | 7 February 2000 |
|
In a recent discussion with two Oracle internal people we raised some questions about making changes to the sql.bsq file.
I have read your tip "Customizing SQL.BSQ" and have read other authors' suggestions about possible changes.
We wanted to discuss adding minimum extent size to the system tablespace definition in sql.bsq
and changing the storage parameters for the SYSTEM rollback segment to include an OPTIMAL size.
(Changing the system rollback segment parameters comes from Oracle8 Backup and Recovery by Rama Velpuri and Anand Adkoli.)
The Oracle folks were entirely opposed to making any changes at all,
but did say that altering the minimum extent after database creation was OK.
Not that it was needed, but it was OK.
However, on the topic of changing the SYSTEM rollback parameters, they said that since the BOOTSTRAP$ table does not record and, if fact, ignores an OPTIMAL setting for the SYSTEM rollback segment, it was pointless to make that kind of change - even if they thought it was a good idea. They also added that even if an OPTIMAL setting was added to the SYSTEM rollback segment in sql.bsq, and we could see it when the database was open, it would be gone the next time the database was rebooted - something that didn't make much sense to me. In my experience, this isn't quite true. I have experimented with changing the SYSTEM rollback parameters in Oracle7, Oracle8, and Oracle8i databases and although I can't see an OPTIMAL parameter in the BOOTSTRAP$ table I do see the OPTIMAL value set correctly when I look in V$ROLLSTAT. And the changes are persistent no matter how many times I reboot the instance. Am I just being dumb and missing something here? | ||
|
In a well managed database, the SYSTEM rollback segment should only be used
for the changes to SYS.UNDO$ associated with bringing other rollback segments online, and taking them offline.
That is, it should hardly be used at all.
However, giving it an OPTIMAL size, just in case it is used, is OK.
I have in fact done so in the past, and I have done it from sql.bsq.
As you say, the change is persistent.
It is persistent because the optimal setting for a rollback segment lives in the rollback segment header block and not in the data dictionary.
If the optimal setting lived in the data dictionary, then its absence in BOOTSTRAP$ would mean that it would never be loaded into the row cache and thus would be at least ineffective, and would be overwritten on the next rowcache flush operation affecting that row. However, that would certainly not happen immediately as suggested, because the rowcache modifications made while replaying BOOTSTRAP$ on opening a database are not in fact flushed (as other modifications are). |
| Copyright © Ixora Pty Ltd |
|