Tech > OracleHintsAndTips

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.DBA_TS_QUOTAS
  • DBA_ROLLBACK_SEGS
  • DBA_USERS
  • DBA_TAB_COLUMNS
  • DBA_INDEXES
  • DBA_TABLES
  • DBA_ROLE_PRIVS
  • 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_$LOCKED_OBJECT - Lists locked objects
  • DBA_OBJECTS - Lists all objects

SET NLS_DATE_FORMAT DD-Mon-RRRR

Paginating Results Sets

See:

-- Frank Dean - 29 Sep 2012


See also

-- Frank Dean - 10 Nov 2002

Related Topics: OracleSqlPlus, OracleHintsAndTips2