一:表空间
(一):创建表空间
三种方式:
1,用Uniform区分配方式:CREATE TABLESPACE MYTS01 DATAFILE ‘C:\ORACLE\PRODUT\10.1.0\ORADATA\OAMIS\MYTS01_1.DBF’ SIZE 2M UNIFORM SIZE 128K.( 使用uniform来分配:扩展时每次表空间分配的区大小 #指定区尺寸为128k,如不指定,区尺寸默认为64k 就是说,如果我们DBA能过预测到表空间中存放的大部分对象都要求使用想同大小的区,那么我们就用Uniform方式,这个时候Oracle会为表空间中的所有对象都分配指定的相同大小的区,我们用uniform分配方式有点就是在表空间中不会产生磁盘碎片,可以节约磁盘空间。)
2,用AUTOALLOCATE区分配方式:CREATE TABLESPACE MYTS02 DATAFILE ‘C:\ORACLE\PRODUCT\10.1.0\ORADATA\OAMIS\MYTS02_1.DBF’ SIZE 2M AUTOALLOCATE(这种AUTOALLOCATE的分配方式是和上面的向对应的,如果我们DBA能过预测到表即可中存放的对象所需要的区德大小会有很大的不同,那么我们就用AUTOALLOCATE的方式。这时:Oracle将自动为表空间中的不同对象分配相应大小的区,最小是64K,不过我们用这个方式分配,会在表空间中产生磁盘碎片。造成一些浪费。)
3,用AUTO段管理方式:create tablespace myts03 datafile 'F:\oracle\product\10.1.0\oradata\orcl\ myts03_1.dbf' size 2M uniform size 128k segment space management auto(这个segment space management是用来指定表空间的段管理方式,就是管理段中的已用数据块和空闲数据块的方式。)
(二):三种方式扩展表空间
(1)添加数据文件(加一个3兆的文件)
alter tablespace myts01 add datafile 'C:\oracle\product\10.1.0\oradata\orcl\myts01_2.dbf' size 2048k;
(2)改变数据文件大小(把文件改成4兆)
alter database datafile 'C:\oracle\product\10.1.0\oradata\orcl\ myts01_2.dbf' resize 4096k;
(3)允许数据文件自动扩展
alter database datafile 'C:\oracle\product\10.1.0\oradata\orcl\ myts01_2.dbf' autoextend on next 1m maxsize 20m;
(三):修改表空间名称
在Oracle10g之前是不能修改表空间名称的,这个是10g特有的。
alter tablespace myts01_2rename to myts01_3
(四):删除表空间:
drop tablespace test including contents;
删除表空间,包括这里面的内容
这条语句只会从数据字典中删除表空间信息,他里面的数据文件还在磁盘上。所以我们用下面的语句做删除,把表空间和里面的数据文件都删除了。
drop tablespace test1 including contents and datafiles;
二:创建用户
CREATE USER JACK IDENTIFIED BY WELCOME DEFAULT TABLESPACE TEST_TABLESPACE(创建一个用户名为JACK口令为WELCOME的账户. IDENTIFIED BY告诉Oracle,这个账户是一个数据库身份验证的账户)
CREATE USER JACK IDENTIFIED EETERNALLY(IDENTIFIED EETERNALLY告诉Oracle这个账户是一个外部身份验证的账户)
CREATE USER SCOTT INENTIFIED GLOBALLY AS ‘CN=SCOTT,OU=DIVISION1,O=SYBEX,C=US’;( INENTIFIED GLOBALLY AS 告诉Oracle,这个账户使用全局身份验证)
三:给用户(HELLOWORLD)授权
GRANT CONNECT TO HELLOWORLD;
GRANT CREATE ANY TABLE TO HELLOWORLD;
GRANT ALTER ANY TABLE TO HELLOWORLD;
GRANT DROP ANY TABLE TO HELLOWORLD;
GRANT SELECT ANY TABLE TO HELLOWORLD;
GRANT UPDATE ANY TABLE TO HELLOWORLD;
GRANT DELETE ANY TABLE TO HELLOWORLD;
GRANT CREATE ANY TABLE TO HELLOWORLD;
GRANT CREATE ANY PROCEDUCE TO HELLOWORLD;
GRANT DROP ANY PROCEDUCE TO HELLOWORLD;
GRANT ALTER ANY PROCEDUCE TO HELLOWORLD;
四:创建序列
CREATE SEQUENCE SEQ_USER
START WITH 1
INCREMENT BY 1
MAXVALUE 500
MINVALUE 1
NOCYCLE
CACHE 10
五:创建表
(一)创建标准表(包括约束)
CREATE TABLE department
(
DepId number(9) not null,
DeptNo char(2) default 01 not null,
Name varchar2(20),
Address varchar2(30),
CONSTRAINT Pk_DeptId PRIMARY KEY(DeptId) VALIDATE,
CONSTRAINT UNIQ_Name UNIQUE(Name) VALIDATE,
CONSTRAINT CHK_Address CHECK(Address IN(‘1号楼’,’2号楼’,’3号楼’,’4号楼’)) VALIDATE
);
(二)创建事务临时表
create global temporary table temp1(
id number(9) primary key,
name varchar2(20)
);
(三)创建会话临时表
create global temporary table temp2(
id number(9) primary key,
name varchar2(20)
) on commit preserve rows;
六:触发器
(一) 语句级触发器
create or replace trigger my_triger
before
insert or update or delete
on emp
begin
if(to_char(sysdate,'day') in ('星期六','星期日'))
or (to_char(sysdate,'HH24') not between 8 and 18) then
raise_application_error(-20001,'不是上班时间,不能修改emp表');
end if;
end;
调用:begin
delete from emp;
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
(二) 行级触发器
create or replace trigger tr_emp_sal_comm
before update of sal,comm or delete on emp
for each row when(old.job='SALESMAN')
begin
case
when updating('sal') then
if :new.sal<:old.sal then
raise_application_error(-20001, '销售人员工资只能涨不能降');
end if;
when updating('comm') then
if :new.comm<:old.comm then
raise_application_error(-20002,'销售人员补助只能涨不能降');
end if;
when deleting then
raise_application_error(-20003, '不能删除emp表的销售人员记录');
end case;
end;
调用:
begin
update emp set comm=800 where job='SALESMAN';
exception when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
七:存储过程
create or replace procedure pro_name(v_no in emp.empno%type,v_name out emp.ename%type,v_sal out emp.sal%type)
is
e_sal_error exception;
begin
select ename,sal into v_name,v_sal from emp
where empno=v_no;
if v_sal>2500 then
dbms_output.put_line('soijdiofj'||v_sal);
raise e_sal_error;
end if;
exception
when no_data_found then
dbms_output.put_line('djjf'||v_no);
when e_sal_error then
dbms_output.put_line('dfdfj');
end pro_name;
调用:
declare
v_a1 emp.ename%type;
v_a2 emp.sal%type;
begin
query_emp(v_no=>5678,v_name=>v_a1,v_sal=>v_a2);
end;
八:函数
create or replace function get_salary_by_deptno
(v_dept_no in emp.deptno%type,v_emp_cnt out number)
return number
is
v_sum number(10,2);
begin
select sum(sal),count(*) into v_sum,v_emp_cnt from emp
where deptno = v_dept_no;
return v_sum;
end get_salary_by_deptno;
调用:
declare
v_a1 emp.deptno%type;
v_a2 number;
v_sum number(10,2);
begin
v_sum := get_salary_by_deptno(v_emp_cnt => v_a1,v_dept_no => 10);
if v_a1 = 0 then
dbms_output.put_line('该部门无人');
else
dbms_output.put_line('该部门工资总和:'||v_sum||'人数'||v_a2);
end if;
end;
九:游标
declare tempsal scott.emp.sal%type;
cursor tempcursor is select * from scott.emp
where sal>tempsal;
cursorrecord tempcursor%rowtype;
begin
tempsal:=800;
open tempcursor;
if tempcursor%isopen then
fetch tempcursor into cursorrecord;
if tempcursor%found then
dbms_output.put_line(to_char(cursorrecord.deptno));
else
dbms_output.put_line('没有取到值');
end if;
else
dbms_output.put_line('游标没有打开!');
end if;
close tempcursor;
end;
十:索引
按照索引数据的存储方式可以分为:
• B*树索引(create index idx_emp_ename on emp(ename) tablespace users)
位图索引(Create bitmap index idx_gender On tab_name(gender))
按照索引个数分为:
单列索引
复合索引
索引列值唯一性可分为:
唯一索引(create unique index idx_emp_ename on emp(ename);
)
非唯一索引(默认)
函数索引:create index funidx_emp_empno on emp substring(empno,1,2);
十一:视图
create view v_emp
as
select emp.*,dept.dname from emp,dept where emp.deptno=dept.deptno;
十二:分区
范围分区
HASH分区(散列分区)
列表分区
复合分区(范围+HASH)(范围+列表)
创建范围分区
Create table material_test
(transaction_id number primary key,
Item_id number(8) not null,
Item_description varchar2(300),
Transaction_date date not null)
Partition by range(transaction_id)
(partition part_01 values less than(30000000) tablespace test1,
partition part_02 values less than(60000000) tablespace test2,
partition part_03 values less than(maxvalue) tablespace test3)
创建HASH分区
Create table material_test2
(transaction_id number primary key,
Item_id number(8) not null,
Item_description varchar2(300),
Transaction_date date not null)
Partition by hash(transaction_id)
(partition part_01 tablespace test1,
partition part_02 tablespace test2,
partition part_03 tablespace test3)
创建列表分区
Create table material_test3
(transaction_id number primary key,
Item_id number(8) not null,
Item_description varchar2(300),
Transaction_date date not null,
city varchar2(100))
Partition by list(city)
(partition part_01 values('北京') tablespace test1,
partition part_02 values('上海') tablespace test2,
partition part_03 values(default) tablespace test3)
十三:备份和恢复
(1) 数据泵方式
1,用sys用户登陆pl/sql,执行如下语句:
create directory my_dump as 'e:\temp';
grant read,write on directory my_dump to scott;
2,进入DOS模式(备份表)
输入如下指令:Expdp scott/tiger directory=my_dump dumpfile=first.dmp logfile=first.log tables=emp,dept
--导出emp,dept表
***注意 expdp是一个expdp.exe文件,不是oracle指令,所以不要在后面打分号。
3,进入DOS模式(备份方案)
输入如下指令:Expdp scott/tiger directory=my_dump dumpfile=scott.dmp logfile=scott.log schemas=scott
--导出scott下所有对象
4,进入DOS模式(备份数据库)
输入如下指令:Expdp system/server directory=my_dump dumpfile=fulll.dmp logfile=full.log Full=y
--备份整个数据库,包括所有方案下的对象
5,进入DOS模式(恢复表)
输入如下指令:impdp scott/tiger directory=my_dump dumpfile=first.dmp logfile=first.log tables=emp,dept
--导入emp,dept表
***注意 impdp是一个impdp.exe文件,不是oracle指令。
6,进入DOS模式(恢复方案)
输入如下指令:impdp scott/tiger directory=my_dump dumpfile=scott.dmp logfile=scott.log schemas=scott
--导入scott下所有对象
7,进入DOS模式(恢复数据库)
输入如下指令:impdp system/server directory=my_dump dumpfile=fulll.dmp logfile=full.log Full=y
--恢复整个数据库,包括所有方案下的对象
(2) 冷备份
第一步,记录需要备份的文件
1.数据文件
查询数据字典dba_data_files,查看有哪些数据文件
select * from dba_data_files;
2.控制文件
select * from v$controlfile;
3.重做日志文件
select * from v$logfile;
4.参数文件
(以oracle10g为例)
D:\oracle\product\10.1.0\admin\orcl\pfile\init.ora.*(可改名为init.ora)
D:\oracle\product\10.1.0\db_1\database\SPFILESID.ORA
D:\oracle\product\10.1.0\db_1\database\PWDsid.ora
第二步,关闭数据库
1.登入sqlplus,在cmd命令行输入
sqlplus system/password as sysdba
2.关闭数据库
SQL> shutdown immediate
第三步,备份文件
拷贝第一步列出的所有文件(数据文件,控制文件,重做日志文件,参数文件)
第四步,再启动数据库
SQL> startup open
恢复数据库
1.关闭数据库
SQL>shutdown immediate
2.将备份文件拷贝会原来位置
3.启动数据库
SQL>startup open
(3) 热备份
1、rman target orcl
2、输入密码
3、backup database;
备份后的文件位置:C:\oracle\product\10.1.0\flash_recovery_area
恢复
1、rman target orcl
2、输入密码
3、shutdown immediate
4、startup mount
5、run
{
set until time "to_date('03/15/08
23:25:00','mm/dd/yy hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;
}可以精确恢复到某个时间点