接 oracle-学习笔记-3
-------------------------------------
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 scott/tiger@ora
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 scott/tiger@ora
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
548
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
