Oracle 考试重点代码整理

(一)、设置时间：set  time on

（二）、归档模式切换

（1）从非归档到归档模式。

C:\>sqlplus sys/oracle as sysdba      -- 以管理员身份登录

SQL>select log_mode from v$database;  -- 查询数据库的归档模式

SQL>shutdown immediate;                    -- 以正常方式关闭实例

SQL>startup mount;                         -- 启动实例至mount状态

SQL>alter database archivelog;             -- 归档模式切换

SQL>alter database open;

（2）从归档到非归档模式。

SQL>c:\sqlplus sys/oracle as sysdba

SQL>select log_mode from v$database;

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database noarchivelog;

SQL>alter database open;

（3）使用log_archive_dest_n配置多个归档位置。

SQL>alter system set log_archive_dest_1='location=d:\stu\archive1';

SQL>alter system set log_archive_dest_2='location=e:\stu\archive2';

SQL>alter system set log_archive_dest_3='location=f:\stu\archive3';

SQL>alter system set log_archive_dest_4='service=ElseOracleDB';

   3）在SQL Plus中创建数据表

（1）启动sqlplus，以账号sys登录到实例finance，这里假设账号口令为oracle。

C:\>sqlplus sys/oracle@finance as sysdba

（2）解锁用户hr。

SQL>alter user hr identified by hr account unlock;

（3）使用hr账户登录到Oracle实例。

SQL>conn hr/hr@finance

（4）创建stu表。

SQL>create table Customer

2    (CustomerNumber NUMBER(10,0) not null constraint pk primary key,

3    CustomerName NVARCHAR not null,

4    CustomerSex NCHAR(1) not null,

5    CustomerIDCard CHAR(18) not null);

4）向表中插入数据

格式1：

SQL>insert into

2    Customer (CustomerNumber, CustomerName, CustomerSex, CustomerIDCard)

3    values('1','杨虹','女', '210123198912093216');

格式2：

SQL>insert into Customer values(2,'张兵','男', '212123198912093216');

格式3：

SQL>insert into

2    Customer(CustomerNumber,CustomerName,CustomerSex, CustomerIDCard)

3    values(&cnum,&cname,&cs,&cidc);

5）更新表中的数据

更新表中的数据，要求每个表中至少要执行两次更新操作，读者自行设计更新语句。下面仅给出在Customer表中更新数据的一个示例。

SQL>update Customer set CustomerSex='女',CustomerName= '赵敏'

2    where CustomerNumber=2;

6）删除表中的数据

给出适当的条件，从每个表中删除两条记录，并且要求每个表只能执行一次delete语句。下面以一个示例演示delete语句的用法。

SQL>delete from Customer where CustomerName like '张%';

4、表空间管理

1）创建表空间

（2）创建永久表空间finance_2，包含2个数据文件。

SQL>create tablespace finance_2

2    datafile 'c:\oracle_db\finance201.dat' size 5m reuse

3    autoextend on next 50k maxsize 50m,

4    'c:\finance_db\finance202.dbf' size 500k reuse

5    autoextend on next 50k maxsize 40m;

（4）创建临时表空间temp_finance。

SQL>create bigfile temporary tablespace temp_finance

2    tempfile 'c:\finance_db\financetemp01.dbf' size 40m

3    extent management local uniform size 1k;

（5）创建撤销表空间undo_finance。

SQL>create undo tablespace undo_finance

2    datafile 'c:\finance_undo01.dbf' size 10m

3    autoextend on next 500k extent management local;

2）删除表空间

（1）删除表空间temp_finance。

SQL>drop tablespace temp_finance

2    including contents cascade constraints;

5、数据文件管理

（1）在表空间中添加数据文件

SQL>alter tablespace finance_1

2    Add datafile 'c:\finance_db\finance102.dbf' size 150k,

3    'c:\finance_db\finance103.dbf' size 100k;

5）删除数据文件

SQL>alter tablespace finance_1 drop datafile 'c:\oracle_db\finance103.dbf';

6、控制文件管理

1）建立多路控制文件

（1）第1步：关闭数据库实例。

SQL>shutdown normal;

（2）第2步：将当前数据库控制文件复制到若干个目标位置，在此增加了两个控制文件的拷贝。

C:\>copy c:\oracle\product\10.2.0\oradata\orcl\control01.ctl d:\oracle\control04.ctl

C:\>copy e:\oracle\product\10.2.0\oradata\orcl\control01.ctl e:\oracle\control05.ctl

（3）第3步：修改参数control_files的值（具体操作方法请参考本次实验的配置数据库初始化参数的有关实验内容），具体如下：

control_files=(

'c:\oracle\product\10.2.0\oradata\orcl\control01.ctl',

'c:\oracle\product\10.2.0\oradata\orcl\control02.ctl',

'c:\oracle\product\10.2.0\oradata\orcl\control03.ctl',

'd:\oracle\control04.ctl',

'e:\oracle\control05.ctl') scope=spfile;

（3）第4步：启动数据库实例。

SQL>startup;

7、联机重做日志文件管理

1）创建联机重做日志组

（1）创建日志文件组4，包括2个成员。

SQL>alter database add logfile group 4

2    ('c:\oracle_log\redo0401.rdo', 'd:\oracle_log\redo0402.rdo') size 50m;

SQL>select group#, members, status, first_time from v$log;

2）创建日志组成员

SQL>alter database add logfile member

2    'e:\oracle_log\redo0103.rdo' to group 1,

3    'e:\oracle_log\redo0203.rdo' to group 2,

4    'e:\oracle_log\redo0303.rdo' to group 3,

5    'e:\oracle_log\redo0403.rdo' to group 4,

6    'e:\oracle_log\redo0403.rdo' to group 5;

3）删除重做日志组

SQL>alter database drop logfile group 3;

SQL>select group#, members, status, first_time from v$log;

1）解锁hr用户，并使用hr帐户登录到数据库

c:\>sqlplus /nolog

SQL>conn sys/oracle as sysdba

SQL>alter user hr identified by hr account unlock;

SQL>conn hr/hr

2）普通表

（1）创建普通表emp1。

SQL>create table emp1(

2    empno         number(5) primary key,

3    ename         varchar2(15) not null,

4    ssn      number(9),

5    job           varchar2(10),

6    mgr           number(5),

7    hiredate date default(sysdate),

8    sal           number(7,2),

9    comm     number(7,2),

10   deptno        number(3) not null

constraint dep_fkey references hr.departments(depaetment_id))

11   tablespace example;

SQL>select table_name from user_tables;        -- 可以看到新建的emp1表。

（10）设置表为只读模式。

SQL>alter table emp1 read only;

（11）删除表。

SQL>drop table emp2;

3）临时表

（1）事务临时表。

SQL>create global temporary table temp1(cola int) on commit delete rows;

SQL>insert into temp1 values(1);

SQL>select * from temp1;

SQL>commit;

SQL>select * from temp1;

说明：on commit delete rows选项表示在临时表中只保留事务数据；读者可比较事务提交前后，两次查询的结果是否一致。

5）分区表

说明：Oracle提供了6种不同的分区方法，即范围分区、间隔分区、散列分区、列表分区、引用分区和系统分区。

（1）创建范围分区表。

SQL>create table sales_data(

2    ticket             number,

3    sale_year     int not null,

4    sale_month         int not null,

5    sale_day      int not null)

6    partition by range(sale_year,sale_month,sale_day)(

7    partition sales_q1 values less than(2008,04,01) tablespace t1,

8    partition sales_q2 values less than(2008,07,01) tablespace t2,

9    partition sales_q3 values less than(2008,10,01) tablespace t3,

10   partition sales_q4 values less than(2009,01,01) tablespace t4,

（4）创建列表分区。

SQL>create table sales_data(

2    ticket_no          number,

3    sale_year          int not null,

4    sale_month             int not null,

5    sale_day           int not null,

6    destination_city   char(3),

7    start_city             char(3))

8    partition by list(start_city)(

9    partition northeast_sales values('NYC', 'BOS', 'PEN') tablespace ts1,

10   partition southwest_sales values('DFW', 'ORL', 'HOU') tablespace ts2,

11   partition pacificwest_sales values('SAN', 'LOS', 'WAS') tablespace ts3,

12   partition southeast_sales values('MIA', 'CHA', 'ATL') tablespace ts4);

说明：分区描述部分为start_city列指定了一列值。表的创建语句创建了4个列表分区。只有落在该列表中的城市才将包括在该分区中。记录为9999，2008，06，01，DFW，HOU信息的机票将存储在southwest_sales分区中。

2、索引管理

（2）在表employees中创建一个多列的唯一索引（复合索引）。

SQL>create unique index emp_idx2 on

2    employees(first_name,department_id,employee_id)

3    tablespace example pctfree 5

4    storage(initial 10k next 20k pctincrease 50);

说明：这里假设l_name列包含大写的员工姓氏，由于此索引在创建时首先使用lower函数将l_name列值转换成小写字母，所以此索引是基于函数的索引。

（7）创建全局分区索引。

SQL>create index ticketsales_idx on ticket_sales(month)

2    global partition by range(month)(

3    partition ticketsales1_idx values less than(3),

4    partition ticketsales1_idx values less than(6),

5    partition ticketsales2_idx values less than(9),

6        partition ticketsales3_idx values less than(MAXVALUE));

说明：分区表的全局索引可以分区也可以不分区，全局不分区的索引类似于未分区表的普通索引。在表ticket_sales创建了全局分区索引后，如果该分区表有DDL操作，则全局索引需要重建。

2）修改索引

SQL>alter index emp_ind rebuild tablespace users

2    initrans 3 maxtrans 10;

SQL>select index_name, table_name, tablespace_name

2    from user_indexes

3    where index_name='EMP_IND';

3）删除索引

SQL>drop index emp_idx1;

SQL>select index_name, table_name, tablespace_name

2    from user_indexes

3    where index_name='EMP_IDX1';

1）创建用户

（1）创建一个用户user1，口令为oracle，同时，指定默认表空间、临时表空间、表空间配额以及概要文件等信息。

SQL>create user user1 identified by oracle

2    default tablespace example

3    temporary tablespace temp_tbs

4    quota 5m on example quota 100m on users

5    profile pro_common_users;

1）授予系统权限

（1）为了说明权限管理的基本方法和操作步骤，先创建两个用户blake和jones。

SQL>create user blake identified by blake

2    default tablespace example

3    temporary tablespace temp

4    quota 2M on example quota 2m on users;

SQL>create user jones identified by jones

2    default tablespace example

3    temporary tablespace temp

4    quota 2M on example quota 2m on users;

（2）授予用户blake系统权限。

SQL>conn sys/oracle as sysdba

SQL>grant create session, create table to blake with admin option;

SQL>grant create view to blake;

3）收回系统权限

（1）回收用户blake的会话权限。

SQL>conn sys/oracle as sysdba

SQL>revoke create session from blake;

4、对象权限管理

1）授予对象权限

说明：在Oracle中，对象权限较少，主要有select、update、delete、insert、execute、index和alter等7种类型，这些权限总是与数据库中的对象有关。

（1）授予alter权限。

SQL>conn scott/tiger

SQL>grant alter on emp to blake;

SQL>conn sys/oracle as sysdba

SQL>grant create session, create table to blake with admin option;

SQL>conn blake/blake

SQL>alter table scott.emp add remark varchar2(500);

（2）授予execute权限。

SQL>conn sys/oracle as sysdba

SQL>grant execute on dbms_transaction to public;

SQL>conn blake/blake

SQL>execute dbms_transaction.read_only;

说明：选项public表示将权限授予public组，即所有用户。

（3）授予index权限。

SQL>conn sys/oracle as sysdba

SQL>grant index on scott.emp to blake;

SQL>conn blake/blake

SQL>create index ind_ename on scott.emp(ename);

（4）授予references权限。

SQL>conn scott/tiger

SQL>grant references on dept to blake;

说明：用户可以直接在自身模式的表之间定义主从关系，但在不同模式表之间定义主从关系，必须将主表的references对象权限授予从表所对应的数据库用户。例如，blake用户要基于表scott.dept建立从表，则必须要被授予references对象权限。

（6）使用with grant option选项。

SQL>conn scott/tiger

SQL>grant select on emp to blake with grant option;

SQL>conn blake/blake

SQL>grant select on scott.emp to jones;

说明：请根据上述例句，认真体会选项with grant option的含义。

3）收回对象权限

（1）收回用户blake对表emp的select权限。

SQL>conn scott/tiger

SQL>revoke select on emp from blake;

SQL>conn blake/blake

SQL>select * from scott.emp;

• 冷备份实验

（1）、创建test2表并插入数据

SQL> create table test2

  2  ( bno varchar(50) not null constraint bn_pri primary key,

  3  bprice number(11))

  4  table users;

     SQL> insert into test2

     2  (bno,price) values('java',100);

（2）、查询控制文件，重做日志文件，数据文件

SQL>col file_name format a60

SQL>col tablespace_name format a15

SQL>select file_name, tablespace_name

2        from dba_data_files

3        where tablespace_name='USERS'

   

    SQL> select name

  2  from v$controlfile;

  SQL> select member

  2  from V$LogFile;

                                                                                                                                                    

（3）、设置数据库为归档模式

SQL> alter database archivelog;

（4）、手动复制文件

SQL> insert into test2

  2  (bno,bprice) values('oracle',200);

SQL> commit;

SQL> shutdown immediate;

(5)、恢复文件文件并启动数据库

SQL> recover datafile 'f:\oracle\oradata\orcl\users01.dbf';

SQL> alter database open;

    select * from test2;

二、热备份实验

(1)、修改数据库为归档模式

SQL> alter database archivelog;

(2)、向表中插入数据

SQL> insert into test2

  2  (bno,bprice) values('C语言',23);

SQL> commit;

利用第一题查询的数据文件，重做日志文件，控制文件的位置

（3）、修改数据库为备份状态

SQL> alter tablespace users begin backup;

SQL> host copy F:\ORACLE\ORADATA\ORCL\USERS01.DBF D:\file2\users01.dbf

            SQL> alter tablespace users end backup;

同第一题，手动备份数据文件，控制文件，重做日志文件

SQL> insert into test2

  2  (bno,bprice) values('kk',77);

SQL> commit;

 （4）、手动删除控制文件

  （5）、手动恢复文件

SQL> startup mount;

SQL> recover database;

SQL> alter database open

SQL> select * from  test2;

• RMAN备份

(1)、向表中添加数据

SQL> insert into test2

  2  (bno,bprice) values('qqq',123);

(2)、查看数据文件的id

SQL> select file_name,file_id from dba_data_files;

SQL> create user rmanadm identified by oracle;

SQL> grant sysdba to rmanadm;

SQL> exit;

（3）、创建通道

C:\WINDOWS\system32>rman

RMAN> connect target rmanadm/oracle

RMAN> configure default device type to disk;

RMAN> configure channel device type disk format 'd:backup\%c';

（4）、备份数据文件

RMAN>backup datafile 4 filesperset=2;

之后手动删除user01.dbf的数据文件

打开数据库发生错误

（5）、恢复文件

RMAN>startup normal;

RMAN>sql "alter tablespace users offline immediate;"

RMAN>restore tablespace users;

RMAN>recover tablespace users;

RMAN>sql "alter tablespace example online;"

之后重新打开数据库

SQL> select * from  test2;