Pro Oracle Database 11g Administration (30 page)
create table cwp_user_profile_test
as select * from cwp_user_profile
where 1=2;
You can also specify that no redo be logged when a CTAS table is created. For large data sets, this can reduce the amount of time required to create the table:
create table cwp_user_profile_101910
nologging
as select * from cwp_user_profile;
Be aware that using the CTAS technique with the NOLOGGING clause creates the table as NOLOGGING
and doesn’t generate the redo required to recover the data that populates the table as the result of the SELECT statement. Also, if the tablespace is defined to be NOLOGGING (in which the CTAS table is being created), then no redo is generated. In these scenarios, you can’t restore and recover your table in the event a failure occurs before you’re able to back up the table. If your data is critical, then don’t use the NOLOGGING clause.
You can also specify parallelism and storage parameters. Depending on the number of CPUs, you may see some performance gains:
create table cwp_user_profile_101910
nologging
tablespace staging_data
parallel 2
as select * from cwp_user_profile_tab;
■
Note
The CTAS technique doesn’t create any indexes or triggers. You have to create indexes and triggers separately if you need those objects from the original table.
Modifying a Table
Altering a table is a common task. New requirements often mean that you need to rename, add, drop or change column data types. In development environments, changing a table is often a trivial task: you often don’t have large quantities of data or hundreds of users simultaneously accessing a table.
However, for active production systems, you need to understand the ramifications of trying to change tables that are currently being accessed and/or are already populated with data.
146
CHAPTER 7 ■ TABLES AND CONSTRAINTS
Obtaining the Needed Lock
When you modify a table, you must have an exclusive lock on the table. One issue is that if a DML
transaction has a lock on the table, you can’t alter the table. In this situation, you receive this error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired The prior error message is somewhat confusing in that it leads you to believe that you can resolve the issue by acquiring a lock with NOWAIT. However, this is a generic message that is generated when the DDL you’re issuing can’t obtain an exclusive lock on the table. In this situation, you have a few options:
• After issuing the DDL command and receiving the ORA-00054 error, rapidly press the / (forward slash) key repeatedly in hopes of modifying the table between transactions.
• Shut down the database and start it in restricted mode, modify the table, and then open the database for normal use.
• In Oracle Database 11
g
and higher, set the DDL_LOCK_TIMEOUT parameter.
The last item in the previous list instructs Oracle to repeatedly attempt to run a DDL statement until it obtains the required lock on the table. You can set the DDL_LOCK_TIMEOUT parameter at the system or session level. This next example instructs Oracle to repeatedly retry to obtain a lock for 100 seconds: SQL> alter session set ddl_lock_timeout=100;
The default value for the system-level DDL_LOCK_TIMEOUT initialization parameter is 0. If you want to modify the default behavior for every session in the system, issue an ALTER SYSTEM SET statement. The following command sets the default timeout value to 10 seconds for the system: SQL> alter system set ddl_lock_timeout=10 scope=both;
Renaming a Table
There are a couple of reasons for renaming a table:
• To make the table conform to standards
• To better determine whether the table is being used before you drop it This example renames a table from INV_MGMT to INV_MGMT_OLD:
SQL> rename inv_mgmt to inv_mgmt_old;
If successful, you should see this message:
Table renamed.
Adding a Column
Use the ALTER TABLE ... ADD statement to add a column to a table. This example adds a column to the INV table:
SQL> alter table inv add(inv_count number);
If successful, you should see this message:
147
CHAPTER 7 ■ TABLES AND CONSTRAINTS
Table altered.
Altering a Column
Occasionally, you need to alter a column to adjust its size or change its data type. Use the ALTER TABLE
... MODIFY statement to adjust the size of a column. This example changes the size of a column to 256
characters:
SQL> alter table inv modify inv_desc varchar2(256);
If you decrease the size of a column, first ensure that no values exist that are greater than the decreased size value:
SQL> select max(length(
SQL> select
You can also alter the column to have a default value. The default value is used any time a record is inserted into the table but no value is provided for a column:
SQL> alter table inv modify(inv_desc default 'No Desc');
If you want to remove the default value of a column, then set it to be NULL: SQL> alter table inv modify(inv_desc default NULL);
Sometimes you need to change a table’s data type: for example, a column that was originally incorrectly defined as a VARCHAR2 needs to be changed to a NUMBER. Before you change a column’s data type, first verify that all values for an existing column contain only valid numeric values. Here’s a simple PL/SQL script to do this:
create or replace function isnum(v_in varchar2)
return varchar is
val_err exception;
pragma exception_init(val_err, -6502); -- char to num conv. error
scrub_num number;
begin
scrub_num := to_number(v_in);
return 'Y';
exception when val_err then
return 'N';
end;
/
You can use the ISNUM function to detect whether data in a column is numeric. The function defines a PL/SQL pragma exception for the ORA-06502 character-to-number conversion error. When this error is encountered, the exception handler captures it and returns an N. If the value passed in to the ISNUM
function is a number, then a Y is returned. If the value can’t be converted to a number, then an N is returned. After you’ve defined the function, you can run it as follows: 148
CHAPTER 7 ■ TABLES AND CONSTRAINTS
SQL> select hold_col from stage where isnum(hold_col)='N';
Similarly, when you modify a character column to a DATE or TIMESTAMP data type, it’s prudent to first check to see whether the data can be successfully converted. Here’s a function that does that: create or replace function isdate(p_in varchar2, f_in varchar2)
return varchar is
scrub_dt date;
begin
scrub_dt := to_date(p_in, f_in);
return 'Y';
exception when others then
return 'N';
end;
/
When you call the ISDATE function, you need to pass it a valid date-format mask: SQL> select hold_col from stage where isdate(hold_col,'YYYYMMDD')='N'; Renaming a Column
There are a couple of reasons to rename a column:
• Sometimes requirements change, and you want to modify the column name to better reflect what the column is used for.
• If you’re planning to drop a column, it doesn’t hurt to rename the column first to better determine whether any users or applications are accessing the column.
Use the ALTER TABLE ... RENAME statement to rename a column:
SQL> alter table inv rename column inv_count to inv_amt;
Dropping a Column
Tables sometimes end up having columns that are never used. This may be because the initial requirements changed or were inaccurate. If you have a table that contains an unused column, you should consider dropping it. If you leave an unused column in a table, you may run into issues with future DBAs not knowing what the column is used for, and the column can potentially consume space unnecessarily.
Before you drop a column, I recommend that you first rename it. Doing so gives you an opportunity to discover whether any users or applications are using the column. After you’re confident the column isn’t being used, first make a backup of the table using Data Pump export, and then drop the column.
The previous strategies provide you with options if you drop a column and then subsequently realize that it’s needed.
To drop a column, use the ALTER TABLE ... DROP statement:
SQL> alter table inv drop (inv_name);
Be aware that the DROP operation may take some time if the table from which you’re removing the column contains a large amount of data. This time lag may result in transactions being delayed while the table is being modified (because the ALTER TABLE statement locks the table). In scenarios like this, you may want to first mark the column as unused and then later drop it when you have a maintenance window:
149
CHAPTER 7 ■ TABLES AND CONSTRAINTS
SQL> alter table inv set unused (inv_name);
When you mark a column as unused, it no longer shows up in the table description. The SET UNUSED
clause doesn’t incur the overhead associated with dropping the column. This technique allows you to quickly stop the column from being seen or used by SQL queries or applications. Any query that attempts to access an unused column receives the following error:
ORA-00904: ... invalid identifier
You can later drop any unused columns when you’ve scheduled some downtime for the application.
Use the DROP UNUSED clause to remove any columns marked as UNUSED.
SQL> alter table inv drop unused columns;
Displaying Table DDL
Sometimes DBAs do a poor job of documenting what DDL is used when creating or modifying a table.
Normally, you should maintain the database DDL code in a source-control repository or in some sort of modeling tool. If your shop doesn’t have the DDL source code, then there are a few ways that you can manually reproduce DDL:
• Query the data dictionary
• Use the exp and imp utilities
• Use Data Pump
• Use the DBMS_METADATA package
Back in the olden days, say version 7 and earlier, DBAs often wrote SQL that queries the data dictionary to attempt to extract the DDL required to re-create objects. Although this method was better than nothing, it was often prone to errors because the SQL didn’t account for every object-creation feature.
The exp and imp utilities are useful for generating DDL. The basic idea is that you export the object in question and then use the imp utility with the SCRIPT or SHOW option to display the DDL. This is a good method, but you often have to manually edit the output of the imp utility to produce the desired DDL.
The Data Pump utility is an excellent method for generating the DDL used to create database objects. Using Data Pump to generate DDL is covered in detail in Chapter 13.
The GET_DDL function of the DBMS_METADATA package is usually the quickest way to display the DDL
required to create an object. This example shows how to generate the DDL for a table named INV: SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLE','INV') from dual;
Here is some sample output:
DBMS_METADATA.GET_DDL('TABLE','INV')
--------------------------------------------------------------------------------
CREATE TABLE "DARL"."INV"
( "INV_ID" NUMBER,
"INV_DESC" VARCHAR2(30),
"INV_COUNT" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"
150
CHAPTER 7 ■ TABLES AND CONSTRAINTS
The following SQL statement displays all the DDL for the tables in a schema: select
dbms_metadata.get_ddl('TABLE',table_name)
from user_tables;
If you want to display the DDL for a table owned by another user, add the SCHEMA parameter to the GET_DDL procedure.
select
dbms_metadata.get_ddl(object_type=>'TABLE', name=>'INVENTORY', schema=>'INV_APP') from dual;
■
Note
You can display the DDL for almost any database object type, such as INDEX, FUNCTION, ROLE, PACKAGE, MATERIALIZED VIEW, PROFILE, CONSTRAINT, SEQUENCE, SYNONYM, and so on.
Dropping a Table
If you want to remove an object such as a table from a user, use the DROP TABLE statement. This example drops a table named INVENTORY:
SQL> drop table inventory;
You should see the following confirmation:
Table dropped.
If you attempt to drop a parent table that has a primary key or unique keys referenced as a foreign key in a child table, you see an error such as
ORA-02449: unique/primary keys in table referenced by foreign keys
You need to either drop the referenced foreign-key constraint(s) or use the CASCADE CONSTRAINTS
option when dropping the parent table:
SQL> drop table inventory cascade constraints;
You must be the owner of the table or have the DROP ANY TABLE system privilege to drop a table. If you have the DROP ANY TABLE privilege, you can drop a table in a different schema by prepending the schema name to the table name:
SQL> drop table inv_mgmt.inventory;
If you don’t prepend the table name with a user name, Oracle assumes you’re dropping a table in your current user.
■
Tip
If you’re using Oracle Database 10g or higher, keep in mind that you can undrop an accidentally dropped table.
151
CHAPTER 7 ■ TABLES AND CONSTRAINTS
Undropping a Table
Suppose you accidentally drop a table, and you want to restore it. First, verify that the table you want to restore is in the recycle bin:
