Shulou(Shulou.com)06/01 Report--

Create an index:

Automatic

-create a PRIMARY KEY

-create a UNIQUE KEY

Manual

-CREATE INDEX statement

-CREATE TABLE statement

Create index in create table statement

Create table new_emp (employee_id number (6) primary key using index)

(create index emp_id_idx on

New_emp (employee_id))

First_name varchar2 (20)

Last_name varchar2 (25))

Select index_name, table_name from user_indexes where table_name = 'new_emp'

Function-based index

A function-based index is an expression-based index.

Index expressions consist of columns, constants, SQL functions, and user-defined functions

Create index upper_dept_name_idx on dept2 (upper (department_name))

Select * from dept2 where upper (department_name) = 'SALES'

Delete index

Use the DROP INDEX command to remove the index from the data dictionary:

Drop index index

Remove the UPPER_DEPT_NAME_IDX index from the data dictionary:

Drop index upper_dept_name_idx

To delete an index, you must be the owner of the index or have DROP ANY INDEX permission

Drop table dept80 purge

FLASHBACK TABLE statement

A single statement can be restored to a specified point in time.

Restore the data in the table and related indexes and constraints.

The table can be restored based on a point in time or a system change number (SCN).

Repair tools for unexpected modifications of the table:

-the table is restored to an earlier point in time

-advantages: ease of use, availability, fast execution

-execution in place (Is performed in place)

Syntax:

Flashback table [schema.] table [

[schema.] table]...

To {timestamp | scn} expr

[{enable | disable} triggers]

Example:

Drop table emp2

Select original_name, operation, droptime from recyclebin

Flashback table emp2 to before drop

Temporary watch

Create a temporary table

Create global temporary table cart on commit delete rows

Create global temporary table today_sales

On commit preserve rows as

Select * from orders

Where order_date = sysdate

External table

Create a catalog for an external table

Create a DIRECTORY object that corresponds to the directory on the file system where the external data source resides.

Create or replace directory emp_diras'/... / emp_dir'

Grant read on directory emp_dir to ora_21

Create an external table

Create table

(,)

Organization external

(type

Default directory

Access parameters

(.))

Location ('')

Reject limit [0 | | unlimited]

Use the ORACLE_LOADER driver to create external tables

Create table oldemp (

Fname char (25), lname char (25))

Organization external

(type oracle_loader

Default directory emp_dir

Access parameters

(records delimited by newline

Nobadfile

Nologfile

Fields terminated by','

(fname position (1:20) char

Lname position (22:41) char))

Location ('emp.dat'))

Parallel 5

Reject limit 200

Query external tables

Select * from oldemp

Use the ORACLE_DATAPUMP driver to create external tables:

Create table emp_ext

(employee_id, first_name, last_name)

Organization external

(

Type oracle_datapump

Default directory emp_dir

Location

('emp1.exp','emp2.exp')

)

Parallel

As

Select employee_id, first_name, last_name

From employees

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.