ORACLE 搭建NETSOL环境(自用)

最新推荐文章于 2024-12-11 16:04:22 发布

天翔灬

最新推荐文章于 2024-12-11 16:04:22 发布

阅读量273

点赞数

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/Ytx904871086/article/details/107322717

版权

ORACLE配置环境

drop tablespace WFS_TABLESPACE including contents and datafiles; --删除表空间
drop tablespace wfs_indexes including contents and datafiles;
drop tablespace wfs including contents and datafiles;
drop tablespace tbs_wfs_report including contents and datafiles;
drop user zxcms cascade; --删除用户
drop tablespace cms including contents and datafiles;

–建wfs表空间
CREATE TABLESPACE WFS_TABLESPACE DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/WFS_TABLESPACE.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间
CREATE TABLESPACE wfs_indexes DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/wfs_indexes.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间
CREATE TABLESPACE wfs DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/wfs.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间
CREATE TABLESPACE tbs_wfs_report DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/tbs_wfs_report.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间

–建cms表空间
CREATE TABLESPACE TBS_LS_CDE DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/TBS_LS_CDE.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间
CREATE TABLESPACE TBS_LS_REPAYMENT DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/TBS_LS_REPAYMENT.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间
CREATE TABLESPACE TBS_LS_DOC DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/TBS_LS_DOC.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间
CREATE TABLESPACE TBS_LS_AMORTIZATION DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/TBS_LS_AMORTIZATION.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间
CREATE TABLESPACE TBS_LS_DATA DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/TBS_LS_DATA.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间
CREATE TABLESPACE TBS_LS_HTR DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/TBS_LS_HTR.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间
CREATE TABLESPACE TBS_LS_RECEIPT DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/TBS_LS_RECEIPT.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间
–创建was表空间
CREATE TABLESPACE was DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/was.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间

–ip: 10.21.68.24
select sid,serial# from v$session where username=‘ZXWFS’ --查询进程序列
alter system kill session ‘129,75’;–杀死进程

–查询所有表空间
select * from dba_tablespaces

create user zxcms identified by zxcms; --创建用户
grant connect to zxcms; --赋权限
grant dba to zxcms;
grant resource to zxcms;
grant unlimited tablespace to zxcms;

–指定表空间
alter user zxwfs default tablespace wfs_tablespace;
alter user zxwfs default tablespace wfs_indexes;
alter user zxwfs default tablespace wfs;
alter user zxwfs default tablespace tbs_wfs_report;

alter user zxcms default tablespace TBS_LS_CDE;
alter user zxcms default tablespace TBS_LS_REPAYMENT;
alter user zxcms default tablespace TBS_LS_DOC;
alter user zxcms default tablespace TBS_LS_AMORTIZATION;
alter user zxcms default tablespace TBS_LS_DATA;
alter user zxcms default tablespace TBS_LS_HTR;
alter user zxcms default tablespace TBS_LS_RECEIPT;

alter user wasuat default tablespace was;

create or replace directory data_pump_dir as ‘/u01/app/oracle/admin/qycx/dpdump/’ --创建导入目录

select * from dba_directories --查询目录 /home/oracle/DataDump
create directory DataDump as ‘/home/oracle/DataDump/’;
grant read,write on directory DataDump to public;

–备份
expdp zxuser/zxuser@qycx directory=DataDump dumpfile=zxwfs20181231.DMP logfile=zxwfs20181231.log schemas=zxwfs

expdp zxuser/zxuser@qycx directory=DataDump dumpfile=zxcms20181231.DMP logfile=zxwfs20181231.log schemas=zxcms

–导入库
impdp zxuser/zxuser@qycx directory=data_pump_dir dumpfile=wfs20181128.dmp logfile=wfs20181128.log REMAP_SCHEMA=BBAFC_WFS:zxwfs full=Y;

impdp zxuser/zxuser@qycx directory=data_pump_dir dumpfile=cms20181128.dmp logfile=cms20181128.log REMAP_SCHEMA=BBAF_RETAIL_PRD:zxcms full=Y;

exp zxuser/zxuser@qycx file=’/u01/app/oracle/admin/qycx/dpdump/was.dmp’ fromuser=was_prd touser=wasuat;
imp EASTBUSCHECK/EASTBUSCHECK@ORCL file='D:\app\orcl\admin\orcl\adump\EASTBUSCHECK.dmp’full =‘Y’

–创建开发表空间
CREATE TABLESPACE qyzx DATAFILE ‘/u01/app/oracle/admin/qycx/dpdump/qyzx001.dbf’ SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --创建表空间

–开发用户
create user zxuser identified by zxuser; --创建用户
grant connect to zxuser; --赋权限
grant dba to zxuser;
grant resource to zxuser;
grant unlimited tablespace to zxuser;