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 BINoJlQsAlRUfgMKjAAgAV7g==
SELECT table_name, tablespace_name FROM user_tables; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ EMPLOYEE_COPY USERS PROJECT_HOURS USERS BIN$3oJlQsAlRUfgMKjAAgAV7g==$0 USERS . . .
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 all s 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.
|
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.
Table 10-2. Key columns in the all_tab_columns view
|
Column |
Description |
|---|---|
|
table_name |
Name of the table containing the column. You'll usually want to use this in your WHERE clause to restrict your output to only those columns from a table of interest. |
|
column_id |
Sequence number, beginning from 1 for a table's first column. You can use column_id in an ORDER BY clause to report columns in the same order as the DESCRIBE command. |
|
column_name |
Name of each column. |
|
data_type |
Datatype of each column. |
|
data_length |
Column length, in bytes, for columns having datatypes constrained by length. These datatypes include VARCHAR2, CHAR, NVARCHAR2, NCHAR, and RAW. |
|
data_precision |
Precision for NUMBER columns. |
|
data_scale |
Scale for NUMBER columns. |
|
nullable |
Whether a column is nullable. This will be either Y or N . |
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 ) .
