Docker安装

  1. docker pull jaspeen/oracle-11g 下载镜像
  2. Oracle Database 11g Release 2 for Linux x86-64 下载oracle11g的文件,两个文件都要解压
  3. docker run --privileged --restart=always --name oracle11g -d -p 1521:1521 -v /opt/oracle:/install jaspeen/oracle-11g 安装oracle
  4. docker commit oracle11g oracle11g_installed 创建安装好的镜像文件
  5. su - oracle
  6. sqlplus / as sysdba
  7. alter user scott account unlock;
  8. commit;
  9. conn scott/tiger

基本操作

登录

1
2
3
4
5
6
7
8
# 登录
sqlplus / as sysdba

# 远程登录,命令行配置
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID force=y

# 查看结果
SQL> show parameter remote_login_passwordfile

查看所有的数据库实例

1
select * from v$instance;

查看所有的用户

1
select * from all_users

创建用户

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 创建用户
create user tangf identified by tangf;
# 修改密码(可选)
alter user tangf identified by tangf;
# 创建表空间
create tablespace tangf_tablespace datafile '/data/app/oracle/oradata/tangf_data.dbf' size 200M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;

# 分配用户和表空间
alter user tangf default tablespace tangf_tablespace;

# 可以删除用户及其相关对象
drop user tangf cascade;

授权用户

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 查看权限
select * from session_privs;

# 授权用户
grant create session,create table,create view,create sequence,create trigger,create procedure,unlimited tablespace to tangf;

# 授权操作 Grant/Revoke
grant select on xxx to tangf;
grant all on xxx to tangf;

# 撤销权限
revoke select on xxx from tangf;

查询表空间

1
SELECT tablespace_name,file_name FROM dba_data_files;

密码永不过期

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 密码永不过期
# 命令行以sysdba的方式进入
  sqlplus / as sysdba;
# 查看用户的密码策略,一般是default
  select username,profile from dba_users;
# 查看指定概要文件(如default)的密码有效期设置
  Select * FROM dba_profiles s Where s.profile='DEFAULT' AND  resource_name='PASSWORD_LIFE_TIME';
# 将密码有效期由默认的180天修改成“无限制”
  ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
# 修改密码,相当于重置密码
  alter user tangf identified by tangf;

Mac连接数据库(Navicat连接Oracle)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
# 下载软件包
# https://www.oracle.com/database/technologies/instant-client/macos-intel-x86-downloads.html
# 这里使用11.2.0.4这个版本

cd ~/opt
unzip instantclient-basic-macos.x64-11.2.0.4.0.zip
unzip instantclient-sqlplus-macos.x64-11.2.0.4.0.zip

cd ~/opt/instantclient_11_2
ln -s libclntsh.dylib.11.1 libclntsh.dylib

ln -s libocci.dylib.11.1 libocci.dylib

mkdir ~/lib
ln -s ~/opt/instantclient_11_2/{libclntsh.dylib.11.1,libnnz11.dylib,\
libociei.dylib} ~/lib/

ln -s ~/opt/instantclient_11_2/{libsqlplus.dylib,libsqlplusic.dylib} ~/lib/
export PATH=~/opt/instantclient_11_2:$PATH

sqlplus tangf/xxxx@172.26.10.61/orcl

例子

1
2
3
4
create user XXZYGX identified by tangf;
create tablespace TS_XXZYGX_DATA datafile '/data/app/oracle/oradata/TS_XXZYGX_DATA.dbf' size 200M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
alter user XXZYGX default tablespace TS_XXZYGX_DATA;
grant create session,create table,create view,create sequence,create trigger,create procedure,unlimited tablespace to XXZYGX;

FAQ

输入不能回退字符

1
2
3
4
cd /home/oracle/
vi .bash_profile
# 添加一行stty erase ^H
source .bash_profile