一、Oracle基本设置命令
1、创建
sys;//系统管理员,拥有最高权限
system;//本地管理员,次高权限
scott;//普通用户,密码默认为tiger,默认未解锁
2、登陆
sqlplus / as sysdba;//登陆sys帐户
sqlplus sys as sysdba;//同上
sqlplus scott/tiger;//登陆普通用户scott
3、管理用户
create user zhangsan;//在管理员帐户下,创建用户zhangsan
alert user scott identified by tiger;//修改密码
4,授予权限
1)、默认的普通用户scott默认未解锁,不能进行那个使用,新建的用户也没有任何权限,必须授予权限
grant create session to zhangsan; //授予zhangsan用户创建session的权限,即登陆权限
grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限
grant create table to zhangsan;//授予创建表的权限
grante drop table to zhangsan;//授予删除表的权限
grant insert table to zhangsan;//插入表的权限
grant update table to zhangsan;//修改表的权限
grant all to public;//这条比较重要,授予所有权限(all)给所有用户(public)
2)、oralce对权限管理比较严谨,普通用户之间也是默认不能互相访问的,需要互相授权
grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限
grant drop on tablename to zhangsan;//授予删除表的权限
grant insert on tablename to zhangsan;//授予插入的权限
grant update on tablename to zhangsan;//授予修改表的权限
grant insert(id) on tablename to zhangsan;
grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限,注意,只能是insert和update
grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限
5、撤销权限
基本语法同grant,关键字为revoke
6、查看权限
select * from user_sys_privs;//查看当前用户所有权限
select * from user_tab_privs;//查看所用用户对表的权限
7、操作表的用户的表
select * from zhangsan.tablename
8、权限传递
即用户A将权限授予B,B可以将操作的权限再授予C,命令如下:
grant alert table on tablename to zhangsan with admin option;//关键字 with admin option
grant alert table on tablename to zhangsan with grant option;//关键字 with grant option效果和admin类似
9、角色
角色即权限的集合,可以把一个角色授予给用户
create role myrole;//创建角色
grant create session to myrole;//将创建session的权限授予myrole
grant myrole to zhangsan;//授予zhangsan用户myrole的角色
drop role myrole;删除角色
10、oracle 如何查看当前用户的表空间名称
查询当前用户的表空间
select default_tablespace from dba_users where username='登录用户'
查询所有表空间
-- 1 )方式1:dba_tablespaces --
select * from dba_tablespaces;
--2 )方式2:v$tablespace --
select * from v$tablespace;
查询用户下所有表
-- 1 )方式1:user_tables --
select * from user_tables;
--2 )方式2: dba_tables --
select * from dba_tables where owner='TMS21';
查看表空间下有多少用户,tablespace_name表空间 的名字一定要大写
select distinct s.owner from dba_segments s where s.tablespace_name ='TEST_TABLESPACE';
二、数据库创建
1、 查询数据文件存放路径
select file_name from dba_data_files;
2、创建表空间
表空间要求,初始设置128M,可以自动增长,每次增长32M,最大不限制,采用本地管理。
创建语句:
create tablespace 表空间名 datafile '数据文件名.DBF' size 128M autoextend on next 32M maxsize unlimited extent management local;
3、创建用户并设置权限
创建系统的数据库用户,设置密码和默认的表空间,并设置用户的权限,用户需要设置如下三个权限,connect,resource,create any view。
创建语句:
create user 用户名 identified by 密码 default tablespace 表空间名;
赋权限语句:
grant connect,resource,create any view to 用户名;
4、给用户设置路径的读写权限
设置用户对指定路径的读写权限,以导入数据。
查询可用的路径:
select directroy_name,directory_path from all_directories;
设置对路径的读写权限
grant read,write on directory 路径名 to 用户名;
5、删除数据库
删除表空间及数据库文件
drop tablespace tablespace_name including contents and datafiles;
删除用户权限
如果有删除用户的权限,则可以,加了cascade就可以把用户连带的数据全部删掉。
drop user user_name cascade;
三、数据库泵导入导出
EXP和IMP是客户端工具程序,它们既可以在客户端使用,也可以在服务端使用。
EXPDP和IMPDP是服务端的工具程序,他们只能在ORACLE服务端使用,不能在客户端使用。
1、数据泵导出(expdp)
expdp流程:
执行expdp和impdp命令需要拥有exp_full_database和imp_full_database权限,授权语句如下: eg:
grant exp_full_database,imp_full_database to orcldev;
1)查询路径信息
查看已经创建的路径信息:
SELECT * FROM dba_directories;
2)创建路径
创建路径需要sys权限,需要有create any directory权限才可以创建路径。
选项:DIRECTORY=directory_object
Directory_object用于指定目录对象名称。需要注意,目录对象是使用CREATE
DIRECTORY语句建立的对象,而不是OS目录。
eg:
CREATE OR REPLACE directory backup_path AS 'D:\APP\ORADATA\db_backup';
–创建路径名为dackup_path的路径,并指向硬盘的指定位置
对新创建的路径进行授权操作:
eg:
grant read,write on directory backup_path to orcldev;
–将对路径的读写权限分配各orcldev用户。
3)导出orcldev这个schema的所用对象[schemas or full]
expdp orcldev/oracle@orcldev directory=backup_path dumpfile=orcldev_schema.dmp logfile=orcldev_schema_2013.log schemas=orcldev
4)导出orcldev这个用户下的某些表[tables]
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_table.dmp logfile=orcldev_table_2013.log tables=('TAB_TEST','TAB_A')
5)只导出orcldev这个用户的元数据[content]
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_meta.dmp logfile=orcldev_meta_2013.log SCHEMAS=orcldev CONTENT=METADATA_ONLY
6)只导出orcldev这个用户50%的抽样数据[sample]
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_samp.dmp logfile=orcldev_samp_2013.log schemas=orcldev sample=50
7)采用并行方式备份整库[parallel]
parallel参数只有在oracle10g之后的版本(包含10g)有效。
oracle_online:you can use the DUMPFILE parameter during export operations to specify multiple dump files, by using a substitution variable (%U) in the filename. This is called a dump file template. The new dump files are created as they are needed, beginning with 01 for %U, then using 02,03,and so on.
# 数据泵的并行备份,"%U"表示自动生成递增的序列号
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_parallel_%U.dmp logfile=orcldev_parallel.log parallel=4
# 对应的并行恢复,"%U"表示自动生成递增的序列号
impdp orcldev/oracle directory=dackup_path dumpfile=orcldev_parallel_%U.dmp logfile=orcldev_parallel.log parallel=4
注意事项:
- 不是并行度越高备份恢复越快
- 可以与FILESIZE参数联合使用
- 导入的PARALLEL值和导出时PARALLEL值可以完全不同的,推荐相同
8) 数据泵压缩备份(多文件备份)
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_%U.dmp logfile=orcldev.log compression=ALL
# 对应的恢复不需要进行解压缩,直接导入即可
impdp orcldev/oracle directory=dackup_path dumpfile=orcldev_%U.dmp logfile=orcldev.log
9)并行与压缩备份结合
# 并行与压缩备份联合
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_%U.dmp logfile=orcldev.log compression=ALL parallel=4
# 对应的恢复
impdp orcldev/oracle directory=dackup_path dumpfile=orcldev_%U.dmp logfile=orcldev.log parallel=4
10)导出orcldev这个方案对象,但不包含索引[exclude]
eg: --可以剔除的对象有:VIEW,PACKAGE,FUNCTION,index,constraints,table,schema,user等等
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log SCHEMAS=orcldev EXCLUDE=index
#导出这个orcldev方案,剔除以TEST开头的索引
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log SCHEMAS=orcldev EXCLUDE=INDEX:"LIKE 'TEST%'"
#备份整库但剔除SCOTT这个用户的对象。
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log EXCLUDE=SCHEMA:"='SCOTT'"
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log EXCLUDE=USER:"='SCOTT'"
#注意:include与exclude不能同时使用。
11)PARFILE选项
expdp命令可以调用parfile文件,在parfile里可以写备份脚本,可以使用query选项。
Oracle highly recommends that you place QUERY specifications in a parameter file; otherwise, you might have to use operating system-specific escape characters on the command line before each quotation mark.
如expdp.txt 内容如下:
USERID=orcldev/oracle directory=dackup_path dumpfile=orcldev_parfile.dmp logfile=orcldev_parfile.log TABLES='TAB_TEST' QUERY="WHERE TRAN_DATE=TO_DATE('2013-08-31','YYYY-MM-DD')"
执行方法:expdp parfile=expdp.txt 即可执行备份
使用parfile好处是使用query选项是不用使用转义字符,如果将query参数放到外边的话,需要将""进行转义。
eg:
UNIX写法:
expdp orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log schemas=orcldev INCLUDE=TABLE:\"IN \(\'TEST_A\',\'TEST_B\'\)\"
–在Unix系统执行是需要将单引号进行转义操作,否则会报错。
WINDOWS写法:
expdp orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log schemas=orcldev INCLUDE=TABLE:"IN \('TEST_A','TEST_B')"
12)TABLESPACE导出表空间
expdp orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log tablespaces=user,orcldev
13)Version选项
VERSION选项默认值是COMPATIBLE,即兼容模式。在我们备份的时候,可以指定版本号。
expdp orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log full=Y VERSION=10.2.0.4
14)FLASHBACK_TIME选项
指定导出特定时间点的表数据,可以联系一下FLASHBACK功能。
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_flash.dmp logfile=orcldev_flash.log SCHEMAS=orcldev FLASHBACK_TIME="TO_TIMESTAMP('2013-09-28 14:30:00','DD-MM-YYYY HH24:MI:SS')"
设置用户对指定路径的读写权限,以导入数据。
查询可用的路径:
select directroy_name,directory_path from all_directories;
将后缀名为dmp的文件放到指定的目录下,如果该路径下没有权限设置权限如下:
设置对路径的读写权限
grant read,write on directory 路径名 to 用户名;
执行系统命令impdp:
impdp 用户名/密码 remap_schema=导出的schema名:导入的schema名 remap_tablespace=导出的表空间名:导入的表空间名 dumpfile=数据文件名 directory=路径名 logfile=导入日志名 transform=oid:n;
2、数据泵导入(impdp)
impdp流程:
1)创建表空间、用户及权限
见上文“二、数据库创建”
2)导入用户(从用户scott导入到用户scott,相同用户表空间)
impdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp schemas=scott logfile=impdp.log;
3)数据泵impdp参数说明
impdp 用户名/密码@地址/orcl DIRECTORY=dump_dir DUMPFILE=dump_name.dmp remap_schema=source_schema:target_schema remap_tablespace=source_tablespace:target_tablespace
1.REMAP_DATAFILE
该选项用于将源数据文件名转变为目标数据文件名,在不同平台之间搬移表空间时需要该选项.
REMAP_DATAFILE=source_datafie:target_datafile
2.REMAP_SCHEMA
该选项用于将源方案的所有对象装载到目标方案中.
REMAP_SCHEMA=source_schema:target_schema
3.REMAP_TABLESPACE
将源表空间的所有对象导入到目标表空间中
REMAP_TABLESPACE=source_tablespace:target:tablespace
4.REUSE_DATAFILES
该选项指定建立表空间时是否覆盖已存在的数据文件.默认为N
REUSE_DATAFIELS={Y | N}
5.SKIP_UNUSABLE_INDEXES
指定导入是是否跳过不可使用的索引,默认为N
6.sqlfile 参数允许创建DDL脚本文件
impdp scott/tiger directory=dump_scott dumpfile=a1.dmp sqlfile=c.sql
默认放在directory下,因此不要指定绝对路径
7.STREAMS_CONFIGURATION
指定是否导入流元数据(Stream Matadata),默认值为Y.
8.TABLE_EXISTS_ACTION
该选项用于指定当表已经存在时导入作业要执行的操作,默认为SKIP
TABBLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE |REPLACE}
当设置该选项为SKIP时,导入作业会跳过已存在表处理下一个对象;
当设置为APPEND时,会追加数据;
当设置为TRUNCATE时,导入作业会截断表,然后为其追加新数据;
当设置为REPLACE时,导入作业会删除已存在表,重建表并追加数据;
4)导入表(从scott用户中把表dept和emp导入到system用户中)
impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp tables=scott.dept,scott.emp remap_schema=scott:system logfile=impdp.log table_exists_action=replace (表空间已存在则替换);
5)导入表空间
impdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=example logfile=impdp.log;
6)导入整个数据库
impdb system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y logfile=impdp.log;
7)追加数据
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION
三、exp/imp导入导出
1、exp/imp导入导出
1)远程导出导入
导出
exp 用户名/密码@ip:端口/实例名 file=E:/XXX.DMP full=y
如下:
exp temp/temp@192.168.1.6:1521/orcl file=E:/temp.DMP full=y
导入
imp 用户名/密码@ip:端口/实例名 file=E:/xxx.DMP full=y
如下:
imp temp/temp@192.168.1.6:1521/orcl file=E:/temp.DMP full=y
2)服务器导出导入
imp cms_enterprise/cms_enterprise file=c:/cms_enterprise1013.DMP full=y
2、imp导入不同表空间
1)库信息
原库–表空间:source;用户:source;密码:source。
目标库–表空间:target;用户:target;密码:target。
2)权限设置
grant resource,connect to target;
# 赋 DBA 权限
grant dba to target;
# 撤销此权限,这个位置很关键
revoke unlimited tablespace from target;
# 将用户在 System 表空间的配额置为 0
alter user target quota 0 on system;
# 设置在用户在 target表空间配额不受限。经过上述设置后,就可以用 imp 导入数据,数据将会进入指定的 target表空间:
alter user target quota unlimited on target;
3)导入方式
window下导入方法
imp target/target fromuser=source touser=target file=D:/source.dmp ignore=y grants=n
linux 下导入方法:
imp b/b file=source.dmp log=source.log fromuser=source touser=target ignore=y commit=y buffer=52428800 indexes=n grants=n constraints=n
四、常见异常问题
1、ORA-28000: the account is locked…
ALTER USER username ACCOUNT UNLOCK;
2、oracle11g导出dmp文件时不能导出空表,导致缺表
1)、设置deferred_segment_creation参数(以下语句可以在cmd里登录数据库后执行):
查看参数:
show parameter deferred_segment_creation;
设置参数:
alter system set deferred_segment_creation=false;
注意:该值设置后对以前导入的空表不产生作用,仍不能导出,只能对后面新增的表产生作用。
2)、导出以前导入的空表
a,分析用户下的所有表:将该sql语句查询出的结果都执行一遍(目的为了查询空表)
select 'analyze table '||table_name||' compute statistics;' from user_tables;
b,查询该用户下所有的空表:将第二条sql语句的执行结果都执行一遍
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
c,执行之后就可以导出空表了
3) expdp导出异常
导出语句:
expdp name/password@orcl directory=backup_path dumpfile=test.dmp logfile=test.log schemas=test;
错误:expdp ORA-12154: TNS:could not resolve the connect identifier specified
解决方案:
增加ip端口:
expdp name/password@192.168.6.66:1521/orcl directory=backup_path dumpfile=test.dmp logfile=test.log schemas=test;
4)expdp/impdp出现ORA-39001、ORA-39000
说明:从oracle 10g采用数据泵方式导出,导入到oracle 11g时问题,由于源数据版本与目标数据库版本不一致,遭遇ORA-39001、ORA-39000
解决方法:
需要expdp导出时加上目标数据的version
查看oracle版本:
# 两种方法
select * from v$version;
select banner from sys.v_$version;
5)expdp出现ORA-39002、ORA-39070、ORA-39087
错误语句:
expdp name/password@192.168.6.66:1521/adc DIRECTORY=backup_path dumpfile=test.dmp schemas=test version=11.2.0.1.0;
错误描述:
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name BACKUP_PATH is invalid
解决方案:
重新创建备份目录
先删除原先创建的备份目录:
drop directory backup_path;
重新创建新的备份目录:
create or replace directory backup_path as '/db5/wangyaodong/dmp';
赋权限:
grant read,write on directory backup_path to public;