Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
OCA /OCP Oracle Database 11g A ll-in-One Exam Guide- P89:There is an ever increasing demand for staff with IT industry certification. The benefits to employers are significant—they can be certain that staff have a certain level of competence—and the benefits to the individuals, in terms of demand for their services, are equally great. Many employers are now requiring technical staff to have certifications, and many IT purchasers will not buy from firms that do not have certified staff. | OCA OCP Oracle Database 11g All-in-One Exam Guide 836 Figure 23-1 Managing directories with SQL Plus To create an external table use the CREATE TABLE command with the keywords ORGANIZATION EXTERNAL. These tell Oracle that the table does not exist as a segment. Then specify the layout and location of the operating system file. For example create table new_dept deptno number 2 dname varchar2 14 loc varchar2 13 organization external type oracle_loader default directory jon_dir access parameters records delimited by newline badfile depts.bad discardfile depts.dsc log file depts.log fields terminated by missing field values are null location depts.txt This command will create an external table that will be populated by the DEPTS. TXT file shown in the section SQL Loader earlier in this chapter. The syntax for the Chapter 23 Moving and Reorganizing Data 837 ACCESS PARAMETERS is virtually identical to the SQL Loader controlfile syntax and is used because the TYPE has been set to ORACLE_LOADER. The specification for the DEFAULT DIRECTORY gives the Oracle directory where Oracle will look for the source datafile and where it will write the log and other files. External tables can be queried in exactly the same way as internal tables. Any SQL involving a SELECT will function against an external table they can be used in joins views and subqueries. They cannot have indexes constraints or triggers. Exercise 23-1 Use SQL Loader and External Tables In this exercise you will install and use SQL Loader to insert data into a table and also to generate the CREATE TABLE script for an external table. 1. Connect to your database as user SYSTEM in the examples the SYSTEM password is ORACLE with SQL Plus. 2. Create a table to use for the exercise create table names first varchar2 10 last varchar2 10 3. Using any editor that will create plain text files create a file names.txt with these values or similar John Watson Roopesh Ramklass Sam Alapati 4. Using the editor create a controlfile .
