List Database Objects by Object Type

You can use ttIsql to list tables, indexes, views, sequences, synonyms, PL/SQL functions, procedures, and packages in a database.

Commands prefixed by all display all of this type of object. For example, the functions command lists PL/SQL functions that are owned by the user, whereas allfunctions lists all PL/SQL functions.

Note:

To run all* commands to see other user's object information, you need to have the permission to do so.

You can optionally specify patterns for object owners and object names.

Use these commands to list database objects:

• tables and alltables - Lists tables.

• indexes and allindexes - Lists indexes.

• views and allviews - Lists views.

• sequences and allsequences - Lists sequences.

• synonyms and allsynonyms - Lists synonyms.

• functions and allfunctions - Lists PL/SQL functions.

• procedures and allprocedures - Lists PL/SQL procedures.

• packages and allpackages - Lists PL/SQL packages.

The following example demonstrates the procedures and allprocedures commands. User TERRY creates a procedure called proc1 while connected to database1. Note that a slash character (/) is entered on a new line following the PL/SQL statements.

The procedures command and the allprocedures command show that it is the only PL/SQL procedure in the database.

$ ttIsql database1
Copyright (c) Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=database1";
Connection successful: 
DSN=database1;UID=Terry;DataStore=/disk1/databases/database1;DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)
Command>create or replace procedure proc1 as begin null; end;
       > /
Procedure created.
Command>procedures;
  TERRY.PROC1
1 procedure found.
Command>allprocedures;
  TERRY.PROC1
1 procedure found.

Now connect to the same DSN as PAT and create a procedure called q. The allprocedures command shows the PL/SQL procedures created by Terry and PAT.

$ ttIsql "DSN=database1;UID=PAT"
Copyright (c) Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=database1;UID=PAT";
Connection successful: DSN=database1;UID=PAT;
DataStore=/disk1/databases/database1;DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)
Command>create or replace procedure q as begin null; end;
       > /
Procedure created.
Command>procedures;
  PAT.Q
1 procedure found.
Command>allprocedures;
  TERRY.PROC1
  PAT.Q
2 procedures found.

In this example, PAT is able to see the TERRY's procedure, it cannot happen unless PAT has the permission.