Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

10.3 Tables

When it comes to
looking at a table and its column definitions, you need to be
concerned with two data dictionary views:

all_tables all_tab_columns
The
all_tables view
contains one row for each table. You can use this view to get a quick
list of tables you own or to which you have been granted some type of
access. all_tables has a one-to-many relationship
to

all_tab_columns, which
contains one record for each column in a table.
all_tab_columns is the source for information on
column names, datatypes, default values, etc.

10.3.1 Listing Tables You Own

To get a quick list of tables you own, it's easier
to use the user_tables view than
all_tables. Remember that
user_tables shows you only the tables you own. To
see a list of your tables, simply select the
table_name column and any other columns containing
information of interest:

SELECT table_name, tablespace_name
FROM user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMPLOYEE_COPY USERS
PROJECT_HOURS USERS
BIN$3oJlQsAlRUfgMKjAAgAV7g==$0 USERS
. . .

10.3.1.1 The recycle bin

Oops! What's that
BIN$3oJlQsAlRUfgMKjAAgAV7g==$0 business all about?
Did I give a table a mixed up name like that? No, I
didn't. The
BIN$3oJlQsAlRUfgMKjAAgAV7g==$0 table that you see
represents a table I deleted. Oracle Database
10 g introduced a recycle bin for deleted
database objects, which somewhat complicates the task of querying the
data dictionary views. Filter out any recycle bin objects by adding
WHERE dropped = 'NO' to the query:

SELECT table_name, tablespace_name
FROM user_tables
WHERE dropped = 'NO';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMPLOYEE_COPY USERS
PROJECT_HOURS USERS
PROJECT USERS
. . .

10.3.1.2 Tables owned by other users

To see tables owned by other users, you need to query the
all_tables view. Be sure to qualify your query by
specifying the owner's username in the WHERE clause.

SELECT table_name
FROM all_tables
WHERE owner = 'SYSTEM'
AND dropped = 'NO';

10.3.1.3 External tables

External tables, which I described near the end of the previous
chapter, are exposed through
user/all/dba_tables,
as most other tables are. However, if you wish to access attributes
specific to external tables, such as the field definitions within
their access parameters, you'll need to query
user/all/dba_external_tables:

COLUMN reject_limit FORMAT A10
COLUMN access_parameters FORMAT A60 WORD_WRAPPED
SELECT reject_limit, access_parameters
FROM user_external_tables
WHERE table_name = 'EMPLOYEE_COMMA';
REJECT_LIM ACCESS_PARAMETERS
---------- ------------------------------------------------------------
UNLIMITED RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'employee_comma.bad'
LOGFILE 'employee_comma.log'
FIELDS (
employee_id CHAR(255) TERMINATED BY ",",
employee_billing_rate CHAR(255) TERMINATED BY ",",
employee_hire_date CHAR(255) TERMINATED BY ","
DATE_FORMAT DATE MASK "MM/DD/YYYY",
employee_name CHAR(255) TERMINATED BY "," OPTIONALLY
ENCLOSED BY '"'
)

10.3.1.4 Object tables

Object tables are exposed through their own set of views and
aren't included in
user/all/dba_tables.
To learn about object tables in your database, query
all_object_tables:

SELECT owner, table_name, tablespace_name
FROM all_object_tables
WHERE dropped = 'NO';
OWNER TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
SYS KOTTD$ SYSTEM
SYS KOTTB$ SYSTEM
SYS KOTAD$ SYSTEM
. . .

10.3.1.5 A combined list of tables

Sometimes it can be inconvenient to have database tables split across
two views, all_tables and
all_object_tables. When that's
the case, you can generate a list of all tables
you're interested in, regardless of type, querying
the rather oddly named all_all_tables view:

SELECT owner, table_name, tablespace_name
FROM all_all_tables
WHERE owner = 'GENNICK '
AND dropped = 'NO';
OWNER TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
GENNICK EMPLOYEE_COMMA
GENNICK EMPLOYEE_FIXED
GENNICK TEST2 USERS
GENNICK ODD_NUMS USERS Don't confuse the two alls in
all_all_tables. The first all
indicates that the view is from the all family.
There are also user_all_tables and
dba_all_tables. The second all
indicates that the view returns a combined list of all table types.

You can query all_views to see the underlying SELECT statement for all_all_tables: SET LONG 6000 SELECT text FROM all_views WHERE view_name = 'ALL_ALL_TABLES' AND owner = 'SYS'; If you issue this query, you'll see that all_all_tables is the UNION of two SELECTs, one against all_tables and the other against all_object_tables. The text column is of type LONG, and the SET LONG command causes SQL*Plus to show more than the default, first 80 bytes.

10.3.2 Listing Column Definitions for a Table

Query the all_tab_columns view
to see detailed information about the columns in a table. Table 10-2 describes some of the key columns in this
view, explaining how you might use them in a query.

Example 10-2 shows a simple script that queries
all_tab_columns for a table you specify. The
COLUMN commands set column widths short enough so the report will fit
within 80 characters. Substitution variables in the WHERE clause
cause SQL*Plus to prompt you for owner and table names.

Example 10-2. A script to list column definitions for a table

COLUMN column_name FORMAT A30 HEADING 'Column Name'
COLUMN data_type FORMAT A17 HEADING 'Data Type'
COLUMN not_null FORMAT A9 HEADING 'Nullable?'
SELECT column_name,
DECODE (data_type,
'VARCHAR2','VARCHAR2 (' || TO_CHAR(data_length) || ')',
'NVARCHAR2','NVARCHAR2 (' || TO_CHAR(data_length) || ')',
'CHAR','CHAR (' || TO_CHAR(data_length) || ')',
'NCHAR','NCHAR (' || TO_CHAR(data_length) || ')',
'NUMBER',
DECODE (data_precision,
NULL, 'NUMBER',
'NUMBER (' || TO_CHAR(data_precision)
|| ',' || TO_CHAR(data_scale) || ')'),
'FLOAT',
DECODE (data_precision,
NULL, 'FLOAT',
'FLOAT (' || TO_CHAR(data_precision) || ')'),
'DATE','DATE',
'LONG','LONG',
'LONG RAW','LONG RAW',
'RAW','RAW (' || TO_CHAR(data_length) || ')',
'MLSLABEL','MLSLABEL',
'ROWID','ROWID',
'CLOB','CLOB',
'NCLOB','NCLOB',
'BLOB','BLOB',
'BFILE','BFILE',
data_type || ' ???') data_type,
DECODE (nullable, 'N','NOT NULL') not_null
FROM all_tab_columns
WHERE owner = UPPER('&owner_name')
AND table_name = UPPER('&table_name')
ORDER BY column_id;

No ACCEPT commands are in Example 10-2. Instead, the
script relies on SQL*Plus's default behavior when it
encounters the two substitution variables in the WHERE clause:

WHERE owner = UPPER('&owner_name')
AND table_name = UPPER('&table_name') As each variable is encountered, SQL*Plus prompts you to supply a
value. The value you supply is used once. Because only one ampersand
is used in front of each variable name, values are
not preserved for use beyond the single
substitution.

The results of running Example 10-2 are as follows:

SQL> @ex10-2
Enter value for owner_name: gennick
old 29: WHERE owner = UPPER('&owner_name')
new 29: WHERE owner = UPPER('gennick')
Enter value for table_name: employee
old 30: AND table_name = UPPER('&table_name')
new 30: AND table_name = UPPER('employee')
Column Name Data Type Nullable?
------------------------------ ----------------- ---------
EMPLOYEE_ID NUMBER NOT NULL
EMPLOYEE_NAME VARCHAR2 (40)
EMPLOYEE_HIRE_DATE DATE
EMPLOYEE_TERMINATION_DATE DATE
EMPLOYEE_BILLING_RATE NUMBER (5,2) The SELECT statement in the script looks complex, but
that's because the statement must accommodate
different column data types. The complication comes from
the
DECODE statement that starts out
like this:

DECODE (data_type,
'VARCHAR2','VARCHAR2 (' || TO_CHAR(data_length) || ')',
'NVARCHAR2','NVARCHAR2 (' || TO_CHAR(data_length) || ')',
'CHAR','CHAR (' || TO_CHAR(data_length) || ')',
'NCHAR','NCHAR (' || TO_CHAR(data_length) || ')',
'NUMBER',
DECODE (data_precision,
NULL, 'NUMBER',
'NUMBER (' || TO_CHAR(data_precision)
|| ',' || TO_CHAR(data_scale) || ')'),
. . .

This long DECODE expression exists because some datatypes have a
length associated with them, some have a precision and scale, and
some have neither. The DECODE function call contains one expression
for each possible datatype, and that expression returns the
appropriate information for that datatype. Consider the
VARCHAR2
datatype, for example. All VARCHAR2 columns have a length associated
with them. To display that length, the following two expressions
(separated by commas) are included in the DECODE call:

'VARCHAR2','VARCHAR2 (' || TO_CHAR(data_length) || ')' The first expression is the string VARCHAR2. When DECODE is
evaluating a datatype that matches that string, it will return the
value of the second expression in the pair. In the case of VARCHAR2,
here is the second expression:

'VARCHAR2 (' || TO_CHAR(data_length) || ')' This second expression concatenates the string 'VARCHAR2
(' with the length, then follows that with a closing
parentheses. The result will be a string resembling the one shown
here:

VARCHAR2 (40) The
NUMBER and
FLOAT datatypes add a bit more complexity. A NUMBER, for example, can
be defined as floating-point or fixed-point. Floating-point numbers
have null values for data_precision and
data_scale. If a NUMBER field is floating-point,
the data_precision is null, and the nested DECODE
returns just NUMBER as the datatype. Otherwise,
the nested DECODE returns NUMBER
(precision,
scale).