oracle-学习笔记-4

9.查看Oracle的datafile地址，查看表空间地址

set lines 1000 pages 1000
column file_name format A70
column tablespace_name format A30
column status for a15
select status, file_name, tablespace_name, bytes/1024/1024 "Size(M)", AUTOEXTENSIBLE from dba_data_files
union all
select status, file_name, tablespace_name, bytes/1024/1024 "Size(M)", AUTOEXTENSIBLE from dba_temp_files
order by tablespace_name, file_name;

select tablespace_name,
       file_id,
       file_name,
       　　 round(bytes / (1024 * 1024), 0) total_space 　　
  from dba_data_files 　　
 order by tablespace_name;

 

10.改变表空间某个数据文件大小

ALTER DATABASE  DATAFILE '/oracle/oradata/yqmora92/ts_yqm.dbf' RESIZE  150M;

alter database datafile 'd:\oracle\product\10.2.0\oradata\test1\test6k.dbf' autoextend on next 50m maxsize 200m;

查看用户表空间的限额

　　select * from user_ts_quotas;

　　max_bytes字段就是了

　　-1是代表没有限制,其它值多少就是多少了.

不对用户做表空间限额控制:

　　GRANT UNLIMITED TABLESPACE TO ***(用户);

　　这种方式是全局性的.

　　或者

　　alter user ***(用户名) quota unlimited on ***(表空间);

　　这种方式是针对特定的表空间的.

　　可以分配自然也可以回收了:

　　revoke unlimited tablespace from ***(用户)

　　或者

　　alter user *** quota 0 on ***

 

11.1主键约束 实体完整性

create table nn(n1 varchar2(10) not null,n2 char(10));
insert into nn values ('abc','def');
alter table nn add constraint pk_nn primary key(n1);

SQL> show user
USER 为 "SCOTT"
SQL> create table nn(n1 varchar2(10) not null,n2 char(10));

表已创建。

SQL> insert into nn values ('abc','def');

已创建 1 行。

SQL> insert into nn values ('abc','def');

已创建 1 行。
SQL> rollback;
回退完成。

SQL> alter table nn add constraint pk_nn primary key(n1);

表已更改。

SQL> insert into nn values ('abc','def');

已创建 1 行。

SQL> insert into nn values ('abc','def');
insert into nn values ('abc','def')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.PK_NN)

 

11.2 外键约束  参照完整性

SQL> create table mm(m1 char(10),m2 varchar2(10),n1 varchar2(10));

表已创建。

SQL> alter table mm add constraint fk_mn foreign key(n1) references nn(n1);

表已更改。

SQL> insert into mm values ('aav','dd','ds');
insert into mm values ('aav','dd','ds')
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.FK_MN) - 未找到父项关键字

SQL> insert into mm values ('aav','dd','abc');

已创建 1 行。

SQL> commit;

提交完成。

11.3 check 约束

SQL> select * from e;

       EID ENAME      SEX         DID
---------- ---------- ---- ----------
        10 aaa        男           25
        15 aa         b            25
         1 赵1        男            1
         2 钱1        女            2
         3 孙1        女            3
         3 李1        男            4
         3 周1        女            5
         4 周2        女

已选择8行。

SQL> update e set sex = '男' where eid = 15;

已更新 1 行。

SQL> commit;

提交完成。

SQL> alter table e add constraint ck_e_sex check(sex = '男' or sex = '女');

表已更改。

SQL> update e set sex = 'aa' where eid = 15;
update e set sex = 'aa' where eid = 15
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.CK_E_SEX)

SQL> select * from e;

       EID ENAME      SEX         DID
---------- ---------- ---- ----------
        10 aaa        男           25
        15 aa         男           25
         1 赵1        男            1
         2 钱1        女            2
         3 孙1        女            3
         3 李1        男            4
         3 周1        女            5
         4 周2        女

已选择8行。

11.4 查看约束

SQL> select constraint_name,constraint_type from user_constraints where table_name='MM';

CONSTRAINT_NAME                C
------------------------------ -
FK_MN                          R

SQL> select constraint_name,constraint_type from user_constraints where table_name='E';

CONSTRAINT_NAME                C
------------------------------ -
CK_E_SEX                       C

SQL> select constraint_name,constraint_type from user_constraints where table_name='NN';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C005167                    C
PK_NN                          P

 

12. 表 索引

表 没建索引之前是按堆存放数据的。

在表的经常查询的列上建立索引

SQL> create index my_mm_idx on mm(m1);

索引已创建。

SQL> select * from mm where m1 ='aav';

M1         M2         N1
---------- ---------- ----------
aav        dd         abc

12.1 位图索引

为了解决 表里面数据很多，但是唯一值很少的情况，例如：有一万条记录，sex列 只有 两个不同的值 ：男和女

SQL> create bitmap index bit_e on e(sex);

索引已创建。

Oracle中NVARCHAR2与VARCHAR2的区别

VARCHAR2是Oracle提供的特定数据类型，Oracle可以保证VARCHAR2在任何版本中该数据类型都可以向上和向下兼容。
VARCHAR在Oracle中不建议使用。

具体到NVARCHAR2和VARCHAR2的区别，从使用角度来看区别在于：NVARCHAR2在计算长度时和字符集相关的，例如数据库是中文字符集时以长度10为例，则

1、NVARCHAR2(10)是可以存进去10个汉字的，如果用来存英文也只能存10个字符。

2、而VARCHAR2(10)的话，则只能存进5个汉字，英文则可以存10个。

 

13. SQL Loader

先创建两个文件：

loader.txt 内容：

abc,xyz
avf,ddd
des,ggf

cont.ctl 内容：

load data
infile 'E:\WorkPlace\OracleWork\loader.txt'
append
into table scott.mm(
m1 position(1:3) char,
m2 position(5:7) char)

 

打开一个cmd窗口，执行：

E:\>sqlldr scott/tiger control=E:\WorkPlace\OracleWork\cont.ctl data=E:\WorkPlace\OracleWork\loader.txt

SQL*Loader: Release 10.2.0.1.0 - Production on 星期二 12月 14 17:32:39 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 2
达到提交点 - 逻辑记录计数 3

进入sql/plus

查看：

SQL> select * from mm;

M1         M2         N1
---------- ---------- ----------
aav        dd         abc
ddd        ss         abc
abc        xyz
avf        ddd
des        ggf

已经添加了3条记录。

 

以上是处理规则数据，下面处理不规则数据：

loader.txt 内容：

abcddd,xyz
avff,dfddd
d,ggfd

cont.ctl 内容:

load data
infile 'E:\WorkPlace\OracleWork\loader1.txt'
append
into table scott.mm(
m1 terminated by ',',
m2 terminated by ',')

打开一个cmd窗口，执行：

E:\>sqlldr scott/tiger control=E:\WorkPlace\OracleWork\cont1.ctl data=E:\WorkPlace\OracleWork\loader1.txt

SQL*Loader: Release 10.2.0.1.0 - Production on 星期二 12月 14 18:11:40 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 2
达到提交点 - 逻辑记录计数 3

 

进入sql/plus

查看：

SQL> select * from mm;

M1         M2         N1
---------- ---------- ----------
aav        dd         abc
ddd        ss         abc
abc        xyz
avf        ddd
des        ggf
abcddd     xyz
avff       dfddd
d          ggfd

已选择8行。

14. OEM配置使用

15. 数据库的备份与恢复

逻辑备份，逻辑恢复
物理备份，物理恢复

导出

F:\>exp 

Export: Release 10.2.0.1.0 - Production on 星期二 12月 14 20:47:22 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
输入数组提取缓冲区大小: 4096 > 5000

 导出文件: EXPDAT.DMP > c:\mybak.dmp

(2)U(用户), 或 (3)T(表): (2)U > t

导出表数据 (yes/no): yes > yes

压缩区 (yes/no): yes > yes

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
要导出的表 (T) 或分区 (T: P): (按 RETURN 退出) > e

. . 正在导出表                               E导出了           8 行

要导出的表 (T) 或分区 (T: P): (按 RETURN 退出) >

导出成功终止, 但出现警告。

F:\>exp 

Export: Release 10.2.0.1.0 - Production on 星期二 12月 14 20:48:48 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
输入数组提取缓冲区大小: 4096 > 5000

 导出文件: EXPDAT.DMP > c:\scott.dmp

(2)U(用户), 或 (3)T(表): (2)U > u

导出权限 (yes/no): yes > yes

导出表数据 (yes/no): yes > yes

压缩区 (yes/no): yes > yes

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 SCOTT 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 SCOTT 的对象类型定义
即将导出 SCOTT 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 SCOTT 的表通过常规路径...
. . 正在导出表                              AA导出了          10 行
. . 正在导出表                             ABC导出了           1 行
. . 正在导出表                            AUTO导出了           7 行
. . 正在导出表                           BONUS导出了           0 行
. . 正在导出表                           BOOKS导出了           8 行
. . 正在导出表                               D导出了          11 行
. . 正在导出表                            DEPT导出了           4 行
. . 正在导出表                               E导出了           8 行
. . 正在导出表                              EE导出了           6 行
. . 正在导出表                             EMP导出了          14 行
. . 正在导出表                              MM导出了           8 行
. . 正在导出表                           MYLOG导出了           3 行
. . 正在导出表                              NN导出了           2 行
. . 正在导出表                        SALGRADE导出了           5 行
. . 正在导出表                               T导出了           2 行
. . 正在导出表                              TT导出了          13 行
. 正在导出同义词
. 正在导出视图
. 正在导出存储过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计信息
成功终止导出, 没有出现警告。

导入：

SQL> select * from e;

       EID ENAME      SE        DID
---------- ---------- -- ----------
        10 aaa        男         25
        15 aa         女         25
         1 赵1        女          1
         2 钱1        女          2
         3 孙1        男          3
         3 李1        男          4
         3 周1        男          5
         4 周2        男

已选择8行。

SQL> delete from e;

已删除8行。

SQL> commit;

提交完成。

SQL> select * from e;

未选定行

打开一个cmd窗口，

E:\>imp scott/tiger

Import: Release 10.2.0.1.0 - Production on 星期三 12月 15 18:53:44 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

导入文件: EXPDAT.DMP> mybak.dmp

输入插入缓冲区大小 (最小为 8192) 30720>

经由常规路径由 EXPORT:V10.02.01 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
只列出导入文件的内容 (yes/no): no >

由于对象已存在, 忽略创建错误 (yes/no): no > yes

导入权限 (yes/no): yes >

导入表数据 (yes/no): yes >

导入整个导出文件 (yes/no): no > yes

. 正在将 SCOTT 的对象导入到 SCOTT
. 正在将 SCOTT 的对象导入到 SCOTT
. . 正在导入表                             "E"导入了           8 行
IMP-00017: 由于 ORACLE 错误 2264, 以下语句失败:
 "ALTER TABLE "E" ADD CONSTRAINT "CK_E_SEX" CHECK (sex = '男' or sex = '女') "
 "ENABLE NOVALIDATE"
IMP-00003: 遇到 ORACLE 错误 2264
ORA-02264: 名称已被一现有约束条件占用
即将启用约束条件...
成功终止导入, 但出现警告。

E:\>

 

回到sql窗口：

SQL> select * from e;

       EID ENAME      SE        DID
---------- ---------- -- ----------
        10 aaa        男         25
        15 aa         女         25
         1 赵1        女          1
         2 钱1        女          2
         3 孙1        男          3
         3 李1        男          4
         3 周1        男          5
         4 周2        男

已选择8行。

 

物理备份
分为：冷备份和热备份

冷备份，就是把数据库停掉，手动拷贝数据文件

热备份，要把日志模式改为 存档模式

SQL> conn sys/ora as sysdba
已连接。
SQL> archive log list
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     11
当前日志序列           13

第一步，改为存档模式

alter system set log_archive_start=true scope=pfile;

第二步，停掉数据库

shutdown immediate

第三步，启动数据

startup mount

表示，打开控制文件，不打开数据文件

第四步，将数据库切换到归档方式

alter database archivelog;

第五步，打开数据文件

alter database open;

第六步，查看模式

SQL> archive log list
数据库日志模式             存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     11
当前日志序列           13

第七步，

alter tablespace TRAINING begin backup;

第八步，手动拷贝

把数据库实例目录下的数据文件 TRAINING 拷贝备份

第九步，

alter tablespace TRAINING end backup;

alter system archive log current;

alter system switch logfile;

alter system switch logfile;

注意要切换两次！

完成。

 

如果数据库出现问题

select * from v$recover_file;

假如 第6号 TRAINING 丢失

alter database datafile 6 offline drop;

alter database open;

把以前手工备份的TRAINING拷贝到实例数据文件目录下

recover datafile 6;
auto

alter database datafile 6 online;

恢复完成。

备份控制文件

SQL> alter database backup controlfile to trace;

数据库已更改。

然后，到下面目录下，可以看到产生的脚本，用于以后备份；
E:\oracle\product\10.2.0\admin\ora\udump

ora_ora_1896.trc

 

恢复控制文件

把 ora_ora_1896.trc 另存为 create_ctl.sql

SQL> @create_ctl.sql

完成。

 

假定日志文件丢失

第一步：

recover database until cancle

第二步：

alter database onpen resetlogs