Oracle的使用

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;

 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;