Oracle Tips for beginners

Table of Contents

  • Accessing relevant data using sqlplus
  • Accessing user tables
  • Obtaining table information
  • Obtaining table column layout

Accessing Database data using Oracle SQLPlus

Oracle provides utilities which users can utilize provided appropriate authority has been established. Making sure that on a UNIX server, the ORACLE_HOME, ORACLE_PATH, ORACLE_BASE, ORACLE_SID, TWO_TASK and TNS_ADMIN variables are set.

Once can use the sqlplus command on a given server to access the Oracle Database and acquire information.

On the unix command prompt provided the DBA has given you log on credentials, you can simply run the sqlplus command:

  • sqlplus userid/password@dbserver

To exit from the utility type quit at the Oracle command prompt.

      sqlplus $SCHEMA_N1/$SCHEMA_P@$DBSERVER
      
      sql>show user
      USER is "TESTER"

Obtaining Table information

Through the facility of the sqlplus tool, you can obtain table information that can be relevant for your needs. Simply typing DESC command on the prompt, will list information such as the column names, whether it is nullable and the type of column.

SQL> desc tablename;

In addition, you can obtain statistics and other relevant information on table by querying the USER_TABLES view using SQL. To get a listing of available fields simply use the DESCribe command listed above: desc user_tables;

Obtaining Column Infomation

Column information can be found in the system tables under user_tab_cols. You can build similar query to obtain a layout information that meets your needs if the DESC tablename is not sufficient enough.

     SELECT TC.TABLE_NAME
          , TC.COLUMN_NAME
          , CASE TC.DATA_TYPE WHEN 'NUMBER' THEN
                 CASE TC.DATA_PRECISION WHEN 38 THEN
                      'INT TYPE'
                 ELSE
                      TC.DATA_TYPE||'('||
                      TRIM(TO_CHAR(TC.DATA_PRECISION))||','||
                      TRIM(TO_CHAR(TC.DATA_SCALE))||')'
                 END
            WHEN 'CHAR' THEN 
                 TC.DATA_TYPE||'('||
                 TRIM(TO_CHAR(TC.DATA_LENGTH))||')'
            WHEN 'TIMESTAMP(6)' THEN
                 'TIMESTAMP'
            ELSE
                 TC.DATA_TYPE||
                 TO_CHAR(TC.DATA_PRECISION)
            END "VAL"
          , TC.NULLABLE
       FROM USER_TAB_COLS TC
      WHERE TC.TABLE_NAME = 'TABLENAME'
      ORDER BY TC.COLUMN_ID
     /

Displaying User Objects

At time when browsing through schema and you don't know what objects are available, getting a list is sometimes beneficial. You can use the SQL below to get listing of objects and types.

SQL
COL OBJECT_TYPE FORMAT A20 HEADING "OBJECT|TYPE"
COL OBJECT_NAME FORMAT A20 HEADING "OBJECT|NAME"
BREAK ON OBJECT_TYPE SKIP 1
SELECT OBJECT_TYPE
, OBJECT_NAME
FROM USER_OBJECTS
ORDER BY OBJECT_TYPE;

SQL - Select contents of user_tables

    SELECT * 
         FROM USER_TABLES;

SQL - Select contents of user_indexes

    SELECT * 
         FROM USER_INDEXES;

SQL - Select contents of user_sequences

    SELECT *
         FROM USER_SEQUENCES;

SQL - Select contents of user_triggers

     SELECT * 
         FROM USER_TRIGGERS;

SQL - DISABLE CONSTRAINTS

#!/usr/bin/ksh

# +--------------------------------------------------------+
# | Script:  disable_constraints.ksh                       |
# |                                                        |
# | Description: This scripts basically disables the table |
# |              contraints on the specified schema.       |
# +--------------------------------------------------------+
# | Following Work variables need defined to region:       |
# |                                                        |
# +--------------------------------------------------------+


DT=`date "+%Y%m%d_%H%M%S"`
LOGFL=disable_constraints_${DT}.log

export userid=????????
export password=???????????
export region=?????????

echo "Constraints Disable for schema: ${userid} started"

sqlplus -s ${userid}/${password}@${region} <<EOF

spool ${LOGFL}

show user

declare
   vTAB_DIS     VARCHAR2(100);
   type v_array is varray(10000) of varchar2(30);
   tbl_array v_array := v_array();
   tbl_const v_array := v_array();
   v_cnt integer     := 0;
begin
--------------------------------------------------------
--- This loop loads arrays and disables constraints
--------------------------------------------------------
   FOR indata in (select table_name, constraint_name, status
                    from user_constraints
                   where constraint_type ='R')
   LOOP
      v_cnt := v_cnt + 1;
      tbl_array.EXTEND;
      tbl_array(v_cnt) := indata.table_name;
      tbl_const.EXTEND;
      tbl_const(v_cnt) := indata.constraint_name;
      vTAB_DIS := 'ALTER TABLE ' || tbl_array(v_cnt)
                                 || ' DISABLE NOVALIDATE CONSTRAINT '
                                 || tbl_const(v_cnt) ;
      dbms_output.put_line('HELP: ' || vTAB_DIS);
      EXECUTE IMMEDIATE vTAB_DIS;
      vTAB_DIS := 'COMMIT';
      EXECUTE IMMEDIATE vTAB_DIS;
   END LOOP;
END;

/

select table_name, constraint_name, status
                    from user_constraints
                   where constraint_type ='R'

/

SPOOL OFF

quit

EOF

echo "Constraints Disable completed for schema: ${userid} "

exit 0

SQL - Enable Constraints

#!/usr/bin/ksh

# +--------------------------------------------------------+
# | Script:  enable_constraints.ksh                        |
# | Description: This scripts basically enables the table  |
# |              contraints on the specified schema.       |
# +--------------------------------------------------------+
# |                                                        |
# +--------------------------------------------------------+

DT=`date "+%Y%m%d_%H%M%S"`

export userid=???????
export password=??????
export region=????????
LOGFL=enable_constraints_${DT}.log


echo "Constraints enable for schema: ${userid} started "

sqlplus -s ${userid}/${password}@${region} <<EOF

spool ${LOGFL}

declare
   vTAB_DIS     VARCHAR2(100);
   type v_array is varray(10000) of varchar2(30);
   tbl_array v_array := v_array();
   tbl_const v_array := v_array();
   v_cnt integer     := 0;
begin
--------------------------------------------------------
--- This loop loads arrays and disables constraints
--------------------------------------------------------
   FOR indata in (select table_name, constraint_name, status
                    from user_constraints
                   where constraint_type ='R')
   LOOP
      v_cnt := v_cnt + 1;
      tbl_array.EXTEND;
      tbl_array(v_cnt) := indata.table_name;
      tbl_const.EXTEND;
      tbl_const(v_cnt) := indata.constraint_name;
      vTAB_DIS := 'ALTER TABLE ' || tbl_array(v_cnt)
                                 || ' ENABLE CONSTRAINT '
                                 || tbl_const(v_cnt) ;
      dbms_output.put_line('HELP: ' || vTAB_DIS);
      EXECUTE IMMEDIATE vTAB_DIS;
      vTAB_DIS := 'COMMIT';
      EXECUTE IMMEDIATE vTAB_DIS;
   END LOOP;
END;

/

select table_name, constraint_name, status
                    from user_constraints
                   where constraint_type ='R'

/

spool off

quit

EOF

echo "Constraints Enabled completed for schema: ${userid}  "


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working