Vladislav Krejcirik

There are several useful queries for Oracle DB:

• select TABLE_NAME from SYS.ALL_TABLES where OWNER=’schema’ – select all tables for given schema
• select USERNAME from SYS.ALL_USERS order by USERNAME – select all schemas
• select VIEW_NAME from SYS.ALL_VIEWS where OWNER=’schema’ order by VIEW_NAME – select all views for given schema
• select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where UPPER(OBJECT_TYPE) = ‘PACKAGE’ order by OWNER, OBJECT_NAME – select all packages that can be viewed by the current user
• select TRIGGER_NAME, OWNER from SYS.ALL_TRIGGERS order by OWNER, TRIGGER_NAME – select all triggers for the current user
• select INDEX_NAME, TABLE_NAME, TABLE_OWNER from SYS.ALL_INDEXES order by TABLE_OWNER, TABLE_NAME, INDEX_NAME – select all indexes

Sometimes output from Sqlplus is a little bit confusing. Fortunately there are ways how to improve that.

• column column_name format a20 – make the column column_name smaller (20 characters), only for strings and dates
• column column_name format 99990 – make the column column_name smaller (5 digits), for numbers
• set pagesize 5000 – set the bigger page size
• column column_name noprint – no print column column_name
• set line 120 – set line length to 120
• set long 10000 – print whole long text, for example in CLOB

I needed to save output from SQL query into file. I found this solution:

SQL>SPOOL /tmp/data.dat
SQL>select * from table;
SQL>SPOOL OFF