Docker安装
- docker pull jaspeen/oracle-11g 下载镜像
- Oracle Database 11g Release 2 for Linux x86-64 下载oracle11g的文件,两个文件都要解压
docker run --privileged --restart=always --name oracle11g -d -p 1521:1521 -v /opt/oracle:/install jaspeen/oracle-11g
安装oracle
- docker commit oracle11g oracle11g_installed 创建安装好的镜像文件
- su - oracle
- sqlplus / as sysdba
- alter user scott account unlock;
- commit;
- 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
|