数据库建库（常用命令记录）

背景：
1.已有数据库服务器
2.数据库服务器已安装oracle 11g

目标：
1.建表空间
2.建用户空间
3.授权

实现：
实现个屁，我就想找个地方记一下命令行

exp bsScan/bsScan@192.168.10.235:1521/orcl file=E:\bsScan.dmp

imp bsScan/bsScan@218.65.178.235:1521/orcl file=E:\bsScan.dmp full=y

imp bsScan/bsScan@192.168.10.235:1521/orcl file=E:\bsScan.dmp full=y

exp bsScan/bsScan@218.65.178.235:1521/orcl file=E:\bsScan.dmp

drop user bsScan cascade;
create user bsScan identified by bsScan default tablespace bsScan account unlock;
grant dba to bsScan;

//bs gpssgp
url：192.168.10.235:1521 USERNAME:gpssgp PWD:gpssgp
root @ brt1234
oracle @ oracle

create temporary tablespace gpssgp_temp tempfile’/home/oracle/app/oradata/orcl/gpssgp_temp.dbf’ size 1024m autoextend on next 100m maxsize 10240m extent management local;

create tablespace gpssgp logging datafile’/home/oracle/app/oradata/orcl/gpssgp.dbf’ size 1024m autoextend on next 100m maxsize 10240m extent management local;

create user gpssgp identified by gpssgp default tablespace gpssgp account unlock;

grant dba to gpssgp;

//get datas from gpssgp, transfer to our db

grant select on “GPSSGP”.”BS” to bsScan;
grant select on “GPSSGP”.”BS_460” to bsScan;
grant select on “GPSSGP”.”BS_CDMA” to bsScan;
grant select on “GPSSGP”.”BS_CDMA_460” to bsScan;

//bs scanning
url：192.168.10.236:1521 USERNAME:bsScan PWD:bsScan
root @ brt1234
oracle @ oracle

创建临时表空间
create temporary tablespace bsScan_temp tempfile’/home/oracle/app/oradata/orcl/bsScan_temp.dbf’ size 1024m autoextend on next 100m maxsize 4096m extent management local;
创建表空间
create tablespace bsScan logging datafile’/home/oracle/app/oradata/orcl/bsScan.dbf’ size 1024m autoextend on next 100m maxsize 4096m extent management local;
创建用户
create user bsScan identified by bsScan default tablespace bsScan account unlock;
授权
grant dba to bsScan;

/*******************************************************************************************************/

删除表空间
drop tablespace brtsys including contents and datafiles cascade constraint;

create temporary tablespace TS_JXKP_temp tempfile’/u01/app/oradata/alfresco/TS_JXKP_temp.dbf’ size 1024m autoextend on next 100m maxsize 4096m extent management local;

create tablespace TS_JXKP logging datafile’/u01/app/oradata/alfresco/TS_JXKP.dbf’ size 1024m autoextend on next 100m maxsize 4096m extent management local;

create user brtsys identified by brtsys default tablespace TS_JXKP account unlock;

grant dba to brtsys;

删除用户
drop user brtsys cascade;

create user brtsys identified by brtsys default tablespace TS_JXKP account unlock;

grant dba to brtsys;

导入dmp
impdp brtsys/brtsys@192.168.10.207:1521/alfresco directory=data_pump_dir dumpfile=brtsys.dmp logfile=20180702.log

导入sql
@/u01/app/oradata/alfresco/guiGangData.sql

/****************************************************************************************************/

关闭数据库
shutdown immediate

启动数据库
startup

drop user kpiManager cascade;

create user kpiManager identified by kpiManager default tablespace kpiManager account unlock;

grant dba to kpiManager;

/**********************************************************************************************************************/
修改数据库语言环境为简体中文
alter session set nls_language=’SIMPLIFIED CHINESE’;

修改数据库语言环境为中文
alter session set nls_language=’AMERICAN’;

/*****************************************************************************************************/

sqlplus / as sysdba

查询字符集
select * from nls_database_parameters;

修改字符集
alter database character set internal_use utf8;

@/u01/app/oradata/alfresco/testSql.sql

create temporary tablespace kpiManager_temp tempfile’/home/oracle/app/oradata/orcl/kpiManager_temp.dbf’ size 1024m autoextend on next 100m maxsize 4096m extent management local;

create temporary tablespace kpiManager_temp tempfile’/home/oracle/kpiManager_temp.dbf’ size 1024m autoextend on next 100m maxsize 4096m extent management local;

create tablespace kpiManager logging datafile’/home/oracle/app/oradata/orcl/kpiManager.dbf’ size 1024m autoextend on next 100m maxsize 4096m extent management local;

create tablespace kpiManager logging datafile’/home/oracle/kpiManager.dbf’ size 1024m autoextend on next 100m maxsize 4096m extent management local;

create user kpiManager identified by kpiManager default tablespace kpiManager account unlock;

grant dba to kpiManager;

启动监听
lsnrctl start

conn kpiManager/kpiManager

alfresco

create user kpiManager identified by kpiManager default tablespace kpiManager account unlock;