Oracle Hints and Tips

Oracle Hints and Tips

This is an initial document containing general hints and tips for administering and using an Oracle database.

Commands

ALTER USER XXXX QUOTA UNLIMITED ON 'TABLESPACE'

DESCRIBE TABLENAME

Useful Tables

TAB
SYS.DBATSQUOTAS
DBAROLLBACKSEGS
DBA_USERS
DBATABCOLUMNS
DBA_INDEXES
DBA_TABLES
DBAROLEPRIVS
DBA_OBJECTS

One way of listing all tables for a particular user

<code>
SELECT D.OBJECT_NAME
    FROM DBA_OBJECTS D
    WHERE D.OWNER = 'USER_NAME' AND D.OBJECT_TYPE = 'TABLE'

  or

SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
/
</code>


v_$session_connect_info

select sid, username, osuser, terminal from v_$session

select A.name, D.OBJECT_NAME
from col$ A, DBA_OBJECTS D
where
  A.OBJ#=D.OBJECT_ID AND
  A.NAME LIKE '%USER%' and D.OBJECT_TYPE = 'TABLE'

USER_TABLES
USER_INDEXES

ALL_VIEWS
ALL_TABLES
CATALOG.SQL
CATPROC.SQL
CATBLOCK.SQL - Prepares views for lock reporting
UTLLOCKT.SQL - Reports locks
V$LOCKEDOBJECT - Lists locked objects
DBA_OBJECTS - Lists all objects

SET NLSDATEFORMAT DD-Mon-RRRR

Paginating Results Sets

See:

-- Frank Dean - 29 Sep 2012