一、表空间和用户

1、建立表空间及数据路径

CREATE TABLESPACE demon DATAFILE ‘/data/oradata/demon.dbf‘ SIZE 1024 M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1 M;

2、建立用户及授权

CREATE USER demon IDENTIFIED BY demon DEFAULT TABLESPACE demon_default TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK; GRANT DBA TO demon; GRANT RESOURCE TO demon; ALTER USER demon DEFAULT ROLE ALL; GRANT SELECT ANY TABLE TO demon; GRANT UNLIMITED TABLESPACE TO demon; GRANT CREATE ANY TABLE to demon;

#报错：ORA-65096: 公用用户名或角色名无效###

alter session set "_ORACLE_SCRIPT"=true;create user c##demon identified by demon default tablespace demon;

3、修改用户密码

alter user demon identified by demontest;

4、删除用户，及级联关系也删除掉

drop user demon cascade;

5、删除表空间，及对应的表空间文件也删除掉

drop tablespace demon including contents and datafiles cascade constraint;

二、Oracle操作命令

1、查看oracle实例名

切换到oracle用户：su - oracle                  sqlplus sys/ as sysdba 登录

①方法

select name from v$database;

②方法

select instance_name from v$instance;

2、导出库命令

expdp demon/demon directory=DATA_PUMP_DIR dumpfile=demon_`date +%Y%m%d`.dmp job_name=expdp_demon logfile=demon_`date +%Y%m%d`.log schemas=demon reuse_dumpfiles=true

导出指定版本

expdp demon/demon DIRECTORY=DIR_DP DUMPFILE=demon_`date +%Y%m%d`.dmp job_name=demon LOGFILE=demon_`date +%Y%m%d`.log schemas=demon version=11.2.0.4.0

3、查看系统数据泵路径DIRECTORY=EXPDP_BACKUP

SELECT * FROM dba_directories;

4、新库创建数据泵目录

create or replace directory exppump as ‘/data/‘;grant read,write on directory exppump to demon;

5、导入数据

impdp demon/demon DUMPFILE=demon_20200107.dmp job_name=demon LOGFILE=demon_20200107.log version=11.2.0.4.0

6、查看当前库

select name from v$database;

查询当前数据库实例名

select instance_name from v$instance;

查看oracle所有表空间

select tablespace_name from user_tablespaces;

删除用户及表空间

drop user demon cascade;drop tablespace demon including contents and datafiles cascade constraint;