Page 143
reuse option. This option without the size option means destroy the contents of any existing file with this name and give the file to this database. When both options are specified, the file is created if it does not exist, and changes the size if it does exist. Specifying the size option alone causes the file to be created if it doesn't exist and an error message to occur if it does.
FIG. 6.6
Creating tablespace
command syntax.
The rename option enables you to change the name of one or more of the existing files in the tablespace. This option does not actually change the names of the files. It just associates the new name with the file. To actually change the name, issue the alter tablespace offline command, and then rename the files in the operating system. You can then rename the file(s) by using the Alter Tablespace command. The Alter Tablespace online command is then issued.
The offline option takes the tablespace out of production. If the immediate option is used, it performs the task even if users are logged on. Using the normal option causes Oracle to wait until the users have stopped using the tablespace. The online restores the tablespace to its normal mode.
You can drop the tablespace by using the Drop Tablespace command followed by the name of the tablespace. The command has an option called including contents. When this option is specified, the tablespace and all of its contents will be eliminated. Without the option, the tablespace must be empty before it can be dropped.
A sequence is an Oracle device used to generate a unique sequential number. This is a very good tool to use for generating sequential payroll numbers, work order numbers, or artificial primary keys. I use artificial primary keys in a transformer database I developed. This database does not have a unique value in any of the fields. Several manufacturers can use the serial number, which is the most logical key. This necessitates the use of the artificial key added to each record as they are added to the table. I use the sequence to give me the unique number.
Listing 6.23 illustrates the creation of a sequence. In this example, the name of the sequence is pay_num_seq. It is used as a qualifier for the NextVal and CurrVal values. The former value generates the next unique number. The number is guaranteed to be unique. The latter value returns the last number generated by the sequence. The Listing's second statement demonstrates the use of this command to populate a new record with an artificial key generated by the sequence.
Page 144
Listing 6.23 L_06_23.SQLCreating and Using a Sequence
SQL> create sequence pay_num_seq 2 increment by 1 start with 100; Sequence created. SQL> insert into employee (payroll_number) 2 values (pay_num_seq.nextval); 1 row created. SQL> drop sequence pay_num_seq; Sequence dropped.
A database trigger is a useful tool used to automatically populate columns of a database. The trigger is a stored procedure written in PL/SQL that is associated with a table and executed when a specified operation occurs to the table.
TIP |
I have used database triggers to populate an artificial key with a new sequential number when a record was inserted. Because I defined the procedure as a database trigger, I did not have to populate the column with a value through the code in my application. |
You can also use triggers to perform other complex functionsif you want to print a letter to a customer welcoming him after he opens an account, for example. You can accomplish such a procedure by creating a database trigger that fires when you insert a customer record into the customer file. The trigger contains the PL/SQL command necessary to produce the letter.
Users granted the create trigger or create any trigger privileges can create triggers. The event options that can cause the trigger to fire are the insert, update, or delete table operations. Another option will cause the trigger to fire before or after the above table operations. The trigger I created to populate the artificial key fired before the insert operation. If it did not, the not null constraint for the artificial primary key column would have prevented the record from being populated. The trigger to create the report fired after the record was inserted. Specifying an of clause on an update trigger causes the trigger to fire only when specific columns are updated. Figure 6.7 illustrates the syntax of the create trigger command.
Listing 6.24 also illustrates the syntax of the Create Trigger command. The words old and new are default qualifiers that you can use in the block to contain values before and after the procedure. The command enables you to change these qualifier names, old and new, to different names. The for each row option causes the trigger to be fired for each row affected by the operation. The when clause allows the trigger to be executed when specific conditions are met. You can disable the trigger by using the Alter Trigger and Alter Table commands. You can drop the trigger by using the Drop Trigger command.
Page 145
FIG. 6.7
Create trigger syntax.
Listing 6.24 L_06_24.SQLCreating a Database Trigger Called payroll_numb
SQL> create or replace trigger seq_trg 2 before insert on employee 3 referencing new as new 4 for each row 5 declare 6 seq number; 7 begin 8 select pay_num_seq.nextval into seq from dual; 9 :new.payroll_number := seq; 10 end; 11 / Trigger created.
The new Oracle8 database has several features that should be mentioned. The first is the ability to partition tables and indexes. This allows you to break the database tables into smaller pieces. You have the ability to break a large table into separate partitions that are located in separate tablespaces. These partitions can be located on different disk drives and at different locations.
Partitioning has advantages. It spreads the I/O across more drives. For instance, if the table is contained on four disk drives, each drive would have one-fourth the I/O as compared to having the data on one drive. This reduces contension and increases response time for active databases. Another advantage involves data recovery and backup. Since each partition is independent of the other, partitions can be backed up and recovered independently. In our example of four partitions, three partitions can be active while the fourth is being backed up or recovered. It also reduces the losses due to corruption. If a drive goes bad or some other problem occurs, the problem is confined to the partition.
To partition a table, you must tell Oracle8 how the data is partitioned and where to place the data. This is initially done using a partition statement at the end of the create statement. This statement has parameters that divide a table's data into partitions. Listing 6.25 contains a reference only create statement that is not meant for execution.