赞
踩
//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,当然谁都不想碰到这种情况。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。