Declaring a name for a temporary table

A temporary table is associated with a session, not with a database. When you create a temporary table, you cannot create another temporary table with the same name (even for another database) until you drop the first temporary table or end the session.

The name of a temporary table must follow the naming requirements for SQL identifiers, but it cannot be a component of a qualified database-object name. When you create a temporary table with the CREATE TEMP TABLE statement, you cannot specify any authorization identifier as its owner. Unlike a permanent table, a temporary table cannot be referenced in an SQL statement by qualifying its identifier with an owner name, or a database name, or a database server name.

The name of a temporary table must be different from the name of any other table, view, sequence object, or synonym in the current database server. Otherwise, the temporary table takes precedence in your session over any permanent table of the same name. The name that you declare for the temporary table, however, need not be different from the temporary table names that are declared in other sessions of the same database server.

If you issue a cross-database DML statement that references a remote permanent table while your local database contains a temporary table with the same name, the DML statement accesses that local temporary table, rather than the remote permanent table.