Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

Managing Tables

The table is the basic building block of any database system.  We discussed tables in Chapter 1 and talked about normalizing data to remove redundancy.  In this section, we are going to discuss the different types of tables inside an Oracle database and how they are created and used.  We need this information as we progress into manipulating the data in tables with the INSERT, UPDATE and DELETE  statements.  In computer parlance, updates are DML

You create a table by defining the column names and their data types.   Columns can be any of the data types discussed in Chapter 2, to include user defined data types.  When we loaded the PUBS schema, we ran the pubs_db.sql script that contained the commands to create the tables.  Let’s look at the AUTHOR table. 

CREATE TABLE AUTHOR
(
  AUTHOR_KEY                VARCHAR2(11),
  AUTHOR_LAST_NAME          VARCHAR2(40),
  AUTHOR_FIRST_NAME         VARCHAR2(20),
  AUTHOR_PHONE              VARCHAR2(12),
  AUTHOR_STREET             VARCHAR2(40), 
  AUTHOR_CITY               VARCHAR2(20), 
  AUTHOR_STATE              VARCHAR2(2), 
  AUTHOR_ZIP                VARCHAR2(5),  
  AUTHOR_CONTRACT_NBR       NUMBER(5)    
);
 

This statement creates a table named AUTHOR that contains nine columns defined within the parentheses.  Each column definition is separated by a coma and contains the data type and size of the column.  The CREATE TABLE command can be quite involved, defining the table storage location and constraints.

CREATE TABLE "PUBS"."EDITOR"
(
  "EDITOR_KEY" VARCHAR2(9) NOT NULL,
  "EDITOR_LAST_NAME" VARCHAR2(30) NOT NULL,
  "EDITOR_FIRST_NAME" VARCHAR2(30) NOT NULL,
  "HIRE_DATE" DATE DEFAULT SYSDATE NOT NULL, 
  "EDITOR_ACTIVE" CHAR(1) DEFAULT 'Y',
   CONSTRAINT "EDITOR_PK" PRIMARY KEY("EDITOR_KEY")
      USING INDEX 
      TABLESPACE "INDX"
)  TABLESPACE “USERS”
 

In the example above, I created a table called EDITOR in the PUBS schema or user.  It has five columns, all of which will not allow NULL values except for editor_active.  The hire_date column will default to the SYSDATE, if a date is not provided when a row is inserted.  Likewise, the editor_active column will default to Y.  I defined a primary key  constraint on the editor_key called editor_pk.  The editor_pk constraint uses an index, which will be built in the INDX tablespace.  The table itself will be built in the USERS tablespace. 

We are not going to get that involved with our table creation at this time, since most of those items belong in the realm of the DBA or will be covered in detail in Chapter 5.  By default, the table will be created in the user’s default tablespace defined when the user was created.  Also, columns not defined as NOT NULL will accept NULL values.  If I wanted to see my user information, I could query the user_users view with this command:

SQL> desc user_users; 

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

SQL> select default_tablespace from user_users; 

DEFAULT_TABLESPACE
------------------------------
USERS

Since the user views will only show me information that belongs to me, the user_users view will only display my information, since I am the only user defined as me. 

Once we have created the table, we can begin inserting data.  Sometimes, I want to create a table to hold some data temporarily.  This would allow me to temporarily create a table with some intermediate data in it, that I then could repeatedly query from.  This is very easy with something called CTAS (pronounced “sea-taz”).


The above text is an excerpt from:

Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

by John Garmany
 


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.