Oracle script

Posted on 2008-10-28
Last Modified: 2013-12-19
Hello Oracle experts;

In Oracle RDB you have commends like:

SQL> attach 'filename stl_rdb';
SQL> sho tables
User tables in database with filename stl_rdb

SQL> sho table scrap_weights
Information for table SCRAP_WEIGHTS
Comment on table SCRAP_WEIGHTS:
Scrap charge information for this heat.
Columns for table SCRAP_WEIGHTS:
Column Name                     Data Type        Domain
-----------                     ---------        ------
HEAT_ID                         CHAR(5)          HEAT_ID
 Oracle Rdb default: ' '
MATERIAL_NAME                   CHAR(16)         MATERIAL_NAME
 Oracle Rdb default: ' '
SORT_ORDER                      INTEGER          SORT_ORDER
 Oracle Rdb default: 0
STD_WT                          REAL             STD_WT
 Oracle Rdb default: -123456789012345678.0
MAN_DMND_WT                     REAL             MAN_DMND_WT
 Oracle Rdb default: -123456789012345678.0
CALC_DMND_WT                    REAL             CALC_DMND_WT
 Oracle Rdb default: -123456789012345678.0
ORIG_LOG_WT                     REAL             ORIG_LOG_WT
 Oracle Rdb default: -123456789012345678.0
CHRGD_MAN_LOG_WT                REAL             CHRGD_MAN_LOG_WT
 Oracle Rdb default: -123456789012345678.0
CHRGD_RS_LOG_WT                 REAL             CHRGD_RS_LOG_WT
 Oracle Rdb default: -123456789012345678.0
CHRGD_COMP_LOG_WT               REAL             CHRGD_COMP_LOG_WT
 Oracle Rdb default: -123456789012345678.0
TIME_STAMP                      DATE VMS         TIME_STAMP
 Oracle Rdb default: CURRENT_TIMESTAMP
ORIG_CALC_DMND_WT               REAL             CALC_DMND_WT
 Oracle Rdb default: -123456789012345678.0
Table constraints for SCRAP_WEIGHTS:
 No Constraints found
Constraints referencing table SCRAP_WEIGHTS:
 No Constraints found
Indexes on table SCRAP_WEIGHTS:
SCRAP_WEIGHTS_KEY               with column HEAT_ID
                                and column MATERIAL_NAME
  No Duplicates allowed
  Type is Sorted
  Key suffix compression is DISABLED
  Node size  610
  Percent fill  90
 Store clause:          STORE IN SCRAP_WEIG_IDX
           (THRESHOLDS ARE ( 68,  68,  68 ))
 Partition information for index:
  Partition: (1) SYS_P00077
   Storage Area: SCRAP_WEIG_IDX
         Thresholds are (68, 68, 68)
Storage Map for table SCRAP_WEIGHTS:
Triggers on table SCRAP_WEIGHTS:
No triggers found

My question to you today is there a simple query in Oracle that will give you the same information as

SQL> sho table scrap_weights
SQL> sho tables

Can you please get back to me I need this ASAP

Thank you so much for your time

Question by:algotube
  • 2
  • 2
LVL 29

Accepted Solution

MikeOM_DBA earned 500 total points
ID: 22824552


PS: Wherever a view is prefixed by 'user_{view}', you can also query 'all_{view}' and 'dba_{view}'.

-- sho tables
select * from tab;
-- or --
select * from user_tables;
-- Table constraints for SCRAP_WEIGHTS:
select * from user_constraints
 where table_name='SCRAP_WEIGHTS';
-- Constraints referencing table SCRAP_WEIGHTS:
select * from user_constraints a
 where exists (
  select 'T' from user_constraints b
   where b.table_name='SCRAP_WEIGHTS'
     and b.constraint_name = a.r_constraint_name);
-- Indexes on table SCRAP_WEIGHTS:
select * from user_indexes
 where table_name='SCRAP_WEIGHTS';
-- Triggers on table SCRAP_WEIGHTS:
Select * from user_triggers
 where table_name='SCRAP_WEIGHTS';
-- Additional information in:
select * from user_tables
 where table_name='SCRAP_WEIGHTS';

Open in new window

LVL 29

Expert Comment

ID: 22824567

An easier way is to installl the EM dbconsole and view the information there.


Author Comment

ID: 22825394
Excellent MikeOM_DBA thank you so much.............. great stuff.........................

Author Closing Comment

ID: 31510804
Thank you for the fast response, very much appreciated.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question