1:安装Oracle及新建数据库
Oracle 11g安装图解
http://www.cnblogs.com/qianyaoyuan/archive/2013/05/05/3060471.html
http://jingyan.baidu.com/article/380abd0aadb63a1d90192cec.html
Oracle图形化新建数据库
http://jingyan.baidu.com/article/90bc8fc80c01f2f653640c1f.html
2:数据库建立成功之后,新建表空间,临时表空间,分区表空间
2.1:建立表空间,临时表空间,分区表空间,路径和大小根据实际情况修改
2.2:新建用户 CCENSE/CCENSE
--建立表空间,路径和大小根据现场情况修改 CREATE TABLESPACE "CCEN" LOGGING DATAFILE 'E:\oracle2\bus\CCEN.ora' SIZE 1000M AUTOEXTEND ON NEXT 1000M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO / --建立临时表空间,路径和大小根据现场情况修改 CREATE TEMPORARY TABLESPACE "CCEN_TMP" TEMPFILE 'E:\oracle2\bus\CCEN_TMP.ora' SIZE 1000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M / --建立分区表空间tbs_par1 CREATE TABLESPACE "TBS_PAR1" LOGGING DATAFILE 'E:\oracle2\bus\TBS_PAR1.ora' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO / --建立用户 CREATE USER "CCENSE" PROFILE "DEFAULT" IDENTIFIED BY "CCENSE" DEFAULT TABLESPACE "CCEN" TEMPORARY TABLESPACE "CCEN_TMP" ACCOUNT UNLOCK; GRANT "CONNECT" TO "CCENSE"; GRANT "DBA" TO "CCENSE"; /
3:Oracle imp/impdp 导入dmp文件到数据库
使用EXPDP和IMPDP时应该注意的事项:
EXP和IMP是客户端工具程序,它们既可以在客户端使用,也可以在服务端使用。
EXPDP和IMPDP是服务端的工具程序,他们只能在ORACLE服务端使用,不能在客户端使用。
IMP只适用于EXP导出的文件,不适用于EXPDP导出文件;IMPDP只适用于EXPDP导出的文件,而不适用于EXP导出文件。
注意:EXP不会导出空表(可能会对存储过程有影响)
3.1: exp/imp导出/导入数据
--exp导出数据 exp CCENSE/CCENSE@OracleDB file=D:\DBBackUp\oracleExp.dmp log=D:\DBBackUp\oracleExp20161103.log --imp导入数据 imp CCENSE/CCENSE@OracleDB file=D:\DBBackUp\oracleExp.dmp log=D:\DBBackUp\oracleImp20161103.log FULL=y; /* 说明:CCENSE/CCENSE@OracleDB --->用户名/密码@数据库实例 file="本地路径+需要导入/导出的dmp文件全称" log="日志文件" FULL=y; */
--给当前用户追加connect,resource,dba [非必须] grant connect,resource,dba to CCENSE;
数据导出: 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中 exp system/manager@TEST file=d:\daochu.dmp full=y 将数据库中system用户与sys用户的表导出 exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys) 将数据库中的表table1 、table2导出 exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2) 将数据库中的表table1中的字段filed1以""打头的数据导出 exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\" 上面是常用的导出,对于压缩我不太在意,用winzip把dmp文件可以很好的压缩。 不过在上面命令后面 加上 compress=y 就可以了 数据的导入 将D:\daochu.dmp 中的数据导入 TEST数据库中。 imp system/manager@TEST file=d:\daochu.dmp 上面可能有点问题,因为有的表已经存在,然后它就报错,对该表就不进行导入。 在后面加上 ignore=y 就可以了。 将d:\daochu.dmp中的表table1 导入 imp system/manager@TEST file=d:\daochu.dmp tables=(table1) 3: 只导出表结构,不导出表数据 添加 rows=n
3.2:expdp/impdp导出/导入数据
使用impdp命令,需要在oracle数据库服务器操作:
使用sqlplus或者Oracle客户端(PL/SQL) 链接到相应的Oracle数据库实例(进行下面第一,第二两步骤的操作)
一、创建逻辑目录,该命令不会在操作系统创建真正的目录,最好以system等管理员创建。
create directory dpdata1 as 'd:\test\dump'; (查看服务器上若没有存在d:\test\dump目录,则手动新建,把dmp备份文件放到d:\test\dump目录下面)
二、查看管理理员目录(同时查看操作系统是否存在,因为Oracle并不关心该目录是否存在,如果不存在,则出错)
select * from dba_directories;
三、导入数据库
--expdp导出数据 expdp CCENSE/CCENSE@OracleDB directory = "dpdata1" dumpfile ="oracleExpdp.dmp" logfile = oracleExpdp20161103.log --impdp导入数据 impdp CCENSE/CCENSE@OracleDB directory="dpdata1" dumpfile="oracleExpdp.dmp" logfile = oracleImpdp20161103.log FULL=y; /* 说明:CCENSE/CCENSE@OracleDB --->用户名/密码@数据库实例 directory="在步骤一中创建的逻辑目录" dumpfile="需要导入/导出的dmp文件全称" logfile="日志文件" FULL=y; */
--其他SQL --表空间[10G]和临时表空间[1G] /*表空间*/ )), ) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name; /*临时表空间*/ file_size,autoextensible from dba_temp_files; -- 查看端口是否被占用 netstat " -- 查看监听状态,启动监听,关闭监听 lsnrctl status LISTENER2 lsnrctl start LISTENER2 lsnrctl stop LISTENER2 --访问地址 linux sqlplus ccense/OracleDB; win sqlplus ccense/OracleDB;
参考文档:http://www.cnblogs.com/luluping/archive/2010/03/16/1687093.html
http://www.cnblogs.com/RoyYu/archive/2012/12/09/2810492.html
--查看表空间的使用情况 select b.file_name 物理文件名, b.tablespace_name 表空间, b.bytes 大小M, (b.bytes ))) 已使用M, substr((b.bytes ))) , , ) 利用率 from dba_free_space a, dba_data_files b where a.file_id = b.file_id group by b.tablespace_name, b.file_name, b.bytes order by b.tablespace_name; --表空间查询SELECT UPPER(F.TABLESPACE_NAME) TABLESPACE_NAME, -- 表空间名, D.TOT_GROOTTE_MB TOT_GROOTTE_MB, -- 表空间大小(G), D.TOT_GROOTTE_MB - F.TOTAL_BYTES USEDMB, ---已使用空间(G), TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') || '%' USAGERATIO, -- 使用比, F.TOTAL_BYTES, -- 空闲空间(G), d.MAXBYTES --表空间最大能扩到多大 FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 3) TOTAL_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 3) TOT_GROOTTE_MB, ROUND(sum(dd.MAXBYTES) / (1024 * 1024 * 1024), 3) MAXBYTES FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 2 DESC; --查看表占用表空间的大小,查看各表数据的行数 SELECT t.table_name, t.NUM_ROWS, s.BYTES, t.OWNER, t.TABLESPACE_NAME FROM dba_tables t, user_segments s where t.TABLE_NAME = s.segment_name DESC; --给sde表空间再添加数据文件 ALTER TABLESPACE CCEN ADD DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\NEWBUS2\CCEN.ORA' SIZE 2048M;--AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED [待验证] --扩展现有表空间数据文件ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 32000M;--查询数据库表行数select t.table_name,t.num_rows from user_tables t --修改数据文件大小alter database datafile 'H:\ORACLE\PRODUCT\10.1.0\ORADATA\ORACLE\USERS01.DBF' RESIZE 10240M; --查询某表空间下有哪些数据文件select * from dba_data_files where tablespace_name='ARCHIVES'; --删除某个数据文件alter tablespace tablespace_name drop datafile 'd:\sp01.dbf';
--移动数据文件 有时,如果你的数据文件所在的磁盘损坏时,该数据文件将不能在使用,为了能够重新使用,需要将这些文件的副本移动到其他的磁盘,然后恢复。
下面以移动数据文件sp01.dbf为例来说明:
--1.确定数据文件所在的表空间
select tablespace_name from dba_data_files where fille_name='d:\sp01.dbf';
--2.使表空间脱机,确保数据文件的一致性,将表空间转变为offline的状态。
alter tablespace sp01 offline;
--3.使用命令移动数据文件到指定的目标位置
host move d:\sp01.dbf c:\sp01.dbf;
--4.执行alter tablespace命令 --在物理上移动了数据之后,还必须执行alter tablespace命令对数据库文件进行逻辑修改
alter tablespace sp01 rename datafile 'd:\sp01.dbf' to 'c:\sp01.dbf';
--5.使表空间联机
alter tablespace sp01 online;
imp 只导入数据,不导入表结构,在命令里加上ignore=y就可以了
ignore=y相当于,如果没有的表,创建并倒入数据,如果已经有的表,忽略创建的,但不忽略倒入。
在使用IMP导入并使用ignore=y参数时,Oracle根本不检查要导入的数据结构和现存在数据库中表的结构是否相同
DESTROY=Y 覆盖已存在的表
exp 命令导出表结构,不导出表数据。只需在命令行里加一个参数rows=n即可。表示不导出表数据
---------------------------------------------------------------------------------------------------------------------------------
--查看指定的表空间是否为自动扩展 select file_name,autoextensible,increment_by from dba_data_files where tablespace_name = '表空间名'; --如果不是自动扩展想修改为自动扩展的话需要操作 alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend on; alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend on next xxx maxsize xxxx; --关闭自动扩展 alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend off; --开启自动扩展功能语法: alter database datafile '对应的数据文件路径信息' autoextend on; --关闭自动扩展功能语法: alter database datafile '对应的数据文件路径信息' autoextend off;
dba_tables : 系统里所有的表的信息,需要DBA权限才能查询 all_tables : 当前用户有权限的表的信息(只要对某个表有任何权限,即可在此视图中看到表的相关信息) user_tables: 当前用户名下的表的信息 select * from dba_tables where TABLESPACE_NAME='CCENSE'--'SYSTEM'--'SYSAUX';--oracle查询所有表格数据量(行数)select t.table_name,t.num_rows from user_tables t;--查询行数小于100W的表,并用逗号分隔转换为一行select wm_concat(t.table_name) from user_tables tWHERE t.num_rows<1000000order by t.num_rows desc; 所以以上3个视图中,user_tables的范围最小,all_tables看到的东西稍多一些,而dba_tables看到最多的信息