Re: sho command

From: Arun Mathur <themathurs_at_gmail.com>
Date: Thu, 3 Jan 2008 06:21:18 -0800 (PST)
Message-ID: <0ce07bf5-a3dc-441b-a275-e54b23f5b0c6@61g2000hsx.googlegroups.com>

On Jan 3, 1:29 am, Rosie <rose.rami..._at_cdps.state.co.us> wrote:
> On Dec 31 2007, 5:36 pm, Rosie <rose.rami..._at_cdps.state.co.us> wrote:
>
> > Using Oracle 9i -
>
> > Using PL/SQL
>
> > Is there a command like SQL's show table command that will show all
> > the information for a specific table, like the table columns & size,
> > any views, indexes, procedures, constraints, triggers, etc. associated
> > with the table?
>
> > Also, is there a command to show the dependancies associated to a
> > table?
>
> > Thanks,
>
> THANK YOU to eveyone for the info provided. To give more info, I am
> new to Oracle (learning by books) and need to do a print out of each
> schema by table that includes any indexes, views, procedures,
> triggers, dependencies, etc. associated with the table and all the
> attributes of each.
>
> In Oracle RDB using SQL, you can type the command show table
> table_name and everything created for that table appears - indexes,
> views, triggers, etc. I was hoping there was a command for Oracle
> using PL/SQL that would do the same thing.
>
> Currently, I am using OEM to generate the printouts but was hoping
> there was something else available (less time consuming).
>
> So, if I am understanding correctly, the alternative in getting what I
> am looking for -- the table plus it's indexes, triggers, etc. would
> need to be done programmatically through the information Mark & Mladen
> provided gathering the information from multiple table views specified
> in the dictionary.
>
> Any additional assistance is greatly appreciated.

Hi Rosie,

This is a good question. I remember using RDB's show table command quite often. In addition to the comments provided, do you have a Metalink account? If so, search their Knowledge Base on "extract DDL". In the meantime, I would take a look at using the dbms_metadata.get_ddl function. Below is a log file from a SQL*Plus session containing two tables, T1 and T2, where T2 has a foreign key constraint referencing T1:

SQL> connect amathur_at_dbdev
Connected.
SQL> drop table t cascade constraints;

Table dropped.

SQL> drop table t2 cascade constraints;

Table dropped.

SQL> create table t(t_pk number(11) not null primary key);

Table created.

SQL> create table t2(t2_pk number(11) not null primary key,   2 t_idx number(11) not null);

Table created.

SQL> alter table t2 add constraint t_for_t2 foreign key(t_idx) references t(t_pk);

Table altered.

SQL> create index t_for_t2 on t2(t_idx);

Index created.

SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')

---

  CREATE TABLE
"AMATHUR"."T"
   ( "T_PK" NUMBER(11,0) NOT NULL
ENABLE,
         PRIMARY KEY
("T_PK")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "FTDCDB"
ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) DBMS_METADATA.GET_DDL('TABLE','T')

---

  TABLESPACE
"FTDCDB" SQL> select dbms_metadata.get_ddl('TABLE','T2') from dual;

DBMS_METADATA.GET_DDL('TABLE','T2')

---

  CREATE TABLE
"AMATHUR"."T2"
   ( "T2_PK" NUMBER(11,0) NOT NULL
ENABLE,
        "T_IDX" NUMBER(11,0) NOT NULL
ENABLE,
         PRIMARY KEY
("T2_PK")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "FTDCDB"
ENABLE,
         CONSTRAINT "T_FOR_T2" FOREIGN KEY
("T_IDX")
          REFERENCES "AMATHUR"."T" ("T_PK")
ENABLE DBMS_METADATA.GET_DDL('TABLE','T2')

---

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE
"FTDCDB" SQL> spool off

Regards,
Arun Received on Thu Jan 03 2008 - 08:21:18 CST