linux运维、架构之路-Oracle基本操作
一、表空间和用户
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;
三、linux下重启oracle数据库
(1) 以oracle身份登录数据库,命令:su – oracle
(2) 进入Sqlplus控制台,命令:sqlplus /nolog
(3) 以系统管理员登录,命令:connect /as sysdba
(4) 启动数据库,命令:startup
(5) 如果是关闭数据库,命令:shutdown immediate
(6) 退出sqlplus控制台,命令:exit
(7) 进入监听器控制台,命令:lsnrctl
(8) 启动监听器,命令:start
(9) 退出监听器控制台,命令:exit
(10)重启数据库结束
成功最有效的方法就是向有经验的人学习!