//windows键+R 输入cmd 进入输入:(用sqlplus操作)
sqlplus conn as sysdba
依次输入用户名system 密码orcl;
或者使用sqldeveloper,plsql等软件,在连接system用户的情况下, 进行以下1,2,3,4的操作,
第5步的导入命令直接win+R用commend窗口, 不需要SQLplus.
1./*创建临时表空间*/-- 创建一个临时的表空间,若不创建默认的临时表空间为temp
create temporary tablespace sunway_1_temp
tempfile 'E:\app\Administrator\sunway_1_temp.dbf'
size 100m
autoextend on
next 50m maxsize 20480m
extent management local;
create temporary tablespace sunway_1_temp
tempfile '/oracle/app/oracle/oradata/orcl/sunway_1_temp.dbf'
size 100m autoextend on next 50m maxsize 20480m
extent management local;
2./*创建数据表空间*/
create tablespace sunway_1
logging
datafile 'E:\app\Administrator\sunway_1.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
create tablespace sunway_1
logging
datafile '/oracle/app/oracle/oradata/orcl/sunway_1.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
3./*创建用户*/
创建用户和授权可以用sqlplus, 或者用数据库软件在system用户下建
sqlplus连接: 打开 cmd 命令窗口,输入 sqlplus / as sysdba
create user sunway identified by sunway
default tablespace sunway_1
temporary tablespace sunway_1_temp;
4./*授权*/
grant connect,resource,dba to sunway;
--如果有删除用户的权限,则可以:
drop user jzzg8 cascade;
--加了cascade就可以把用户连带的数据全部删掉。
5.导入dmp文件(不需要sqlplus), win+R命令行直接输入导入导出命令
//导入dmp文件:格式说明——imp 用户名/秘钥@数据库版本名称 file=dmp文件路径 full=y,然后回车,即开始导入数据库.若需打印日志:log=E:\imp.log
imp test/111111@XE file=D:/zjyd.dmp full=y
imp jszg1/jszg1@orcl file=E:/dump/dir/exp.dmp full=y
imp test/111111@XE file=D:/zjyd.dmp full=y log=E:\imp.log(日志位置)
imp TBBANK/TBBANK@orcl file=E:/dump/dir/exp.dmp fromuser=YBTIS touser=YBBANK log=E:\dmp\imp.log indexes=n statistics=none
imp TBBANK/TBBANK@orcl file=E:/dump/dir/exp.dmp fromuser=YBTIS touser=YBBANK log=E:\dmp\imp.log indexes=n statistics=none tables=(CHB_ASSET_ATTRVALUE)
导入指定表
/*导出数据库的语句*/
win + R :CMD-->
exp GYTIS001/GYTIS001@172.17.20.90:1521/orcl file=C:\Users\Administrator\Desktop\GY001.dmp
exp YB32TIS2/yb32tis2@10.10.10.48:1521/XDDB file=E:\dmp\YB32TIS2001.dmp
//导入dmp文件:格式说明——imp 用户名/秘钥@数据库版本名称 file=dmp文件路径 full=y,然后回车,即开始导入数据库.若需打印日志:log=E:\imp.log
imp test/111111@XE file=D:/zjyd.dmp full=y log=E:\imp.log
imp jszg1/jszg1@orcl file=E:/dump/dir/exp.dmp full=y log=E:\dmp\imp.log
imp test/111111@XE file=D:/zjyd.dmp full=y log=E:\imp.log
imp TBBANK/TBBANK@orcl file=E:/dump/dir/exp.dmp fromuser=YBTIS touser=YBBANK log=E:\dmp\imp.log indexes=n statistics=none
imp hmjc1/hmjc1@192.168.70.97:1521/orcl file=/home/oracle/dmp/chb_asset_attrvalue.dmp full=y log=/home/oracle/dmp/hmimp2.log
imp hmjc1/hmjc1@192.168.70.97:1521/orcl file=/home/oracle/dmp/HMBANK0902.dmp full=y log=/home/oracle/dmp/hmimp3.log
注意事项:
Oracle创建directory
一般创建directory都是为了用数据泵导入/导出数据用,其实directory还有很多别的用处,本文不做阐述
数据泵说明: 导出命令expdp, 只导出在连接的数据库的数据泵目录中, 无法直接远程导出, 还需要物理传输, 想要实现直接远程导出, 用exp命令, 比如只导一个表的数据, 用exp就比较方便.
1、新建directory的语法
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
/*dmp文件要放在此路径下,才可进行导入*/
例如:
create or replace directory BAK_DIR as 'E:\dump\dir';
create or replace directory BAK_DIR as '/home/dump/dir';
这样把目录e:\dump\dir设置成dump_dir代表的directory
2、查询有哪些directory
select * from dba_directories;
3、赋权
grant read,write on directory BAK_DIR to sunway;
4、删除
drop directory BAK_DIR;
/*根据之前directory的路径,用数据泵导入dmp文件*/
impdp sunway/sunway DIRECTORY=BAK_DIR DUMPFILE=MDM814_V8.1.8.1.DMP remap_schema=MDM81151:sunway remap_tablespace=MDM81151:sunway_1 TRANSFORM=segment_attributes:n
路径 源库用户名:新建库用户名 源库表空间名:新建库表空间名
impdp sunway/sunway DIRECTORY=BAK_DIR DUMPFILE=MDM81173_v11.2.0.3.0.DMP remap_schema=MDM81151:sunway remap_tablespace=MDM81151:sunway_1 TRANSFORM=segment_attributes:n
linux:
impdp sunway/sunway@192.168.1.188:1521/orcl DIRECTORY=BAK_DIR DUMPFILE=MDM81173_v11.2.0.3.0.DMP remap_schema=MDM81151:sunway remap_tablespace=MDM81151:sunway_1 TRANSFORM=segment_attributes:n
impdp sunway/sunway@192.168.1.199:1521/orcl DIRECTORY=BAK_DIR DUMPFILE=MDM9init_V9.0.17.1.dmp remap_schema=MDM9INIT:sunway remap_tablespace=MDM9INIT:sunway_1 TRANSFORM=segment_attributes:n
1.查询表空间语句:
select username,default_tablespace from dba_users;
/*查询当前用户默认表空间*/
select username,default_tablespace from dba_users;
/*查询所有表空间*/
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
/*查询临时表空间*/
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
/*2.删除表空间:*/
2.1可以先将其offline
alter tablespace xx offline;
例:alter tablespace EAS_D_KDQHMS_STANDARD offline;
2.2将磁盘上的数据文件一同删除
drop tablespace xxx including contents and datafiles;
--表空间
drop tablespace EAS_D_KDQHMS_STANDARD including contents and datafiles;
--临时表空间不用offline ,可以直接drop
drop tablespace EAS_T_KDQHMS_STANDARD including contents and datafiles;
2.1可以先将其offline
alter tablespace xx offline;
例:alter tablespace EAS_D_KDQHMS_STANDARD offline;
2.2将磁盘上的数据文件一同删除
drop tablespace xxx including contents and datafiles;
--表空间
drop tablespace EAS_D_KDQHMS_STANDARD including contents and datafiles;
--临时表空间不用offline ,可以直接drop
drop tablespace EAS_T_KDQHMS_STANDARD including contents and datafiles;
3.删除用户:(如果实在删除不了用户,可以重启一下数据库再删除)
1.首先将索要删除的用户锁定,这句必须执行,否则之后杀死进程无效!
alter user 用户名 account lock;
2.从【v$Session】表查看当前用户占用资源,有使用资源的情况下,肯定不能删除用户
select username,sid,serial#,paddr from v$session where username='用户名(必须大写)';
3. 杀死status为【 INACTIVE】的进程,sid和seria#值为该列下的数值
alter system kill session 'sid,serial#';
报错处理--这一步如果报错 ORA-00031:标记要终止的会话
1)select a.spid,b.sid,b.serial#,b.username
from v$process a,v$session b
where a.addr=b.paddr
and b.status='KILLED';
2)kill -9 spid
例如:kill -9 12345
4. 删除用户,如果不成功,即还是会报01940错,因为还有【 INACTIVE】进程没杀死
drop user 用户名 cascade;
drop user xxx;(drop user QHMS cascade;)
如果用户的schema中有objects ,需要加cascade参数,即drop user xxx cascade;
例:
drop user sunway cascade;
4.关闭数据库:
shutdown (shutdown abort)
alter tablespace xx offline;
启动数据库:
startup
关闭数据库
? ?1)正常关闭 shutdown
? ?2) 立即关闭 shutdown immediate
? ?3) 关闭事务 shutdown transactional
? ?4) 强行关闭 shutdown abort,当然谁都不想碰到这种情况。