Tables

2017-11-03 09:05:07

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==

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.

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.

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.

Case Sensitivity in the Data Dictionary

Oracle's default behavior is to uppercase the names of any objects that you create. For example, you may create a table giving the lowercase name employee :

create table employee ( . . . )

Oracle will uppercase this table name, converting it to EMPLOYEE before creating the table, and thereafter you'll need to use the uppercase version of the table name when querying the data dictionary. Because it's so common to type in lowercase, many of the scripts in this chapter use the UPPER function in the WHERE clause to relieve you of the burden of remembering to hit Caps Lock before you type.

WHERE owner = UPPER('&owner_name') AND table_name = UPPER('&table_name')

This WHERE clause, taken from Example 10-2, automatically uppercases any owner and table names that you provide. Bear in mind though, that it is possible, using double quotation marks, to prevent object names from being translated to uppercase. For example, the quotation marks around the identifier in the following statement cause Oracle to leave the name in lowercase as it is specified:

CREATE TABLE "employee" ( . . . )

If your scripts automatically uppercase the owner and table names that you supply, which is usually a convenience, you'll have a difficult time working with tables having lowercase or mixed-case names. If you were determined enough, you could write a script that would leave names alone when you quoted them, possibly via CASE expressions, thus mimicking the behavior of Oracle. Most DBAs though, myself included, prefer working with identifiers that are all uppercase and don't need to be quoted.

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 ) .

Case Sensitivity in the Data Dictionary

Категории