Oracle 常用指令 & SQL - 管理 User/Schema 篇 (Manage USER/SCHEMA section)

User 相關指令

-- Users/Schema 建立流程
-- 建立 Scheam
create user user_name identified by password;

-- 授權連線/資源
grant create session, resource to user_name;

-- 建立 User 專屬 tablespace 空間 (依需求)
create tablespace tbs1 datafile 1g autoextned on next 100m maxsize unlimited;

-- 調整 user 的預設 tablespace (預設剛建好的schema都是使用users這個tablespace)(依需求)
alter user user_name default tablespace tbs1;

-- 調整 user 對 tablespace 的使用空間 (依需求)
alter user user_name quota unlimited on tbs1;

-- 測試以下執行過程都沒錯誤的話，就表示schema建立正常無誤
conn user_name/password
create table t1 (c1 int);
insert into t1 values (1);
drop table t1 purge;

-- 查詢 Users 認證

col username format a30
col password format a30
col account_status format a25
SELECT username, password, password_versions, account_status, authentication_type 
FROM dba_users;

-- 查詢 Users default tablespace profile & value

col username format a30
col password format a30
col profile format a30
SELECT username, default_tablespace, temporary_tablespaceE, profile 
FROM dba_users;

-- 修改 User Password

ALTER USER user_name IDENTIFIED BY "Password" ACCOUNT UNLOCK;

-- Change User to Schema Only. (19c later)

ALTER USER user_name NO AUTHENTICATION;

-- Create Proxy Session User

ALTER USER schema GRANT CONNECT THROUGH user_name;
SELECT * FROM proxy_users;