select tablespace_name,
block_size,
status,
contents,
logging,
extent_management,
plugged_in,
bigfile
from dba_tablespaces
----------------
14-sep-15
R_GROUP
Y
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N NO
R_GROUP
14-SEP-15 03.10 N
.34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ---------
PROFILE INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM
--------------- --------------- --------------- ------------ - -------- - ---
LAST_LOGIN O
--------------- -
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME N PASSWORD N YES
R_GROUP
Y
SYSBACKUP ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
18 rows selected.
SQL> col DEFAULT_TABLESPACE for a15
SQL> /
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TABLESPACE CREATED PROFILE
--------------- ------------------------------ --------- ---------------
INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- --------------- ------------ - -------- - --- --------------- -
SYS ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
SYS_GROUP 10G 11G N PASSWORD N YES Y
SYSTEM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
SYS_GROUP 10G 11G N PASSWORD N YES 14-SEP-15 03.15 Y
.40.000000000 P
M +05:30
GSMADMIN_INTERN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
AL KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
AUDSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
GSMUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
XS$NULL ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SYSKM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
APPQOSSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N NO 14-SEP-15 03.10 N
R_GROUP .34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME N PASSWORD N YES Y
R_GROUP
SYSBACKUP ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TABLESPACE CREATED PROFILE
--------------- ------------------------------ --------- ---------------
INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- --------------- ------------ - -------- - --- --------------- -
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
18 rows selected.
SQL> col TEMPORARY_TABLESPACE for a12
SQL> /
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TA CREATED PROFILE INITIAL_RSRC_CO
--------------- ------------ --------- --------------- ---------------
EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- ------------ - -------- - --- --------------- -
SYS ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT SYS_GROUP
10G 11G N PASSWORD N YES Y
SYSTEM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT SYS_GROUP
10G 11G N PASSWORD N YES 14-SEP-15 03.15 Y
.40.000000000 P
M +05:30
GSMADMIN_INTERN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
AL KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
AUDSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
GSMUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
XS$NULL ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SYSKM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
APPQOSSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N NO 14-SEP-15 03.10 N
.34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
N PASSWORD N YES Y
SYSBACKUP ########## OPEN 05-MAR-16
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TA CREATED PROFILE INITIAL_RSRC_CO
--------------- ------------ --------- --------------- ---------------
EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- ------------ - -------- - --- --------------- -
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
18 rows selected.
SQL> select ^C
SQL> select USERNAME,ACCOUNT_STATUS,
2 DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,
3 PROFILE FROM DBA_USERS;
USERNAME ACCOUNT_STATU DEFAULT_TABLESP TEMPORARY_TA PROFILE
--------------- ------------- --------------- ------------ ---------------
SCOTT OPEN DMTS TEMP DEFAULT
AUDSYS EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
XS$NULL EXPIRED & LOC USERS TEMP DEFAULT
KED
ORACLE_OCM EXPIRED & LOC USERS TEMP DEFAULT
KED
DIP EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMCATUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMADMIN_INTERN EXPIRED & LOC SYSAUX TEMP DEFAULT
AL KED
APPQOSSYS EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
XDB EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
DBSNMP EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOC SYSTEM TEMP DEFAULT
KED
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
18 rows selected.
SQL> SAVE dba_users.sql
Created file dba_users.sql
SQL> col USERNAME for a8
SQL> /
USERNAME ACCOUNT_STATU DEFAULT_TABLESP TEMPORARY_TA PROFILE
-------- ------------- --------------- ------------ ---------------
SCOTT OPEN DMTS TEMP DEFAULT
AUDSYS EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
XS$NULL EXPIRED & LOC USERS TEMP DEFAULT
KED
ORACLE_O EXPIRED & LOC USERS TEMP DEFAULT
CM KED
DIP EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMCATUS EXPIRED & LOC USERS TEMP DEFAULT
ER KED
GSMADMIN EXPIRED & LOC SYSAUX TEMP DEFAULT
_INTERNA KED
L
APPQOSSY EXPIRED & LOC SYSAUX TEMP DEFAULT
S KED
XDB EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
DBSNMP EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
ANONYMOU LOCKED SYSAUX TEMP DEFAULT
S
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOC SYSTEM TEMP DEFAULT
KED
SYSBACKU OPEN SYSTEM TEMP DEFAULT
P
18 rows selected.
SQL> col ACCOUNT_STATUS for a17
SQL> /
USERNAME ACCOUNT_STATUS DEFAULT_TABLESP TEMPORARY_TA PROFILE
-------- ----------------- --------------- ------------ ---------------
SYSBACKU OPEN SYSTEM TEMP DEFAULT
P
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
ANONYMOU LOCKED SYSAUX TEMP DEFAULT
S
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
XDB EXPIRED & LOCKED SYSAUX TEMP DEFAULT
APPQOSSY EXPIRED & LOCKED SYSAUX TEMP DEFAULT
S
GSMADMIN EXPIRED & LOCKED SYSAUX TEMP DEFAULT
_INTERNA
L
GSMCATUS EXPIRED & LOCKED USERS TEMP DEFAULT
ER
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_O EXPIRED & LOCKED USERS TEMP DEFAULT
CM
XS$NULL EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
SCOTT OPEN DMTS TEMP DEFAULT
18 rows selected.
SQL> col USERNAME for a12
SQL> /
USERNAME ACCOUNT_STATUS DEFAULT_TABLESP TEMPORARY_TA PROFILE
------------ ----------------- --------------- ------------ ---------------
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
GSMADMIN_INT EXPIRED & LOCKED SYSAUX TEMP DEFAULT
ERNAL
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
APPQOSSYS EXPIRED & LOCKED SYSAUX TEMP DEFAULT
XDB EXPIRED & LOCKED SYSAUX TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
XS$NULL EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_OCM EXPIRED & LOCKED USERS TEMP DEFAULT
GSMCATUSER EXPIRED & LOCKED USERS TEMP DEFAULT
SCOTT OPEN DMTS TEMP DEFAULT
18 rows selected.
SQL> select length(max(username)) from dba_users;
LENGTH(MAX(USERNAME))
---------------------
7
SQL> select max(length(username)) from dba_users;
MAX(LENGTH(USERNAME))
---------------------
17
R_GROUP
Y
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N NO
R_GROUP
14-SEP-15 03.10 N
.34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ---------
PROFILE INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM
--------------- --------------- --------------- ------------ - -------- - ---
LAST_LOGIN O
--------------- -
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME N PASSWORD N YES
R_GROUP
Y
SYSBACKUP ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
18 rows selected.
SQL> col DEFAULT_TABLESPACE for a15
SQL> /
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TABLESPACE CREATED PROFILE
--------------- ------------------------------ --------- ---------------
INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- --------------- ------------ - -------- - --- --------------- -
SYS ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
SYS_GROUP 10G 11G N PASSWORD N YES Y
SYSTEM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
SYS_GROUP 10G 11G N PASSWORD N YES 14-SEP-15 03.15 Y
.40.000000000 P
M +05:30
GSMADMIN_INTERN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
AL KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
AUDSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
GSMUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
XS$NULL ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SYSKM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
APPQOSSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N NO 14-SEP-15 03.10 N
R_GROUP .34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME N PASSWORD N YES Y
R_GROUP
SYSBACKUP ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TABLESPACE CREATED PROFILE
--------------- ------------------------------ --------- ---------------
INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- --------------- ------------ - -------- - --- --------------- -
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
18 rows selected.
SQL> col TEMPORARY_TABLESPACE for a12
SQL> /
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TA CREATED PROFILE INITIAL_RSRC_CO
--------------- ------------ --------- --------------- ---------------
EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- ------------ - -------- - --- --------------- -
SYS ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT SYS_GROUP
10G 11G N PASSWORD N YES Y
SYSTEM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT SYS_GROUP
10G 11G N PASSWORD N YES 14-SEP-15 03.15 Y
.40.000000000 P
M +05:30
GSMADMIN_INTERN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
AL KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
AUDSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
GSMUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
XS$NULL ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SYSKM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
APPQOSSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N NO 14-SEP-15 03.10 N
.34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
N PASSWORD N YES Y
SYSBACKUP ########## OPEN 05-MAR-16
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TA CREATED PROFILE INITIAL_RSRC_CO
--------------- ------------ --------- --------------- ---------------
EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- ------------ - -------- - --- --------------- -
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
18 rows selected.
SQL> select ^C
SQL> select USERNAME,ACCOUNT_STATUS,
2 DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,
3 PROFILE FROM DBA_USERS;
USERNAME ACCOUNT_STATU DEFAULT_TABLESP TEMPORARY_TA PROFILE
--------------- ------------- --------------- ------------ ---------------
SCOTT OPEN DMTS TEMP DEFAULT
AUDSYS EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
XS$NULL EXPIRED & LOC USERS TEMP DEFAULT
KED
ORACLE_OCM EXPIRED & LOC USERS TEMP DEFAULT
KED
DIP EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMCATUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMADMIN_INTERN EXPIRED & LOC SYSAUX TEMP DEFAULT
AL KED
APPQOSSYS EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
XDB EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
DBSNMP EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOC SYSTEM TEMP DEFAULT
KED
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
18 rows selected.
SQL> SAVE dba_users.sql
Created file dba_users.sql
SQL> col USERNAME for a8
SQL> /
USERNAME ACCOUNT_STATU DEFAULT_TABLESP TEMPORARY_TA PROFILE
-------- ------------- --------------- ------------ ---------------
SCOTT OPEN DMTS TEMP DEFAULT
AUDSYS EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
XS$NULL EXPIRED & LOC USERS TEMP DEFAULT
KED
ORACLE_O EXPIRED & LOC USERS TEMP DEFAULT
CM KED
DIP EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMCATUS EXPIRED & LOC USERS TEMP DEFAULT
ER KED
GSMADMIN EXPIRED & LOC SYSAUX TEMP DEFAULT
_INTERNA KED
L
APPQOSSY EXPIRED & LOC SYSAUX TEMP DEFAULT
S KED
XDB EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
DBSNMP EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
ANONYMOU LOCKED SYSAUX TEMP DEFAULT
S
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOC SYSTEM TEMP DEFAULT
KED
SYSBACKU OPEN SYSTEM TEMP DEFAULT
P
18 rows selected.
SQL> col ACCOUNT_STATUS for a17
SQL> /
USERNAME ACCOUNT_STATUS DEFAULT_TABLESP TEMPORARY_TA PROFILE
-------- ----------------- --------------- ------------ ---------------
SYSBACKU OPEN SYSTEM TEMP DEFAULT
P
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
ANONYMOU LOCKED SYSAUX TEMP DEFAULT
S
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
XDB EXPIRED & LOCKED SYSAUX TEMP DEFAULT
APPQOSSY EXPIRED & LOCKED SYSAUX TEMP DEFAULT
S
GSMADMIN EXPIRED & LOCKED SYSAUX TEMP DEFAULT
_INTERNA
L
GSMCATUS EXPIRED & LOCKED USERS TEMP DEFAULT
ER
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_O EXPIRED & LOCKED USERS TEMP DEFAULT
CM
XS$NULL EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
SCOTT OPEN DMTS TEMP DEFAULT
18 rows selected.
SQL> col USERNAME for a12
SQL> /
USERNAME ACCOUNT_STATUS DEFAULT_TABLESP TEMPORARY_TA PROFILE
------------ ----------------- --------------- ------------ ---------------
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
GSMADMIN_INT EXPIRED & LOCKED SYSAUX TEMP DEFAULT
ERNAL
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
APPQOSSYS EXPIRED & LOCKED SYSAUX TEMP DEFAULT
XDB EXPIRED & LOCKED SYSAUX TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
XS$NULL EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_OCM EXPIRED & LOCKED USERS TEMP DEFAULT
GSMCATUSER EXPIRED & LOCKED USERS TEMP DEFAULT
SCOTT OPEN DMTS TEMP DEFAULT
18 rows selected.
SQL> select length(max(username)) from dba_users;
LENGTH(MAX(USERNAME))
---------------------
7
SQL> select max(length(username)) from dba_users;
MAX(LENGTH(USERNAME))
---------------------
17
SQL> desc dba_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(128)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(12)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED
SQL> create user u1 identified by u1;
User created.
SQL> create user u2 identified by u2 default tablespace sales;
User created.
SQL> create user u3 identified by u3 default tablespace hr temporary tablespace temp3;
create user u3 identified by u3 default tablespace hr temporary tablespace temp3
*
ERROR at line 1:
ORA-00959: tablespace 'HR' does not exist
SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle12c/oradata/wiprodb/temp2.dbf' size 30m;
Tablespace created.
SQL> create temporary tablespace temp3 tempfile '/u01/app/oracle12c/oradata/wiprodb/temp3.dbf' size 30m;
Tablespace created.
SQL> create tablespace hr datafile '/u01/app/oracle12c/oradata/wiprodb/hr.dbf' size 30m;
Tablespace created.
SQL> create user u3 identified by u3 default tablespace hr temporary tablespace temp3;
User created.
SQL> create user u4 identified by u4 temporary tablespace temp3;
User created.
SQL> @dba_usres;
SP2-0310: unable to open file "dba_usres.sql"
SQL> @dba_users;
USERNAME ACCOUNT_STATUS DEFAULT_TABLESP TEMPORARY_TA PROFILE
------------ ----------------- --------------- ------------ ---------------
SCOTT OPEN DMTS TEMP DEFAULT
U2 OPEN SALES TEMP DEFAULT
U1 OPEN USERS TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
XS$NULL EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_OCM EXPIRED & LOCKED USERS TEMP DEFAULT
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
GSMCATUSER EXPIRED & LOCKED USERS TEMP DEFAULT
GSMADMIN_INT EXPIRED & LOCKED SYSAUX TEMP DEFAULT
ERNAL
APPQOSSYS EXPIRED & LOCKED SYSAUX TEMP DEFAULT
XDB EXPIRED & LOCKED SYSAUX TEMP DEFAULT
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
U3 OPEN HR TEMP3 DEFAULT
U4 OPEN USERS TEMP3 DEFAULT
22 rows selected.
SQL> 1
1* select USERNAME,ACCOUNT_STATUS,
SQL> l
1 select USERNAME,ACCOUNT_STATUS,
2 DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,
3* PROFILE FROM DBA_USERS
SCOTT OPEN DMTS TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
USERNAME ACCOUNT_STATUS DEFAULT_TABL TEMPORARY_TABLE PROFILE
----------------- --------------- ------------ --------------- ---------------
SYSTEM OPEN SYSTEM TEMP DEFAULT
U1 OPEN USERS TEMP DEFAULT
U2 OPEN SALES TEMP DEFAULT
U3 OPEN HR TEMP3 DEFAULT
U4 OPEN USERS TEMP3 DEFAULT
XDB EXPIRED & LOCKE SYSAUX TEMP DEFAULT
D
XS$NULL EXPIRED & LOCKE USERS TEMP DEFAULT
D
-----------------------------------------
[oracle12c@uit144 Desktop]$ cd
[oracle12c@uit144 ~]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 14 15:50:54 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: u1
Enter password:
ERROR:
ORA-01045: user U1 lacks CREATE SESSION privilege; logon denied
SQL> grant create session to u1;
Grant succeeded.
SQL> create table t1 (a number);
create table t1 (a number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> grant create table to u1;
Grant succeeded.
---------------------------------------------
SQL> desc dba_tab_privs;
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(128)
OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
GRANTOR VARCHAR2(128)
PRIVILEGE VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
COMMON VARCHAR2(3)
TYPE VARCHAR2(24)
SQL> select distinct privilege from dba_tab_privs;
PRIVILEGE
----------------------------------------
FLASHBACK
EXECUTE
ON COMMIT REFRESH
ALTER
DEQUEUE
INHERIT PRIVILEGES
DEBUG
UPDATE
DELETE
QUERY REWRITE
SELECT
PRIVILEGE
----------------------------------------
USE
READ
INSERT
INDEX
WRITE
REFERENCES
17 rows selected.
SQL>
----------------------------------------
select * from dba_tab_privs;
7658 rows selected.
--------------------
grant create session,create table,
create synonym,create view,
create any index to u2
========================
SQL> insert into t2 values (2);
insert into t2 values (2)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SALES'
SQL> alter user u2 quota 5m on sales;
User altered.
select * from dba_ts_quotas;
SQL> col TABLESPACE_NAME for a14
SQL>
SQL> /
TABLESPACE_NAM USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
-------------- ----------------- ---------- ---------- ---------- ---------- ---
DMTS SCOTT 0 -1 0 -1 NO
SYSAUX GSMADMIN_INTERNAL 983040 104857600 120 12800 NO
SALES U2 131072 5242880 16 640 NO
SYSAUX AUDSYS 0 -1 0 -1 NO
SYSAUX APPQOSSYS 0 -1 0 -1 NO
SQL>
SQL> show user
USER is "U2"
SQL> conn u3/u3
ERROR:
ORA-01045: user U3 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u2/u2
Connected.
SQL> grant create session to u3;
grant create session to u3
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn u1/u1
Connected.
SQL> grant create session to u2 with admin option;
Grant succeeded.
SQL> conn u2/u2
Connected.
SQL> grant create session to u3;
Grant succeeded.
SQL> conn u3/u3
Connected.
SQL> grant create session to u4;
grant create session to u4
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn u2/u2
Connected.
SQL> grant create session to u10;
grant create session to u10
*
ERROR at line 1:
ORA-01917: user or role 'U10' does not exist
SQL> show user
USER is "U2"
SQL> revoke create session from u1;
Revoke succeeded.
SQL> revoke create session from sys;
Revoke succeeded.
SQL> grant create session to sys;
Grant succeeded.
SQL> conn u1/u1
Connected.
SQL> select * from u2.t2;
select * from u2.t2
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
SQL> show
SQL> show user
USER is "U1"
SQL> conn / as sysdba
Connected.
SQL> conn u2/u2
Connected.
SQL> grant select on t2 to u1;
Grant succeeded.
SQL> conn u1/u1
Connected.
SQL> select * from u2.t2;
A
----------
2
2
2
2
2
2
2
2
2
2
10 rows selected.
SQL> select * from t1;
no rows selected
SQL> insert into t1 values(1);
insert into t1 values(1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
SQL> conn / as sysdba
Connected.
SQL> alter user u1 quota 5m on users;
User altered.
SQL> insert into t1 values(1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from t1;
A
----------
1
1
1
1
1
1
1
1
1
1
1
A
----------
1
1
13 rows selected.
SQL> conn u2/u2
Connected.
SQL> select * from u1.t1;
no rows selected
SQL> conn u1/u1
Connected.
SQL> select * from t1;
no rows selected
SQL> insert into t1 values(1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit
2 ;
Commit complete.
SQL> conn u2/u2
Connected.
SQL> select * from u1.t1;
A
----------
1
1
1
1
1
SQL> revoke select on t2 to u1;
revoke select on t2 to u1
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> revoke select on t2 to u1 with grant option;
revoke select on t2 to u1 with grant option
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> revoke select on t2 from u1;
Revoke succeeded.
SQL>
block_size,
status,
contents,
logging,
extent_management,
plugged_in,
bigfile
from dba_tablespaces
----------------
14-sep-15
R_GROUP
Y
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N NO
R_GROUP
14-SEP-15 03.10 N
.34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ---------
PROFILE INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM
--------------- --------------- --------------- ------------ - -------- - ---
LAST_LOGIN O
--------------- -
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME N PASSWORD N YES
R_GROUP
Y
SYSBACKUP ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
18 rows selected.
SQL> col DEFAULT_TABLESPACE for a15
SQL> /
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TABLESPACE CREATED PROFILE
--------------- ------------------------------ --------- ---------------
INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- --------------- ------------ - -------- - --- --------------- -
SYS ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
SYS_GROUP 10G 11G N PASSWORD N YES Y
SYSTEM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
SYS_GROUP 10G 11G N PASSWORD N YES 14-SEP-15 03.15 Y
.40.000000000 P
M +05:30
GSMADMIN_INTERN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
AL KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
AUDSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
GSMUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
XS$NULL ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SYSKM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
APPQOSSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N NO 14-SEP-15 03.10 N
R_GROUP .34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME N PASSWORD N YES Y
R_GROUP
SYSBACKUP ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TABLESPACE CREATED PROFILE
--------------- ------------------------------ --------- ---------------
INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- --------------- ------------ - -------- - --- --------------- -
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
18 rows selected.
SQL> col TEMPORARY_TABLESPACE for a12
SQL> /
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TA CREATED PROFILE INITIAL_RSRC_CO
--------------- ------------ --------- --------------- ---------------
EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- ------------ - -------- - --- --------------- -
SYS ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT SYS_GROUP
10G 11G N PASSWORD N YES Y
SYSTEM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT SYS_GROUP
10G 11G N PASSWORD N YES 14-SEP-15 03.15 Y
.40.000000000 P
M +05:30
GSMADMIN_INTERN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
AL KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
AUDSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
GSMUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
XS$NULL ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SYSKM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
APPQOSSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N NO 14-SEP-15 03.10 N
.34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
N PASSWORD N YES Y
SYSBACKUP ########## OPEN 05-MAR-16
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TA CREATED PROFILE INITIAL_RSRC_CO
--------------- ------------ --------- --------------- ---------------
EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- ------------ - -------- - --- --------------- -
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
18 rows selected.
SQL> select ^C
SQL> select USERNAME,ACCOUNT_STATUS,
2 DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,
3 PROFILE FROM DBA_USERS;
USERNAME ACCOUNT_STATU DEFAULT_TABLESP TEMPORARY_TA PROFILE
--------------- ------------- --------------- ------------ ---------------
SCOTT OPEN DMTS TEMP DEFAULT
AUDSYS EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
XS$NULL EXPIRED & LOC USERS TEMP DEFAULT
KED
ORACLE_OCM EXPIRED & LOC USERS TEMP DEFAULT
KED
DIP EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMCATUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMADMIN_INTERN EXPIRED & LOC SYSAUX TEMP DEFAULT
AL KED
APPQOSSYS EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
XDB EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
DBSNMP EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOC SYSTEM TEMP DEFAULT
KED
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
18 rows selected.
SQL> SAVE dba_users.sql
Created file dba_users.sql
SQL> col USERNAME for a8
SQL> /
USERNAME ACCOUNT_STATU DEFAULT_TABLESP TEMPORARY_TA PROFILE
-------- ------------- --------------- ------------ ---------------
SCOTT OPEN DMTS TEMP DEFAULT
AUDSYS EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
XS$NULL EXPIRED & LOC USERS TEMP DEFAULT
KED
ORACLE_O EXPIRED & LOC USERS TEMP DEFAULT
CM KED
DIP EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMCATUS EXPIRED & LOC USERS TEMP DEFAULT
ER KED
GSMADMIN EXPIRED & LOC SYSAUX TEMP DEFAULT
_INTERNA KED
L
APPQOSSY EXPIRED & LOC SYSAUX TEMP DEFAULT
S KED
XDB EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
DBSNMP EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
ANONYMOU LOCKED SYSAUX TEMP DEFAULT
S
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOC SYSTEM TEMP DEFAULT
KED
SYSBACKU OPEN SYSTEM TEMP DEFAULT
P
18 rows selected.
SQL> col ACCOUNT_STATUS for a17
SQL> /
USERNAME ACCOUNT_STATUS DEFAULT_TABLESP TEMPORARY_TA PROFILE
-------- ----------------- --------------- ------------ ---------------
SYSBACKU OPEN SYSTEM TEMP DEFAULT
P
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
ANONYMOU LOCKED SYSAUX TEMP DEFAULT
S
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
XDB EXPIRED & LOCKED SYSAUX TEMP DEFAULT
APPQOSSY EXPIRED & LOCKED SYSAUX TEMP DEFAULT
S
GSMADMIN EXPIRED & LOCKED SYSAUX TEMP DEFAULT
_INTERNA
L
GSMCATUS EXPIRED & LOCKED USERS TEMP DEFAULT
ER
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_O EXPIRED & LOCKED USERS TEMP DEFAULT
CM
XS$NULL EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
SCOTT OPEN DMTS TEMP DEFAULT
18 rows selected.
SQL> col USERNAME for a12
SQL> /
USERNAME ACCOUNT_STATUS DEFAULT_TABLESP TEMPORARY_TA PROFILE
------------ ----------------- --------------- ------------ ---------------
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
GSMADMIN_INT EXPIRED & LOCKED SYSAUX TEMP DEFAULT
ERNAL
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
APPQOSSYS EXPIRED & LOCKED SYSAUX TEMP DEFAULT
XDB EXPIRED & LOCKED SYSAUX TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
XS$NULL EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_OCM EXPIRED & LOCKED USERS TEMP DEFAULT
GSMCATUSER EXPIRED & LOCKED USERS TEMP DEFAULT
SCOTT OPEN DMTS TEMP DEFAULT
18 rows selected.
SQL> select length(max(username)) from dba_users;
LENGTH(MAX(USERNAME))
---------------------
7
SQL> select max(length(username)) from dba_users;
MAX(LENGTH(USERNAME))
---------------------
17
R_GROUP
Y
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N NO
R_GROUP
14-SEP-15 03.10 N
.34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ---------
PROFILE INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM
--------------- --------------- --------------- ------------ - -------- - ---
LAST_LOGIN O
--------------- -
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME N PASSWORD N YES
R_GROUP
Y
SYSBACKUP ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15
DEFAULT DEFAULT_CONSUME 10G 11G N PASSWORD N YES
R_GROUP
Y
18 rows selected.
SQL> col DEFAULT_TABLESPACE for a15
SQL> /
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TABLESPACE CREATED PROFILE
--------------- ------------------------------ --------- ---------------
INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- --------------- ------------ - -------- - --- --------------- -
SYS ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
SYS_GROUP 10G 11G N PASSWORD N YES Y
SYSTEM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
SYS_GROUP 10G 11G N PASSWORD N YES 14-SEP-15 03.15 Y
.40.000000000 P
M +05:30
GSMADMIN_INTERN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
AL KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
AUDSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
GSMUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
XS$NULL ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SYSKM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
APPQOSSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N NO 14-SEP-15 03.10 N
R_GROUP .34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME N PASSWORD N YES Y
R_GROUP
SYSBACKUP ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TABLESPACE CREATED PROFILE
--------------- ------------------------------ --------- ---------------
INITIAL_RSRC_CO EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- --------------- ------------ - -------- - --- --------------- -
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT
DEFAULT_CONSUME 10G 11G N PASSWORD N YES Y
R_GROUP
18 rows selected.
SQL> col TEMPORARY_TABLESPACE for a12
SQL> /
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TA CREATED PROFILE INITIAL_RSRC_CO
--------------- ------------ --------- --------------- ---------------
EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- ------------ - -------- - --- --------------- -
SYS ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT SYS_GROUP
10G 11G N PASSWORD N YES Y
SYSTEM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT SYS_GROUP
10G 11G N PASSWORD N YES 14-SEP-15 03.15 Y
.40.000000000 P
M +05:30
GSMADMIN_INTERN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
AL KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
AUDSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
GSMUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
XS$NULL ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SYSKM ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
APPQOSSYS ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
ORACLE_OCM ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
XDB ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SCOTT ########## OPEN 09-MAR-16
DMTS TEMP 11-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N NO 14-SEP-15 03.10 N
.34.000000000 P
M +05:30
DBSNMP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
SYSDG ########## OPEN 05-MAR-16
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
DIP ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
OUTLN ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
ANONYMOUS ########## LOCKED 07-SEP-15 05-MAR-16
SYSAUX TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
N PASSWORD N YES Y
SYSBACKUP ########## OPEN 05-MAR-16
USERNAME USER_ID PASSWORD ACCOUNT_STATU LOCK_DATE EXPIRY_DA
--------------- ---------- --------------- ------------- --------- ---------
DEFAULT_TABLESP TEMPORARY_TA CREATED PROFILE INITIAL_RSRC_CO
--------------- ------------ --------- --------------- ---------------
EXTERNAL_NAME PASSWORD_VER E AUTHENTI P COM LAST_LOGIN O
--------------- ------------ - -------- - --- --------------- -
SYSTEM TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
GSMCATUSER ########## EXPIRED & LOC 07-SEP-15 07-SEP-15
KED
USERS TEMP 07-SEP-15 DEFAULT DEFAULT_CONSUME
R_GROUP
10G 11G N PASSWORD N YES Y
18 rows selected.
SQL> select ^C
SQL> select USERNAME,ACCOUNT_STATUS,
2 DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,
3 PROFILE FROM DBA_USERS;
USERNAME ACCOUNT_STATU DEFAULT_TABLESP TEMPORARY_TA PROFILE
--------------- ------------- --------------- ------------ ---------------
SCOTT OPEN DMTS TEMP DEFAULT
AUDSYS EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
XS$NULL EXPIRED & LOC USERS TEMP DEFAULT
KED
ORACLE_OCM EXPIRED & LOC USERS TEMP DEFAULT
KED
DIP EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMCATUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMADMIN_INTERN EXPIRED & LOC SYSAUX TEMP DEFAULT
AL KED
APPQOSSYS EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
XDB EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
DBSNMP EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOC SYSTEM TEMP DEFAULT
KED
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
18 rows selected.
SQL> SAVE dba_users.sql
Created file dba_users.sql
SQL> col USERNAME for a8
SQL> /
USERNAME ACCOUNT_STATU DEFAULT_TABLESP TEMPORARY_TA PROFILE
-------- ------------- --------------- ------------ ---------------
SCOTT OPEN DMTS TEMP DEFAULT
AUDSYS EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMUSER EXPIRED & LOC USERS TEMP DEFAULT
KED
XS$NULL EXPIRED & LOC USERS TEMP DEFAULT
KED
ORACLE_O EXPIRED & LOC USERS TEMP DEFAULT
CM KED
DIP EXPIRED & LOC USERS TEMP DEFAULT
KED
GSMCATUS EXPIRED & LOC USERS TEMP DEFAULT
ER KED
GSMADMIN EXPIRED & LOC SYSAUX TEMP DEFAULT
_INTERNA KED
L
APPQOSSY EXPIRED & LOC SYSAUX TEMP DEFAULT
S KED
XDB EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
DBSNMP EXPIRED & LOC SYSAUX TEMP DEFAULT
KED
ANONYMOU LOCKED SYSAUX TEMP DEFAULT
S
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOC SYSTEM TEMP DEFAULT
KED
SYSBACKU OPEN SYSTEM TEMP DEFAULT
P
18 rows selected.
SQL> col ACCOUNT_STATUS for a17
SQL> /
USERNAME ACCOUNT_STATUS DEFAULT_TABLESP TEMPORARY_TA PROFILE
-------- ----------------- --------------- ------------ ---------------
SYSBACKU OPEN SYSTEM TEMP DEFAULT
P
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
ANONYMOU LOCKED SYSAUX TEMP DEFAULT
S
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
XDB EXPIRED & LOCKED SYSAUX TEMP DEFAULT
APPQOSSY EXPIRED & LOCKED SYSAUX TEMP DEFAULT
S
GSMADMIN EXPIRED & LOCKED SYSAUX TEMP DEFAULT
_INTERNA
L
GSMCATUS EXPIRED & LOCKED USERS TEMP DEFAULT
ER
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_O EXPIRED & LOCKED USERS TEMP DEFAULT
CM
XS$NULL EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
SCOTT OPEN DMTS TEMP DEFAULT
18 rows selected.
SQL> col USERNAME for a12
SQL> /
USERNAME ACCOUNT_STATUS DEFAULT_TABLESP TEMPORARY_TA PROFILE
------------ ----------------- --------------- ------------ ---------------
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
GSMADMIN_INT EXPIRED & LOCKED SYSAUX TEMP DEFAULT
ERNAL
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
APPQOSSYS EXPIRED & LOCKED SYSAUX TEMP DEFAULT
XDB EXPIRED & LOCKED SYSAUX TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
XS$NULL EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_OCM EXPIRED & LOCKED USERS TEMP DEFAULT
GSMCATUSER EXPIRED & LOCKED USERS TEMP DEFAULT
SCOTT OPEN DMTS TEMP DEFAULT
18 rows selected.
SQL> select length(max(username)) from dba_users;
LENGTH(MAX(USERNAME))
---------------------
7
SQL> select max(length(username)) from dba_users;
MAX(LENGTH(USERNAME))
---------------------
17
SQL> desc dba_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(128)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(12)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED
SQL> create user u1 identified by u1;
User created.
SQL> create user u2 identified by u2 default tablespace sales;
User created.
SQL> create user u3 identified by u3 default tablespace hr temporary tablespace temp3;
create user u3 identified by u3 default tablespace hr temporary tablespace temp3
*
ERROR at line 1:
ORA-00959: tablespace 'HR' does not exist
SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle12c/oradata/wiprodb/temp2.dbf' size 30m;
Tablespace created.
SQL> create temporary tablespace temp3 tempfile '/u01/app/oracle12c/oradata/wiprodb/temp3.dbf' size 30m;
Tablespace created.
SQL> create tablespace hr datafile '/u01/app/oracle12c/oradata/wiprodb/hr.dbf' size 30m;
Tablespace created.
SQL> create user u3 identified by u3 default tablespace hr temporary tablespace temp3;
User created.
SQL> create user u4 identified by u4 temporary tablespace temp3;
User created.
SQL> @dba_usres;
SP2-0310: unable to open file "dba_usres.sql"
SQL> @dba_users;
USERNAME ACCOUNT_STATUS DEFAULT_TABLESP TEMPORARY_TA PROFILE
------------ ----------------- --------------- ------------ ---------------
SCOTT OPEN DMTS TEMP DEFAULT
U2 OPEN SALES TEMP DEFAULT
U1 OPEN USERS TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
XS$NULL EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_OCM EXPIRED & LOCKED USERS TEMP DEFAULT
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
GSMCATUSER EXPIRED & LOCKED USERS TEMP DEFAULT
GSMADMIN_INT EXPIRED & LOCKED SYSAUX TEMP DEFAULT
ERNAL
APPQOSSYS EXPIRED & LOCKED SYSAUX TEMP DEFAULT
XDB EXPIRED & LOCKED SYSAUX TEMP DEFAULT
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
U3 OPEN HR TEMP3 DEFAULT
U4 OPEN USERS TEMP3 DEFAULT
22 rows selected.
SQL> 1
1* select USERNAME,ACCOUNT_STATUS,
SQL> l
1 select USERNAME,ACCOUNT_STATUS,
2 DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,
3* PROFILE FROM DBA_USERS
SCOTT OPEN DMTS TEMP DEFAULT
SYS OPEN SYSTEM TEMP DEFAULT
SYSBACKUP OPEN SYSTEM TEMP DEFAULT
SYSDG OPEN SYSTEM TEMP DEFAULT
SYSKM OPEN SYSTEM TEMP DEFAULT
USERNAME ACCOUNT_STATUS DEFAULT_TABL TEMPORARY_TABLE PROFILE
----------------- --------------- ------------ --------------- ---------------
SYSTEM OPEN SYSTEM TEMP DEFAULT
U1 OPEN USERS TEMP DEFAULT
U2 OPEN SALES TEMP DEFAULT
U3 OPEN HR TEMP3 DEFAULT
U4 OPEN USERS TEMP3 DEFAULT
XDB EXPIRED & LOCKE SYSAUX TEMP DEFAULT
D
XS$NULL EXPIRED & LOCKE USERS TEMP DEFAULT
D
-----------------------------------------
[oracle12c@uit144 Desktop]$ cd
[oracle12c@uit144 ~]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 14 15:50:54 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: u1
Enter password:
ERROR:
ORA-01045: user U1 lacks CREATE SESSION privilege; logon denied
SQL> grant create session to u1;
Grant succeeded.
SQL> create table t1 (a number);
create table t1 (a number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> grant create table to u1;
Grant succeeded.
---------------------------------------------
SQL> desc dba_tab_privs;
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(128)
OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
GRANTOR VARCHAR2(128)
PRIVILEGE VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
COMMON VARCHAR2(3)
TYPE VARCHAR2(24)
SQL> select distinct privilege from dba_tab_privs;
PRIVILEGE
----------------------------------------
FLASHBACK
EXECUTE
ON COMMIT REFRESH
ALTER
DEQUEUE
INHERIT PRIVILEGES
DEBUG
UPDATE
DELETE
QUERY REWRITE
SELECT
PRIVILEGE
----------------------------------------
USE
READ
INSERT
INDEX
WRITE
REFERENCES
17 rows selected.
SQL>
----------------------------------------
select * from dba_tab_privs;
7658 rows selected.
--------------------
grant create session,create table,
create synonym,create view,
create any index to u2
========================
SQL> insert into t2 values (2);
insert into t2 values (2)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SALES'
SQL> alter user u2 quota 5m on sales;
User altered.
select * from dba_ts_quotas;
SQL> col TABLESPACE_NAME for a14
SQL>
SQL> /
TABLESPACE_NAM USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
-------------- ----------------- ---------- ---------- ---------- ---------- ---
DMTS SCOTT 0 -1 0 -1 NO
SYSAUX GSMADMIN_INTERNAL 983040 104857600 120 12800 NO
SALES U2 131072 5242880 16 640 NO
SYSAUX AUDSYS 0 -1 0 -1 NO
SYSAUX APPQOSSYS 0 -1 0 -1 NO
SQL>
SQL> show user
USER is "U2"
SQL> conn u3/u3
ERROR:
ORA-01045: user U3 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u2/u2
Connected.
SQL> grant create session to u3;
grant create session to u3
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn u1/u1
Connected.
SQL> grant create session to u2 with admin option;
Grant succeeded.
SQL> conn u2/u2
Connected.
SQL> grant create session to u3;
Grant succeeded.
SQL> conn u3/u3
Connected.
SQL> grant create session to u4;
grant create session to u4
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn u2/u2
Connected.
SQL> grant create session to u10;
grant create session to u10
*
ERROR at line 1:
ORA-01917: user or role 'U10' does not exist
SQL> show user
USER is "U2"
SQL> revoke create session from u1;
Revoke succeeded.
SQL> revoke create session from sys;
Revoke succeeded.
SQL> grant create session to sys;
Grant succeeded.
SQL> conn u1/u1
Connected.
SQL> select * from u2.t2;
select * from u2.t2
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
SQL> show
SQL> show user
USER is "U1"
SQL> conn / as sysdba
Connected.
SQL> conn u2/u2
Connected.
SQL> grant select on t2 to u1;
Grant succeeded.
SQL> conn u1/u1
Connected.
SQL> select * from u2.t2;
A
----------
2
2
2
2
2
2
2
2
2
2
10 rows selected.
SQL> select * from t1;
no rows selected
SQL> insert into t1 values(1);
insert into t1 values(1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
SQL> conn / as sysdba
Connected.
SQL> alter user u1 quota 5m on users;
User altered.
SQL> insert into t1 values(1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from t1;
A
----------
1
1
1
1
1
1
1
1
1
1
1
A
----------
1
1
13 rows selected.
SQL> conn u2/u2
Connected.
SQL> select * from u1.t1;
no rows selected
SQL> conn u1/u1
Connected.
SQL> select * from t1;
no rows selected
SQL> insert into t1 values(1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit
2 ;
Commit complete.
SQL> conn u2/u2
Connected.
SQL> select * from u1.t1;
A
----------
1
1
1
1
1
SQL> revoke select on t2 to u1;
revoke select on t2 to u1
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> revoke select on t2 to u1 with grant option;
revoke select on t2 to u1 with grant option
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> revoke select on t2 from u1;
Revoke succeeded.
SQL>
No comments:
Post a Comment