搭几年的数据库,总结一下,以备后用
说在前面:此文档是应需现网将相关数据库导回进行测试数据库搭建出的。
搭建数据库:
查看表空间,数据文件:
SELECT * FROM DBA_TABLESPACES;
SELECT * FROM DBA_DATA_FILES;
a、 新建表空间:具体见表空间
/*
Before run the script, please modify the file's path, name and size.
To run the script, connect as a user with SYSDBA privilege.
*/
CREATE TABLESPACE TBS_ICD3_PUB_BJ
DATAFILE '/opt/oracle/app/oradata/ora11g/tablespace/tbs_csp30_beijing_01' SIZE 2000M REUSE AUTOEXTEND OFF,
'/opt/oracle/app/oradata/ora11g/tablespace/tbs_csp30_beijing_02' SIZE 500M REUSE AUTOEXTEND ON
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
--DROP TABLESPACE TBS_CSP_PUB_TIGO INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE TBS_ICD3_PUB_BJ_TMP
TEMPFILE '/opt/oracle/app/oradata/ora11g/tablespace/tbs_csp30_beijing_03' SIZE 500M REUSE AUTOEXTEND on
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
b、 建用户
CREATE USER ICD_BJ IDENTIFIED BY icd
DEFAULT TABLESPACE TBS_ICD3_PUB_BJ
TEMPORARY TABLESPACE TBS_ICD3_PUB_BJ_TMP
PROFILE DEFAULT;
GRANT EXP_FULL_DATABASE TO ICD_BJ;
GRANT IMP_FULL_DATABASE TO ICD_BJ;
GRANT CONNECT TO ICD_BJ;
GRANT RESOURCE TO ICD_BJ;
GRANT UNLIMITED TABLESPACE TO ICD_BJ;
-- If the version is 10g or 11g, please delete the double hyphen (--) in the following two lines.
GRANT DEBUG CONNECT SESSION TO ICD_BJ;
GRANT DEBUG ANY PROCEDURE TO ICD_BJ;
c、 然后用上面的用户登录,将现网导回的对象导入即可。
1、用PL\SQL导出域对象,具体操作:Tools----Export User Objects
只需要选择Single file一个选项,其他都不要选。
2、导表数据,具体操作:Tools----Export Tables
表数据用SQL格式导出,复选框都不选, commit record 1000条, where 过滤 ROWNUM < 10000 , 10000 数目自己定。如果需要将全部数据导出where过滤条件不需要。
3、上面两个文件导回来后进行环境搭建,导入域:直接在command窗口, @ 回车选择文件就行了。
4、 导入表数据:表数据导入的时候一定要用 tools --> import table,选择导入文件即可。用command窗口执行很慢。