Oracle常用命令

Oracle监听、启动及关闭

--启动监听
lsnrctl start
--修改shm大小
sudo mount -o remount,size=1G /dev/shm/
df -h | grep shm
--系统用户登录
sqlplus / as sysdba

--启动database
startup
--若启动报错 ORA-00845: MEMORY_TARGET not supported on this system，说明shm较小
-- 关闭
shutdown immediate;

Wallet相关

-- 生成钱包(该过程会产生一个主密钥存储在wallet中)：
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "oracle123";
--打开钱包
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "oracle123";
--关闭钱包
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "oracle123";

-- 使用示例
----------------------------------------------------------------------------------------
--系统管理员打开钱包
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "oracle123";
--在钱包打开的前提下：
conn c##user1;
--一行显示多个数据
set linesize 1000;
--使用默认加密算法
create table t1 (id int, name varchar(128) ENCRYPT);
--插入数据
insert into t1 values (1,'zhangsan');
insert into t1 values (2,'lisi');
insert into t1 values (3,'wangwu');
insert into t1 values (4,'zhaoliu');
insert into t1 values (5,'tianqi');
select * from t1;
--授予权限给其他用户
grant select on t1 to c##user2;
--user2用户进行查看
conn c##tjy2;
select * from c##user1.t1; --ok
--管理员关闭钱包
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "oracle123";
--使用系统管理员关闭钱包进行查看
--用户c##user1和c##user2执行select语句均报错
ORA-28365: wallet is not open

用户相关

--创建用户(19c版本用户名需要加c##)
create user c##uxdb identified by 123456;
create user c##user1 identified by 123456;
create user c##user2 identified by 123456;

--给用户授权
-- 授权用户连接权限
grant connect, resource to zhangsan; 
--授予用户创建session的权限，即登陆权限
grant create session to c##uxdb;
grant create session to c##user1;
grant create session to c##user