Oracle tables with $ in name

Question

In Sql Developer under tables I see tables, but some of the tables have $ in name.

Example:

  tb$something

What does that '$' sign mean when in name of table?

Solution 1

it doesn't mean anything. as you can create tables with dollar signs in if you want (probably not recommended as people may get confused thinking they are oracle special tables).

if you're referring to objects owned by SYS and SYSTEM then Oracle names its memory structure views for example V$ (eg v$session). but these are just a naming convention that Oracle corp uses for its objects.

Solution 2

Tables that contain the prefix V$ are tables that store volatile information.

Volatile information is produced by an oracle instance while it is in use (In use means the instance is up and running). Volatile Information includes Statistics, session information of users, memory allocation.

Solution 3

Usually, these are tables that "belong" to the database - either because they are owned by one of the predefined users (SYS, ...) or because Oracle uses them for housekeeping, e.g. for

• fulltext indices (these are named DR$... )
• materialized view logs (these are named RUPD$... and MLOG$...)

But you can't rely on that rule, because (as DazzaL already mentioned), nothing prevents you from creating your own tables with a dollar sign in the name.

Comments

[Gaurav Soni]

Oracle creates the v$ views from the x$ structures. The actual v$ view create view definitions are located in $ORACLE_HOME/rdbms/admin directory so you can see how the v$ views are built on the x$ structures.

[Jeffrey Kemp]

I use $ sometimes, e.g. EMPLOYEE$JN is the journal table for the EMPLOYEE. The only downside is that the script to create the table can't include a $ or SQL*Plus complains about it.

[Sergey Mirvoda]

>Oracle strongly discourages you from using $ and # in nonquoted identifiers. docs.oracle.com/cd/E11882_01/server.112/e10592/…