There are some misconceptions about the X$ tables in Oracle. For example, do not use the X$ tables if you have a heart condition or are an inexperienced DBA—or you may ruin the entire database. (At least this is what some people will tell you. Sounds pretty scary.)
The most common misconception about the X$ tables is that the DBA can drop one or update one, thus ruining the database. However, X$ tables cannot be ruined. The only user who can select from these tables is the SYS user. A SELECT statement is the only command available to be performed on these X$ tables. An error occurs if you attempt to grant SELECT access to a user. Consider the following attempts to drop or alter an X$ table in the following listings. In the first listing, you cannot drop any of the X$ tables (even as the SYS user):
In this next listing, you are not able to update, insert, or delete any data in the X$ tables (even as the SYS user):
NOTE
When you mention the X$ tables, most people say, “Oh, pretty scary. I would never touch those tables.” The fact is DML commands (UPDATE, INSERT, DELETE) are not allowed on the X$ tables, even as the SYS superuser.
TIP
Only the SYS superuser can select from the X$ tables. An error occurs if an attempt is made to grant SELECT access to a user. But the X$ tables are not completely harmless. Because they are not documented, they could lead to data being misinterpreted. For example, if a V$ view definition is modified to use a brand-new X$ table, but the DBA has created his or her own view on the X$ tables, he or she might not have accurate information following an upgrade.
Leave a Reply