-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/access.sql
-- Author : Tim Hall
-- Description : Lists all objects being accessed in the schema.
-- Call Syntax : @access (schema-name or all) (object-name or all)
-- Requirements : Access to the v$views.
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET LINESIZE 255
SET VERIFY OFF
COLUMN object FORMAT A30
SELECT a.object,
a.type,
a.sid,
b.serial#,
b.username,
b.osuser,
b.program
FROM v$access a,
v$session b
WHERE a.sid = b.sid
AND a.owner = DECODE(UPPER('&1'), 'ALL', a.object, UPPER('&1'))
AND a.object = DECODE(UPPER('&2'), 'ALL', a.object, UPPER('&2'))
ORDER BY a.object;
========================================================================================
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/testdb/temp2.dbf' size 30m;
Tablespace created.
SQL> create temporary tablespace temp3 tempfile '/u01/app/oracle12c/oradata/testdb/temp3.dbf' size 30m;
Tablespace created.
SQL> create tablespace hr datafile '/u01/app/oracle12c/oradata/testdb/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>
SQL> revoke select on t2 from u1;
Revoke succeeded.
SQL>
------------------------------------------------------
15-sep-2015
------------------\=====
========================\
[oracle12c@uit144 Desktop]$ cd
[oracle12c@uit144 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Sep 16 14:51:04 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> desc v$log;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
CON_ID NUMBER
SQL> select GROUP#,MEMBERS,THREAD#,STATUS,SEQUENCE#,BYTES/1024/1024
2 from v$log;
GROUP# MEMBERS THREAD# STATUS SEQUENCE# BYTES/1024/1024
---------- ---------- ---------- ---------------- ---------- ---------------
1 1 1 CURRENT 339 4
2 1 1 INACTIVE 338 4
SQL> save log.sql
Created file log.sql
SQL> cl scr
SQL> desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
CON_ID NUMBER
SQL> select GROUP#,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE,CON_ID
2 from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_ CON_ID
--- ----------
1 ONLINE
/u01/app/oracle12c/oradata/testdb/redo1a.log
NO 0
2 ONLINE
/u01/app/oracle12c/oradata/testdb/redo2a.log
NO 0
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_ CON_ID
--- ----------
SQL> save logfile.sql
Created file logfile.sql
SQL> col MEMBER for a15
SQL> /
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
1 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1a.log
2 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2a.log
---------------
QL> alter database add logfile group 3 ('/u01/app/oracle12c/oradata/testdb/redo3a.log') size 20m;
Database altered.
SQL> alter database add logfile group 4 ('/u01/app/oracle12c/oradata/testdb/redo4.log') size 10m;
Database altered.
---------------
alter database add logfile member ('/u01/app/oracle12c/oradata/testdb/redo1b.log') to group 1;
alter database add logfile member (/u01/app/oracle12c/oradata/testdb/redo1b.log) to group 1
*
ERROR at line 1:
ORA-02236: invalid file name
SQL> alter database add logfile member 'u01/app/oracle12c/oradata/testdb/redo1b.log' to group 1;
alter database add logfile member 'u01/app/oracle12c/oradata/testdb/redo1b.log' to group 1
*
ERROR at line 1:
ORA-00301: error in adding log file
'u01/app/oracle12c/oradata/testdb/redo1b.log' - file cannot be created
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
SQL> oerr ora 27040
SP2-0734: unknown command beginning "oerr ora 2..." - rest of line ignored.
SQL> !oerr ora 27040
27040, 00000, "file create error, unable to create file"
// *Cause: create system call returned an error, unable to create file
// *Action: verify filename, and permissions
---------------
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo1b.log' to group 1;
Database altered.
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo2b.log' to group 2;
Database altered.
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo3b.log' to group 3;
Database altered.
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo4b.log' to group 4;
Database altered.
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo1;;
alter database add logfile member '/u01/app/oracle12c/oradata/testdb/;
*
ERROR at line 1:
ORA-01756: quoted string not properly terminated
SQL> @logfile
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
1 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1a.log
2 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2a.log
3 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3a.log
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
4 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo4.log
1 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1b.log
2 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
rodb/redo2b.log
3 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3b.log
4 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo4b.log
-------------
alter database add logfile member('/u01/app/oracle12c/oradata/testdb/redo1c.log','/u01/app/oracle12c/oradata/testdb/redo1d.log') to group 1;
SQL> alter database add logfile member('/u01/app/oracle12c/oradata/testdb/redo2c.log','/u01/app/oracle12c/oradata/testdb/redo2d.log') to group 2;
alter database add logfile member('/u01/app/oracle12c/oradata/testdb/redo2c.log','/u01/app/oracle12c/oradata/testdb/redo2d.log') to group 2
*
ERROR at line 1:
ORA-02236: invalid file name
SQL> alter database add logfile member'/u01/app/oracle12c/oradata/testdb/redo2c.log','/u01/app/oracle12c/oradata/testdb/redo2d.log' to group 2;
Database altered.
---------------------------------
alter database add logfile member'/u01/app/oracle12c/oradata/testdb/redo2c.log','/u01/app/oracle12c/oradata/testdb/redo2d.log' to group 2
ERROR at line 1:
ORA-00357: too many members specified for log file, the maximum is 5
---------------------------------
[oracle12c@uit144 testdb]$ ls
c2.ctl redo1a.log redo2c.log redo4.log temp3.dbf
control.ctl redo1b.log redo2d.log sales01.dbf temp.dbf
dmts.dbf redo1c.log redo2e.log sysaux.dbf undo1.dbf
hr.dbf redo1d.log redo3a.log system.dbf undo2.dbf
production01.dbf redo2a.log redo3b.log temp1.dbf users.dbf
production.dbf redo2b.log redo4b.log temp2.dbf
[oracle12c@uit144 testdb]$
---------------------------------
alter database add logfile member '/u01/app/oracle12c/oradata/testdb/
redo3c.log','/u01/app/oracle12c/oradata/testdb/redo3c.log'
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo3c.log','/u01/app/oracle12c/oradata/testdb/redo3d.log','/u01/app/oracle12c/oradata/testdb/redo3e.log' to group 3;
Database altered.
----------------------------------
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo4b.log' to group 4;
alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo4b.log' to group 4
*
ERROR at line 1:
ORA-01577: cannot add log file '/u01/app/oracle12c/oradata/testdb/redo4b.log'
- file already part of database
---------------------------------
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo45.log' to group 4;
Database altered.
-------------------------------
[oracle12c@uit144 testdb]$ ls
c2.ctl redo1a.log redo2c.log redo3d.log sysaux.dbf undo1.dbf
control.ctl redo1b.log redo2d.log redo3e.log system.dbf undo2.dbf
dmts.dbf redo1c.log redo2e.log redo45.log temp1.dbf users.dbf
hr.dbf redo1d.log redo3a.log redo4b.log temp2.dbf
production01.dbf redo2a.log redo3b.log redo4.log temp3.dbf
production.dbf redo2b.log redo3c.log sales01.dbf temp.dbf
[oracle12c@uit144 testdb]$
--------------------------
add logfile member as unique redo name file
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo456666666.log' to group 4;
Database altered.
[oracle12c@uit144 testdb]$ ls redo4*
redo456666666.log redo45.log redo4b.log redo4.log
drop member file
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo456666666.log' to group 4;
Database altered.
--------------------
experiment for drooping group file
SQL> alter database add logfile group 5 ('/u01/app/oracle12c/oradata/testdb/redo5a.log') size 15m;
Database altered.
[oracle12c@uit144 testdb]$ ls
c2.ctl redo1b.log redo2e.log redo45.log temp1.dbf
control.ctl redo1c.log redo3a.log redo4b.log temp2.dbf
dmts.dbf redo1d.log redo3b.log redo4.log temp3.dbf
hr.dbf redo2a.log redo3c.log redo5a.log temp.dbf
production01.dbf redo2b.log redo3d.log sales01.dbf undo1.dbf
production.dbf redo2c.log redo3e.log sysaux.dbf undo2.dbf
redo1a.log redo2d.log redo456666666.log system.dbf users.dbf
[oracle12c@uit144 testdb]$ ls -l redo5*
-rw-r----- 1 oracle12c oinstall 15729152 Sep 16 17:21 redo5a.log
SQL> alter database drop logfile group 5;
Database altered.
SQL>
--------------------------------
status for all logfile are
SQL> @log
GROUP# MEMBERS THREAD# STATUS SEQUENCE# BYTES/1024/1024
---------- ---------- ---------- ---------------- ---------- ---------------
1 4 1 INACTIVE 339 4
2 5 1 INACTIVE 338 4
3 5 1 CURRENT 340 20
4 3 1 UNUSED 0 10
SQL> @logfile
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
1 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1a.log
2 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2a.log
3 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3a.log
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
4 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo4.log
1 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1b.log
2 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
rodb/redo2b.log
3 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3b.log
4 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo4b.log
1 INVALID ONLINE /u01/app/oracle NO 0
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
12c/oradata/wip
rodb/redo1c.log
1 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1d.log
2 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2c.log
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
2 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2d.log
2 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2e.log
3 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3c.log
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
3 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3d.log
3 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3e.log
4 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
rodb/redo45.log
17 rows selected.
------------------------
switch the logfiles
GROUP# MEMBERS THREAD# STATUS SEQUENCE# BYTES/1024/1024
---------- ---------- ---------- ---------------- ---------- ---------------
1 4 1 INACTIVE 339 4
2 5 1 INACTIVE 338 4
3 5 1 CURRENT 340 20
4 3 1 UNUSED 0 10
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBERS THREAD# STATUS SEQUENCE# BYTES/1024/1024
---------- ---------- ---------- ---------------- ---------- ---------------
1 4 1 INACTIVE 339 4
2 5 1 INACTIVE 338 4
3 5 1 ACTIVE 340 20
4 3 1 CURRENT 341 10
-------------------
HOw can u make a perfect tablesize for a viewing purpose in sql*plus prompt.
find out the max string length of a given column in a table.
select max(length(ACCOUNT_STATUS)) from dba_users;
SQL> select max(length(ACCOUNT_STATUS)),max(ACCOUNT_STATUS) from dba_users;
MAX(LENGTH(ACCOUNT_STATUS)) MAX(ACCOUNT_STATUS)
--------------------------- --------------------------------
16 OPEN
SQL> select max(length(username)) from dba_users;
MAX(LENGTH(USERNAME))
---------------------
17
SQL> select max(length(DEFAULT_TABLESPACE)) from dba_users;
MAX(LENGTH(DEFAULT_TABLESPACE))
-------------------------------
6
SQL> select max(length(TEMPORARY_TABLESPACE)) from dba_users;
MAX(LENGTH(TEMPORARY_TABLESPACE))
---------------------------------
5
SQL> select max(length(PROFILE)) from dba_users;
MAX(LENGTH(PROFILE))
--------------------
7
SQL> select max(length(LAST_LOGIN)) from dba_users;
MAX(LENGTH(LAST_LOGIN))
-----------------------
38
Result is :
SQL> @dba_users
USERNAME ACCOUNT_STATUS DEFAULT TEMPOR PROFILE
----------------- ---------------- ------- ------ --------
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
APPQOSSYS EXPIRED & LOCKED SYSAUX TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
GSMADMIN_INTERNAL EXPIRED & LOCKED SYSAUX TEMP DEFAULT
GSMCATUSER EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_OCM EXPIRED & LOCKED USERS TEMP DEFAULT
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
SCOTT OPEN DMTS TEMP DEFAULT
--------------------------------
Now for table dba_data_files
SQL> desc dba_data_files
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> select max(length(file_name)) from dba_data_files;
MAX(LENGTH(FILE_NAME))
----------------------
51
SQL> select max(length(file_id)) from dba_data_files;
MAX(LENGTH(FILE_ID))
--------------------
2
SQL> select max(length(tablespace_name)) from dba_data_files;
MAX(LENGTH(TABLESPACE_NAME))
----------------------------
11
SQL> select max(length(BYTES/1024/1024)) from dba_data_files;
MAX(LENGTH(BYTES/1024/1024))
----------------------------
11
FILE_NAME FILE_ID TABLESPACE_N SIZE_MB
------------------ ---------- ------------ ----------
ales01.dbf
/u01/app/oracle12c 6 UNDO2 30
/oradata/testdb/un
do2.dbf
/u01/app/oracle12c 7 PRODUCTION 20
/oradata/testdb/p
roduction.dbf
/u01/app/oracle12c 8 PRODUCTION1 20
----------------------------
Cheking for dba extents
DBA_EXTENTS
SQL> @dba_extents
OWNER
--------------------------------------------------------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_N EXTENT_ID FILE_ID BLOCK_ID BYTES/1024 BLOCKS
------------ ---------- ---------- ---------- ---------- ----------
U1
T1
USERS 0 4 128 64 8
SQL> select max(length(owner)) from dba_extents;
MAX(LENGTH(OWNER))
------------------
17
SQL> select max(length(SEGMENT_NAME)) from dba_extents;
MAX(LENGTH(SEGMENT_NAME))
-------------------------
30
--------------------------------------------------
================================================
find out details regarding
V$INSTANCE
V$DATAFILE
V$CONTROLFILE
V$DATABASE
V$LOGFILE
SQL> select name from v$database;
NAME
---------
testDB
SQL> select name form v$controlfile;
select name form v$controlfile
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle12c/oradata/testdb/control.ctl
/u01/app/oracle12c/oradata/testdb/c2.ctl
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle12c/oradata/testdb/system.dbf
/u01/app/oracle12c/oradata/testdb/sysaux.dbf
/u01/app/oracle12c/oradata/testdb/undo1.dbf
/u01/app/oracle12c/oradata/testdb/users.dbf
/u01/app/oracle12c/oradata/testdb/sales01.dbf
/u01/app/oracle12c/oradata/testdb/undo2.dbf
/u01/app/oracle12c/oradata/testdb/production.dbf
/u01/app/oracle12c/oradata/testdb/production01.dbf
/u01/app/oracle12c/oradata/testdb/dmts.dbf
/u01/app/oracle12c/oradata/testdb/hr.dbf
10 rows selected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
test
SQL> select name from v$logfile;
select name from v$logfile
*
ERROR at line 1:
ORA-00904: "NAME": invalid identifier
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle12c/oradata/testdb/redo1a.log
/u01/app/oracle12c/oradata/testdb/redo2a.log
/u01/app/oracle12c/oradata/testdb/redo3a.log
/u01/app/oracle12c/oradata/testdb/redo4.log
/u01/app/oracle12c/oradata/testdb/redo1b.log
/u01/app/oracle12c/oradata/testdb/redo2b.log
/u01/app/oracle12c/oradata/testdb/redo3b.log
/u01/app/oracle12c/oradata/testdb/redo4b.log
/u01/app/oracle12c/oradata/testdb/redo1c.log
/u01/app/oracle12c/oradata/testdb/redo1d.log
/u01/app/oracle12c/oradata/testdb/redo2c.log
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle12c/oradata/testdb/redo2d.log
/u01/app/oracle12c/oradata/testdb/redo2e.log
/u01/app/oracle12c/oradata/testdb/redo3c.log
/u01/app/oracle12c/oradata/testdb/redo3d.log
/u01/app/oracle12c/oradata/testdb/redo3e.log
/u01/app/oracle12c/oradata/testdb/redo45.log
17 rows selected.
----------------------------------------------------
===================================================
HOW CAN U COPY ALL FILES(DATAFILES/CONTROLFILES/LOGFILES AS COLDBACKUP).
[oracle12c@uit144 testdb]$ du -sh
1.2G .
[oracle12c@uit144 testdb]$ cd ..
[oracle12c@uit144 oradata]$ du -sh
5.6G .
[oracle12c@uit144 oradata]$ du -sh testdb
1.2G testdb
[oracle12c@uit144 oradata]$ du -sh testdb testdb skydb
1.2G testdb
604M testdb
1.7G skydb
[oracle12c@uit144 oradata]$ ls
lgdb moondb skydb testdb testdb
[oracle12c@uit144 oradata]$ ls -lah
total 28K
drwxr-x--- 7 oracle12c oinstall 4.0K Sep 7 18:59 .
drwxrwxr-x 10 oracle12c oinstall 4.0K Sep 4 17:14 ..
drwxr-xr-x 2 oracle12c oinstall 4.0K Sep 7 18:04 lgdb
drwxr-x--- 2 oracle12c oinstall 4.0K Sep 4 17:50 moondb
drwxr-x--- 2 oracle12c oinstall 4.0K Sep 4 17:17 skydb
drwxr-xr-x 2 oracle12c oinstall 4.0K Sep 10 15:06 testdb
drwxr-xr-x 2 oracle12c oinstall 4.0K Sep 16 17:21 testdb
[oracle12c@uit144 testdb]$ ls -lah
total 1.2G
drwxr-xr-x 2 oracle12c oinstall 4.0K Sep 16 17:21 .
drwxr-x--- 7 oracle12c oinstall 4.0K Sep 7 18:59 ..
-rw-r----- 1 oracle12c oinstall 8.1M Sep 18 13:28 c2.ctl
-rw-r----- 1 oracle12c oinstall 8.1M Sep 18 13:28 control.ctl
-rw-r----- 1 oracle12c oinstall 201M Sep 18 11:59 dmts.dbf
-rw-r----- 1 oracle12c oinstall 31M Sep 18 11:59 hr.dbf
-rw-r----- 1 oracle12c oinstall 21M Sep 18 11:59 production01.dbf
[oracle12c@uit144 testdb]$ cp *.dbf *.ctl *.log /u02/backup_18sep15/
cp: cannot create regular file `/u02/backup_18sep15/dmts.dbf': Permission denied
cp: cannot create regular file `/u02/backup_18sep15/hr.dbf': Permission denied
cp: cannot create regular file `/u02/backup_18sep15/production01.dbf': Permission denied
cp: cannot create regular file `/u02/backup_18sep15/production.dbf': Permission denied
cp: cannot create regular file `/u02/backup_18sep15/sales01.dbf': Permission denied
----------------------
NOW OPEN ROOT USER
AND PASS THIS COMMAND AS SUPER USER
[root@uit144 u02]# sudo chmod -R 777 /u02
[root@uit144 u02]$ mkdir backup_18sep15
created
now cpoy the file
[oracle12c@uit144 testdb]$ cp *.dbf *.ctl *.log /u02/backup_18sep15/
cheking the status in that folder
[oracle12c@uit144 u02]$ cd backup_18sep15/
[oracle12c@uit144 backup_18sep15]$ ls
c2.ctl redo1b.log redo2e.log redo45.log temp1.dbf
control.ctl redo1c.log redo3a.log redo4b.log temp2.dbf
dmts.dbf redo1d.log redo3b.log redo4.log temp3.dbf
hr.dbf redo2a.log redo3c.log redo5a.log temp.dbf
production01.dbf redo2b.log redo3d.log sales01.dbf undo1.dbf
production.dbf redo2c.log redo3e.log sysaux.dbf undo2.dbf
redo1a.log redo2d.log redo456666666.log system.dbf users.dbf
[oracle12c@uit144 backup_18sep15]$
=================================================
HOW CAN YOU ENABLE ARCHIVELOG MODE
SQL> strartup
SP2-0042: unknown command "strartup" - rest of line ignored.
SQL> startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
Database mounted.
Database opened.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> alter database archivelog
2 ;
alter database archivelog
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup mount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle12c/oradata/testdb/arch
Oldest online log sequence 341
Next log sequence to archive 344
Current log sequence 344
CHECK FOR ARCHIVELOG MODE WORKING OR NOT
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle12c/oradata/testdb/arch
Oldest online log sequence 341
Next log sequence to archive 344
Current log sequence 344
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle12c/oradata/testdb/arch
Oldest online log sequence 346
Next log sequence to archive 349
Current log sequence 349
CHECK AT PHYSICAL LOCATION
[oracle12c@uit144 arch]$ du -sh
6.6M .
[oracle12c@uit144 arch]$ ls
1_344_889816016.dbf 1_346_889816016.dbf 1_348_889816016.dbf
1_345_889816016.dbf 1_347_889816016.dbf
[oracle12c@uit144 arch]$ du -sh
6.7M .
[oracle12c@uit144 arch]$ ls -lah
total 6.7M
drwxr-xr-x 2 oracle12c oinstall 4.0K Sep 18 15:39 .
drwxr-xr-x 3 oracle12c oinstall 4.0K Sep 18 15:10 ..
-rw-r----- 1 oracle12c oinstall 6.6M Sep 18 15:37 1_344_889816016.dbf
-rw-r----- 1 oracle12c oinstall 6.5K Sep 18 15:39 1_345_889816016.dbf
-rw-r----- 1 oracle12c oinstall 1.0K Sep 18 15:39 1_346_889816016.dbf
-rw-r----- 1 oracle12c oinstall 1.5K Sep 18 15:39 1_347_889816016.dbf
-rw-r----- 1 oracle12c oinstall 1.0K Sep 18 15:39 1_348_889816016.dbf
[oracle12c@uit144 arch]$
=======================================================
USING OMF (ORACLE MANAGED FILES)
1.GO TO PARAMETER FILE LOCATION N CREATE ANOTHER COPY OF PARAMETER FILE
cd /u01/app/oracle12c/product/12.1.0/db_1/dbs
[oracle12c@uit144 dbs]$ ls
core_3897 hc_sky.dat init.ora lkMOONDB orapwmoon
core_4129 hc_test.dat inittest.ora lkSKYDB orapwsky
hc_lg.dat hc_test.dat inittest.ora lkTESTDB spfilemoon.ora
hc_moon.dat initlg.ora lkLGDB lktestDB spfilesky.ora
[oracle12c@uit144 dbs]$
[oracle12c@uit144 dbs]$ cp inittest.ora initomfdb.ora
[oracle12c@uit144 dbs]$ vi initomfdb.ora
db_name='omfdb'
memory_target=600m
processes = 350
#audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
#audit_trail ='db'
#db_block_size=8192
#db_domain=''
#db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area'
#db_recovery_file_dest_size=2G
#diagnostic_dest='<ORACLE_BASE>'
#dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
#open_cursors=300
#remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
#control_files = /u01/app/oracle12c/oradata/testdb/control.ctl,
# /u01/app/oracle12c/oradata/testdb/c2.ctl
compatible ='11.2.0'
3. CONNNECT / AS SYSDBA
SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 18 16:00:30 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create database;
create database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup nomount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
SQL>
SQL> create database;
Database created.
CHEKING FILES
SQL> select name from v$database;
NAME
---------
OMFDB
SQL> select name from v$controlfile;'
2 ;
ERROR:
ORA-01756: quoted string not properly terminated
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle12c/product/12.1.0/db_1/dbs/cntrlomfdb.dbf
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
omfdb
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle12c/product/12.1.0/db_1/dbs/log1omfdb.dbf
/u01/app/oracle12c/product/12.1.0/db_1/dbs/log2omfdb.dbf
SQL>
SQL> create tablespace ts1;
create tablespace ts1
*
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
==================================================
experiment with CONTROL FILE.
THERE IS NO STATIC VIEWS FOR CONTROL FILE.
1>select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle12c/oradata/testdb/control.ctl
/u01/app/oracle12c/oradata/testdb/c2.ctl
SQL> show parameter control_files;
NAME TYPE VALUE
--------------- -------- ----------------------
control_files string /u01/app/oracle12c/oradata/wip
rodb/control.ctl, /u01/app/ora
cle12c/oradata/testdb/c2.ctl
SQL> show parameter spfile;
NAME TYPE VALUE
--------- ----------- ------------------------------
spfile string
value=nothing then spfile
value=something pfile
A>
MULTIPLEXING(ADDITING) OF CONTROL FILE
i>shutdown db gracefully
shutdown immediate
ii>add controlfile at os level
[oracle12c@uit144 testdb]$ cp c2.ctl c3.ctl
iii>edit pfile
[oracle12c@uit144 dbs]$ vi inittest.ora
control_files = /u01/app/oracle12c/oradata/testdb/control.ctl,
/u01/app/oracle12c/oradata/testdb/c2.ctl,/u01/app/oracle12c/oradata/testdb/c3.ctl
iv>startup the database normal
ORA-00214: control file '/u01/app/oracle12c/oradata/testdb/control.ctl'
version 2843 inconsistent with file '/u01/app/oracle12c/oradata/testdb/c3.ctl'
version 2840
error due to changing time parameter file , db not shutdown.
SQL> startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
Database mounted.
Database opened.
SQL> show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle12c/oradata/wip
rodb/control.ctl, /u01/app/ora
cle12c/oradata/testdb/c2.ctl,
/u01/app/oracle12c/oradata/wi
prodb/c3.ctl
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
=========================================================
RECREATION OF CONTROL FILE
[oracle12c@uit144 trace]$ pwd
/u01/app/oracle12c/diag/rdbms/testdb/test/trace
SQL> alter database backup controlfile to trace;
Database altered.
oracle12c@uit144 testdb]$ mv c3.ctl /home/oracle12c/controlfilebackup/
[oracle12c@uit144 testdb]$ mv control.ctl /home/oracle12c/controlfilebackup/
[oracle12c@uit144 testdb]$
afiedt.buf c.sql dba_users.sql log.sql s.sql
a.txt dba_data_files.sql db_str.sql Music Templates
backup_own_purpose dba_extents.sql Desktop Pictures u1.sql
controlfilebackup dba_segments.sql Documents Public Videos
cr8dball.sql dba_ts1.sql Downloads runall.sql wipdb.sql
[oracle12c@uit144 ~]$ cd controlfilebackup/
[oracle12c@uit144 controlfilebackup]$ ls
c2.ctl
[oracle12c@uit144 controlfilebackup]$ ls
c2.ctl c3.ctl
[oracle12c@uit144 controlfilebackup]$ ls
c2.ctl c3.ctl control.ctl
[oracle12c@uit144 controlfilebackup]$
NO CONTROL FILE IN TESTDB LOCATION
Start the db at nomount
startup nomount
Done.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
SQL>
now go to SYS>>@/home/oracle12c/c.sql
SQL> @/home/oracle12c/c.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
===================================
RENAMING THE DATBASE
TAKE BACKUP OF CONTROLFILE
[oracle12c@uit144 dbs]$ vi inittest.ora
[oracle12c@uit144 dbs]$ cd
[oracle12c@uit144 testdb]$ rm c2.ctl c3.ctl control.ctl
SQL> startup nomount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
SQL> @/home/oracle12c/c.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
SQL>
SQL> alter database open resetlog;
alter database open resetlog
*
ERROR at line 1:
ORA-02288: invalid OPEN mode
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$database;
NAME
---------
testDB1
================================
SCOTT USER file LOCATION
[oracle12c@uit144 admin]$ cp utlsampl.sql ~/demo.sql
[oracle12c@uit144 admin]$ pwd
/u01/app/oracle12c/product/12.1.0/db_1/rdbms/admin
[oracle12c@uit144 admin]$
=======================================
CHANGING GLOGIN STATUS
[oracle12c@uit144 admin]$ pwd
/u01/app/oracle12c/product/12.1.0/db_1/sqlplus/admin
[oracle12c@uit144 admin]$
SYS>>@u1
connecting to user1
Connected.
Done.
U1>>@s
Now connecting to oracle with sysdba privileges
Connected.
connected successfully
Enjoyed!!!!!!!!!!
SYS>>conn scott/tiger;
Connected.
SCOTT>>
ALTER SESSION SET nls_date_format = 'HH:MI:SS';
-- SET the SQLPROMPT to include the _USER, _CONNECT_IDENTIFIER
-- and _DATE variables.
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "
-- To set the number of lines to display in a report page to 24.
SET PAGESIZE 24
-- To set the number of characters to display on each report line to 78.
SET LINESIZE 78
-- To set the number format used in a report to $99,999.
SET NUMFORMAT $99,999
========================
SHOW ERRORLOGGING CONCEPT
select username,statement,message
from sperrorlog;
USERNAME
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
SYS
set errorlogging on
SP2-0310: unable to open file "dba_file.sql"
SYS
set errorlogging on
SP2-0310: unable to open file "dba_files.sql"
SYS
select username,statement,message
from sperrorlog;
ORA-00933: SQL command not properly ended
3 rows selected.
SYS>>
=====================================
STORE Command
During a given SQL*Plus session, it’s likely that you’ll need to change your environment settings in
order to run a specific SQL script or command. If you want to preserve these settings for future use,
you can do so with the help of the STORE command. Once you store the values in a script, you can run
116 CHAPTER 4 ■ U SING SQL*PLUS AND ORACLE EN TERPR ISE MANAGER
that script anytime to restore the original values of all variables. Thus, if you run a report that requires
changing some variable values, just run the script that contains the original values after you finish the
report to restore the original values of the variables.
The following example shows how to use the STORE command to save your SQL*Plus environment
settings:
SQL> STORE SET mysqlplus.sql
Created file mysqlplus.sql
Executing the previous command will result in the storing of all the current environmental
values in the file named sqlplus.sql. Once you store your favorite environment variables in a file,
you can easily reuse them anytime you want by simply executing the script. (I explain the execution
of SQL scripts in the following sections.) In order to restore the stored values of all system variables,
enter
SQL> START mysqlplus.sql
If you wish, you can just enter @mysqlplus.sql
SHOW USER
SHOW RECYCLEBIN
SHOW TTITLE
SHOW BTITLE
SHOW ALL
SHOW SGA
SHOW PARAMETERS
SHOW PARAMETERS MEMORY
SHOW SPPARAMETER DB_NAME
SHOW ERR PROCEDURE TEST_PROC
SQLPROMPT Command
SQL> SET SQLPROMPT '_CONNECT_IDENTIFIER > '
nick >
SQL> SET SQLPROMPT "_USER > "
APPOWNER >
SQL> SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
APPOWNER@nick >
SQL> SET SQLPROMPT "_USER _PRIVILEGE> "
SYS AS SYSDBA>
SQL> SET SQLPROMPT "_USER 'on' _DATE 'at' _CONNECT_IDENTIFIER > "
SYS on 20-JUN-05 at nick>
============================
HOST Command :
The HOST command enables you to use operating system commands from within SQL*Plus. You
may, for example, want to see if a file exists in a certain directory, or you may want to use the cp or
tar commands at the UNIX level and return to your SQL*Plus session to resume interacting with the
Oracle database.
Here is an example showing how to use the HOST command:
SQL> HOST cp /u01/app/oracle/new.sql /tmp
The HOST command in the preceding example will help you copy the new.sql file from the specified
directory to the tmp directory.
Just about any command you can use at the operating system level can be executed using the
HOST command. You can replace the HOST command with ! (bang, or exclamation point) to run operating
system commands from within SQL*Plus, as in the following example:
SQL> ! cp /u01/app/oracle/new.sql /tmp
■Note If you just type the command by itself, as in HOST or !, you’ll be transported to the operating system directory
from which you logged into the SQL*Plus session.
When you’re done with your operating system task, just type exit on the command line, and
you’ll return to the SQL*Plus session you just left. Here’s an example:
SQL> HOST
$ exit
SQL>
==================================
The ACCEPT and PROMPT commands are usually used together in a SQL script, typically to request
user input and save the input in variables that can be used later in the program. The following example
illustrates the use of these commands:
SQL> PROMPT 'Please enter your last name'
SQL> ACCEPT lastname CHAR FORMAT a20 alapati
==========================
SQL> SHOW PAUSE
PAUSE is OFF
SQL> SET PAUSE ON
SQL> SHOW PAUSE
PAUSE is ON and set to ""
SQL> BREAK ON DEPT_ID SKIP PAGE ON JOB_ID SKIP 1 ON SALARY SKIP 1
SQL> SELECT dept_id, job_id, salary, emp_name
FROM employees
WHERE salary > 50000
ORDER BY dept_id, job_id, salary, emp_name;
SQL> COLUMN dept FORMAT a15 HEADING 'Department'
SQL> COLUMN cost FORMAT $9999
SQL> COMPUTE AVG OF sales ON district
SQL> SELECT region, district, sales
FROM total_sales
WHERE district = 'NORTH';
SQL> REPFOOTER PAGE RIGHT 'END OF THE 1st QUARTER RESULTS REPORT'
SQL> REPHEADER PAGE CENTER '1st QUARTER RESULTS REPORT FOR 2008'
SQL> TTITLE 'Annual Financial Report for the Women's Club, 2008'
SQL> BTITLE '2005 Report'
Caution After you use the BTITLE and TTITLE commands, as well as many other SQL*Plus commands,
you have to manually turn them off to prevent all the ensuing SQL commands in that session from inheriting those
settings. For example, if you don’t turn the title off after you create a report, all the subsequent output for any
command will be printed with the same title.
Saving the SQL Buffer Contents to a File
SQL> SELECT username,process,sid,serial#
FROM v$session
WHERE status = 'ACTIVE'
.
SQL> SAVE status.sql
Created file status.sql
SQL> SAVE REPLACE status.sql
SQL> SAVE APPEND status.sql
=====================================
On UNIX systems, you can configure an environment variable called ORACLE_PATH to tell SQL*Plus
where to look for a script. This way, you can put all your routine SQL scripts in one location, and you
don’t need to specify the complete path for the file each time you want to execute an existing script.
On my UNIX servers, for example, this is how I set the variable:
$ export $ORACLE_PATH=/u01/app/oracle/admin/dba/sql
=====================================
Creating a Windows Batch Script
You can easily create a batch script in a Windows system to run your SQL*Plus commands. For instance,
say you have a script file called testscript.sql that provides information about the users in your
database. If you want to schedule this script to run at a specified time, you must first create a Windows
batch file that invokes the testscript.sql script file. You can then use the Windows at scheduling
utility, if you wish, to schedule the batch script.
Here’s a simple example. First create a batch file, named testbatch.bat, containing the following
(the testscript.sql script writes its output to the output.txt file):
sqlplus username/password@connect_identifier @C:\temp\testscript.sql
notepad.exe C:\temp\output.txt
The preceding batch file will start a SQL*Plus session, run the testscript.sql script, and output
the results of the testscript.sql file into the output.txt file using the notepad executable.
SQL> DEFINE dept = finance
SQL> UNDEFINE dept
SQL> col segment_name for a27
DEFINE owner = '&1'
SELECT segment_name,segment_type,extents
FROM dba_segments
WHERE owner = upper ('&owner')
AND extents > 10
AND segment_name NOT LIKE 'TMP%'
ORDER BY segment_type,extents desc
SQL> @extents.sql
Enter value for 1: system
SEGMENT_NAME SEGMENT_TYPE EXTENTS
HELP_TOPIC_SEQ INDEX 18
PRODUCT_PROFILE TABLE 22
SQL>
Predefined SQL*Plus Variables
SQL> DEFINE
DEFINE _DATE = "23-DEC-07" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl2" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release
11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options"
(CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)
SQL> exit
SQL>
======================
Listing SQL Commands
SQL> l
1 SELECT username, status, process, sid, serial#
2 FROM v$session
3* WHERE status = 'ACTIVE'
SQL>
If you want to see what’s in your SQL script before you execute it, load it from the operating
system into the SQL buffer by using the GET command, as follows:
SQL> GET status.sql
1 SELECT username,status,process,sid,serial#
2 FROM v$session
3* WHERE status = 'ACTIVE'
SQL> GET one_script.sql
1 @check.sql
2 @create_table.sql
3 @insert_table.sql
4* @create_constraint.sql
c/serial/serial#
SQL> SELECT username, profile
2 FROM dba_users
3 .
SQL> 1
1* SELECT username, profile
SQL> APPEND , created_date
1* select username, profile, created_date
SQL> l
1 SELECT username, profile, created_date
2* FROM dba_users
SQL>
SET SQLTERMINATOR OFF
SQL> INSERT INTO EMPLOYEES VALUES ('BEGIN
2 LOAD_PROCEDURE);
3* END')
SQL> ARCHIVE LOG START
Statement processed.
SQL> ARCHIVE LOG STOP
Statement processed.
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /a03/app/oracle/admin/NICKO/arch/
Oldest online log sequence 933
Next log sequence to archive 937
Current log sequence 937
SQL>
Here is an example that you are likely to be familiar with:
1. Set the environment variables:
SQL> SET ECHO OFF HEADING OFF FEEDBACK OFF
2. Name a spool file, to which the output of the first script will be written:
SQL> SPOOL test.txt
3. Execute the SQL that creates more SQL:
SQL> SELECT 'grant connect, resource to '||username||';' FROM dba_users;
This is part of the output of the preceding command:
GRANT CONNECT, RESOURCE TO DBA1;
GRANT CONNECT, RESOURCE TO MAMIDI;
GRANT CONNECT, RESOURCE TO JEFFRESS;
GRANT CONNECT, RESOURCE TO CAMPBELL;
GRANT CONNECT, RESOURCE TO ALAPATI;
GRANT CONNECT, RESOURCE TO BOLLU;
GRANT CONNECT, RESOURCE TO BOGAVELLI;
SQL> SPOOL OFF
4. The spooled script will have captured the preceding commands. Now run that script
(test.txt in this example):
SQL> @test.txt
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
SQL>s
===========================================================
-- File Name : https://oracle-base.com/dba/monitoring/access.sql
-- Author : Tim Hall
-- Description : Lists all objects being accessed in the schema.
-- Call Syntax : @access (schema-name or all) (object-name or all)
-- Requirements : Access to the v$views.
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET LINESIZE 255
SET VERIFY OFF
COLUMN object FORMAT A30
SELECT a.object,
a.type,
a.sid,
b.serial#,
b.username,
b.osuser,
b.program
FROM v$access a,
v$session b
WHERE a.sid = b.sid
AND a.owner = DECODE(UPPER('&1'), 'ALL', a.object, UPPER('&1'))
AND a.object = DECODE(UPPER('&2'), 'ALL', a.object, UPPER('&2'))
ORDER BY a.object;
========================================================================================
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/testdb/temp2.dbf' size 30m;
Tablespace created.
SQL> create temporary tablespace temp3 tempfile '/u01/app/oracle12c/oradata/testdb/temp3.dbf' size 30m;
Tablespace created.
SQL> create tablespace hr datafile '/u01/app/oracle12c/oradata/testdb/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>
SQL> revoke select on t2 from u1;
Revoke succeeded.
SQL>
------------------------------------------------------
15-sep-2015
------------------\=====
========================\
[oracle12c@uit144 Desktop]$ cd
[oracle12c@uit144 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Sep 16 14:51:04 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> desc v$log;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
CON_ID NUMBER
SQL> select GROUP#,MEMBERS,THREAD#,STATUS,SEQUENCE#,BYTES/1024/1024
2 from v$log;
GROUP# MEMBERS THREAD# STATUS SEQUENCE# BYTES/1024/1024
---------- ---------- ---------- ---------------- ---------- ---------------
1 1 1 CURRENT 339 4
2 1 1 INACTIVE 338 4
SQL> save log.sql
Created file log.sql
SQL> cl scr
SQL> desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
CON_ID NUMBER
SQL> select GROUP#,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE,CON_ID
2 from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_ CON_ID
--- ----------
1 ONLINE
/u01/app/oracle12c/oradata/testdb/redo1a.log
NO 0
2 ONLINE
/u01/app/oracle12c/oradata/testdb/redo2a.log
NO 0
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_ CON_ID
--- ----------
SQL> save logfile.sql
Created file logfile.sql
SQL> col MEMBER for a15
SQL> /
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
1 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1a.log
2 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2a.log
---------------
QL> alter database add logfile group 3 ('/u01/app/oracle12c/oradata/testdb/redo3a.log') size 20m;
Database altered.
SQL> alter database add logfile group 4 ('/u01/app/oracle12c/oradata/testdb/redo4.log') size 10m;
Database altered.
---------------
alter database add logfile member ('/u01/app/oracle12c/oradata/testdb/redo1b.log') to group 1;
alter database add logfile member (/u01/app/oracle12c/oradata/testdb/redo1b.log) to group 1
*
ERROR at line 1:
ORA-02236: invalid file name
SQL> alter database add logfile member 'u01/app/oracle12c/oradata/testdb/redo1b.log' to group 1;
alter database add logfile member 'u01/app/oracle12c/oradata/testdb/redo1b.log' to group 1
*
ERROR at line 1:
ORA-00301: error in adding log file
'u01/app/oracle12c/oradata/testdb/redo1b.log' - file cannot be created
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
SQL> oerr ora 27040
SP2-0734: unknown command beginning "oerr ora 2..." - rest of line ignored.
SQL> !oerr ora 27040
27040, 00000, "file create error, unable to create file"
// *Cause: create system call returned an error, unable to create file
// *Action: verify filename, and permissions
---------------
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo1b.log' to group 1;
Database altered.
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo2b.log' to group 2;
Database altered.
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo3b.log' to group 3;
Database altered.
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo4b.log' to group 4;
Database altered.
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo1;;
alter database add logfile member '/u01/app/oracle12c/oradata/testdb/;
*
ERROR at line 1:
ORA-01756: quoted string not properly terminated
SQL> @logfile
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
1 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1a.log
2 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2a.log
3 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3a.log
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
4 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo4.log
1 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1b.log
2 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
rodb/redo2b.log
3 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3b.log
4 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo4b.log
-------------
alter database add logfile member('/u01/app/oracle12c/oradata/testdb/redo1c.log','/u01/app/oracle12c/oradata/testdb/redo1d.log') to group 1;
SQL> alter database add logfile member('/u01/app/oracle12c/oradata/testdb/redo2c.log','/u01/app/oracle12c/oradata/testdb/redo2d.log') to group 2;
alter database add logfile member('/u01/app/oracle12c/oradata/testdb/redo2c.log','/u01/app/oracle12c/oradata/testdb/redo2d.log') to group 2
*
ERROR at line 1:
ORA-02236: invalid file name
SQL> alter database add logfile member'/u01/app/oracle12c/oradata/testdb/redo2c.log','/u01/app/oracle12c/oradata/testdb/redo2d.log' to group 2;
Database altered.
---------------------------------
alter database add logfile member'/u01/app/oracle12c/oradata/testdb/redo2c.log','/u01/app/oracle12c/oradata/testdb/redo2d.log' to group 2
ERROR at line 1:
ORA-00357: too many members specified for log file, the maximum is 5
---------------------------------
[oracle12c@uit144 testdb]$ ls
c2.ctl redo1a.log redo2c.log redo4.log temp3.dbf
control.ctl redo1b.log redo2d.log sales01.dbf temp.dbf
dmts.dbf redo1c.log redo2e.log sysaux.dbf undo1.dbf
hr.dbf redo1d.log redo3a.log system.dbf undo2.dbf
production01.dbf redo2a.log redo3b.log temp1.dbf users.dbf
production.dbf redo2b.log redo4b.log temp2.dbf
[oracle12c@uit144 testdb]$
---------------------------------
alter database add logfile member '/u01/app/oracle12c/oradata/testdb/
redo3c.log','/u01/app/oracle12c/oradata/testdb/redo3c.log'
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo3c.log','/u01/app/oracle12c/oradata/testdb/redo3d.log','/u01/app/oracle12c/oradata/testdb/redo3e.log' to group 3;
Database altered.
----------------------------------
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo4b.log' to group 4;
alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo4b.log' to group 4
*
ERROR at line 1:
ORA-01577: cannot add log file '/u01/app/oracle12c/oradata/testdb/redo4b.log'
- file already part of database
---------------------------------
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo45.log' to group 4;
Database altered.
-------------------------------
[oracle12c@uit144 testdb]$ ls
c2.ctl redo1a.log redo2c.log redo3d.log sysaux.dbf undo1.dbf
control.ctl redo1b.log redo2d.log redo3e.log system.dbf undo2.dbf
dmts.dbf redo1c.log redo2e.log redo45.log temp1.dbf users.dbf
hr.dbf redo1d.log redo3a.log redo4b.log temp2.dbf
production01.dbf redo2a.log redo3b.log redo4.log temp3.dbf
production.dbf redo2b.log redo3c.log sales01.dbf temp.dbf
[oracle12c@uit144 testdb]$
--------------------------
add logfile member as unique redo name file
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo456666666.log' to group 4;
Database altered.
[oracle12c@uit144 testdb]$ ls redo4*
redo456666666.log redo45.log redo4b.log redo4.log
drop member file
SQL> alter database add logfile member '/u01/app/oracle12c/oradata/testdb/redo456666666.log' to group 4;
Database altered.
--------------------
experiment for drooping group file
SQL> alter database add logfile group 5 ('/u01/app/oracle12c/oradata/testdb/redo5a.log') size 15m;
Database altered.
[oracle12c@uit144 testdb]$ ls
c2.ctl redo1b.log redo2e.log redo45.log temp1.dbf
control.ctl redo1c.log redo3a.log redo4b.log temp2.dbf
dmts.dbf redo1d.log redo3b.log redo4.log temp3.dbf
hr.dbf redo2a.log redo3c.log redo5a.log temp.dbf
production01.dbf redo2b.log redo3d.log sales01.dbf undo1.dbf
production.dbf redo2c.log redo3e.log sysaux.dbf undo2.dbf
redo1a.log redo2d.log redo456666666.log system.dbf users.dbf
[oracle12c@uit144 testdb]$ ls -l redo5*
-rw-r----- 1 oracle12c oinstall 15729152 Sep 16 17:21 redo5a.log
SQL> alter database drop logfile group 5;
Database altered.
SQL>
--------------------------------
status for all logfile are
SQL> @log
GROUP# MEMBERS THREAD# STATUS SEQUENCE# BYTES/1024/1024
---------- ---------- ---------- ---------------- ---------- ---------------
1 4 1 INACTIVE 339 4
2 5 1 INACTIVE 338 4
3 5 1 CURRENT 340 20
4 3 1 UNUSED 0 10
SQL> @logfile
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
1 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1a.log
2 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2a.log
3 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3a.log
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
4 ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo4.log
1 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1b.log
2 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
rodb/redo2b.log
3 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3b.log
4 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo4b.log
1 INVALID ONLINE /u01/app/oracle NO 0
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
12c/oradata/wip
rodb/redo1c.log
1 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo1d.log
2 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2c.log
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
2 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2d.log
2 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo2e.log
3 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3c.log
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
3 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3d.log
3 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
rodb/redo3e.log
4 INVALID ONLINE /u01/app/oracle NO 0
12c/oradata/wip
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- --------------- --- ----------
rodb/redo45.log
17 rows selected.
------------------------
switch the logfiles
GROUP# MEMBERS THREAD# STATUS SEQUENCE# BYTES/1024/1024
---------- ---------- ---------- ---------------- ---------- ---------------
1 4 1 INACTIVE 339 4
2 5 1 INACTIVE 338 4
3 5 1 CURRENT 340 20
4 3 1 UNUSED 0 10
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBERS THREAD# STATUS SEQUENCE# BYTES/1024/1024
---------- ---------- ---------- ---------------- ---------- ---------------
1 4 1 INACTIVE 339 4
2 5 1 INACTIVE 338 4
3 5 1 ACTIVE 340 20
4 3 1 CURRENT 341 10
-------------------
HOw can u make a perfect tablesize for a viewing purpose in sql*plus prompt.
find out the max string length of a given column in a table.
select max(length(ACCOUNT_STATUS)) from dba_users;
SQL> select max(length(ACCOUNT_STATUS)),max(ACCOUNT_STATUS) from dba_users;
MAX(LENGTH(ACCOUNT_STATUS)) MAX(ACCOUNT_STATUS)
--------------------------- --------------------------------
16 OPEN
SQL> select max(length(username)) from dba_users;
MAX(LENGTH(USERNAME))
---------------------
17
SQL> select max(length(DEFAULT_TABLESPACE)) from dba_users;
MAX(LENGTH(DEFAULT_TABLESPACE))
-------------------------------
6
SQL> select max(length(TEMPORARY_TABLESPACE)) from dba_users;
MAX(LENGTH(TEMPORARY_TABLESPACE))
---------------------------------
5
SQL> select max(length(PROFILE)) from dba_users;
MAX(LENGTH(PROFILE))
--------------------
7
SQL> select max(length(LAST_LOGIN)) from dba_users;
MAX(LENGTH(LAST_LOGIN))
-----------------------
38
Result is :
SQL> @dba_users
USERNAME ACCOUNT_STATUS DEFAULT TEMPOR PROFILE
----------------- ---------------- ------- ------ --------
ANONYMOUS LOCKED SYSAUX TEMP DEFAULT
APPQOSSYS EXPIRED & LOCKED SYSAUX TEMP DEFAULT
AUDSYS EXPIRED & LOCKED USERS TEMP DEFAULT
DBSNMP EXPIRED & LOCKED SYSAUX TEMP DEFAULT
DIP EXPIRED & LOCKED USERS TEMP DEFAULT
GSMADMIN_INTERNAL EXPIRED & LOCKED SYSAUX TEMP DEFAULT
GSMCATUSER EXPIRED & LOCKED USERS TEMP DEFAULT
GSMUSER EXPIRED & LOCKED USERS TEMP DEFAULT
ORACLE_OCM EXPIRED & LOCKED USERS TEMP DEFAULT
OUTLN EXPIRED & LOCKED SYSTEM TEMP DEFAULT
SCOTT OPEN DMTS TEMP DEFAULT
--------------------------------
Now for table dba_data_files
SQL> desc dba_data_files
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> select max(length(file_name)) from dba_data_files;
MAX(LENGTH(FILE_NAME))
----------------------
51
SQL> select max(length(file_id)) from dba_data_files;
MAX(LENGTH(FILE_ID))
--------------------
2
SQL> select max(length(tablespace_name)) from dba_data_files;
MAX(LENGTH(TABLESPACE_NAME))
----------------------------
11
SQL> select max(length(BYTES/1024/1024)) from dba_data_files;
MAX(LENGTH(BYTES/1024/1024))
----------------------------
11
FILE_NAME FILE_ID TABLESPACE_N SIZE_MB
------------------ ---------- ------------ ----------
ales01.dbf
/u01/app/oracle12c 6 UNDO2 30
/oradata/testdb/un
do2.dbf
/u01/app/oracle12c 7 PRODUCTION 20
/oradata/testdb/p
roduction.dbf
/u01/app/oracle12c 8 PRODUCTION1 20
----------------------------
Cheking for dba extents
DBA_EXTENTS
SQL> @dba_extents
OWNER
--------------------------------------------------------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_N EXTENT_ID FILE_ID BLOCK_ID BYTES/1024 BLOCKS
------------ ---------- ---------- ---------- ---------- ----------
U1
T1
USERS 0 4 128 64 8
SQL> select max(length(owner)) from dba_extents;
MAX(LENGTH(OWNER))
------------------
17
SQL> select max(length(SEGMENT_NAME)) from dba_extents;
MAX(LENGTH(SEGMENT_NAME))
-------------------------
30
--------------------------------------------------
================================================
find out details regarding
V$INSTANCE
V$DATAFILE
V$CONTROLFILE
V$DATABASE
V$LOGFILE
SQL> select name from v$database;
NAME
---------
testDB
SQL> select name form v$controlfile;
select name form v$controlfile
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle12c/oradata/testdb/control.ctl
/u01/app/oracle12c/oradata/testdb/c2.ctl
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle12c/oradata/testdb/system.dbf
/u01/app/oracle12c/oradata/testdb/sysaux.dbf
/u01/app/oracle12c/oradata/testdb/undo1.dbf
/u01/app/oracle12c/oradata/testdb/users.dbf
/u01/app/oracle12c/oradata/testdb/sales01.dbf
/u01/app/oracle12c/oradata/testdb/undo2.dbf
/u01/app/oracle12c/oradata/testdb/production.dbf
/u01/app/oracle12c/oradata/testdb/production01.dbf
/u01/app/oracle12c/oradata/testdb/dmts.dbf
/u01/app/oracle12c/oradata/testdb/hr.dbf
10 rows selected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
test
SQL> select name from v$logfile;
select name from v$logfile
*
ERROR at line 1:
ORA-00904: "NAME": invalid identifier
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle12c/oradata/testdb/redo1a.log
/u01/app/oracle12c/oradata/testdb/redo2a.log
/u01/app/oracle12c/oradata/testdb/redo3a.log
/u01/app/oracle12c/oradata/testdb/redo4.log
/u01/app/oracle12c/oradata/testdb/redo1b.log
/u01/app/oracle12c/oradata/testdb/redo2b.log
/u01/app/oracle12c/oradata/testdb/redo3b.log
/u01/app/oracle12c/oradata/testdb/redo4b.log
/u01/app/oracle12c/oradata/testdb/redo1c.log
/u01/app/oracle12c/oradata/testdb/redo1d.log
/u01/app/oracle12c/oradata/testdb/redo2c.log
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle12c/oradata/testdb/redo2d.log
/u01/app/oracle12c/oradata/testdb/redo2e.log
/u01/app/oracle12c/oradata/testdb/redo3c.log
/u01/app/oracle12c/oradata/testdb/redo3d.log
/u01/app/oracle12c/oradata/testdb/redo3e.log
/u01/app/oracle12c/oradata/testdb/redo45.log
17 rows selected.
----------------------------------------------------
===================================================
HOW CAN U COPY ALL FILES(DATAFILES/CONTROLFILES/LOGFILES AS COLDBACKUP).
[oracle12c@uit144 testdb]$ du -sh
1.2G .
[oracle12c@uit144 testdb]$ cd ..
[oracle12c@uit144 oradata]$ du -sh
5.6G .
[oracle12c@uit144 oradata]$ du -sh testdb
1.2G testdb
[oracle12c@uit144 oradata]$ du -sh testdb testdb skydb
1.2G testdb
604M testdb
1.7G skydb
[oracle12c@uit144 oradata]$ ls
lgdb moondb skydb testdb testdb
[oracle12c@uit144 oradata]$ ls -lah
total 28K
drwxr-x--- 7 oracle12c oinstall 4.0K Sep 7 18:59 .
drwxrwxr-x 10 oracle12c oinstall 4.0K Sep 4 17:14 ..
drwxr-xr-x 2 oracle12c oinstall 4.0K Sep 7 18:04 lgdb
drwxr-x--- 2 oracle12c oinstall 4.0K Sep 4 17:50 moondb
drwxr-x--- 2 oracle12c oinstall 4.0K Sep 4 17:17 skydb
drwxr-xr-x 2 oracle12c oinstall 4.0K Sep 10 15:06 testdb
drwxr-xr-x 2 oracle12c oinstall 4.0K Sep 16 17:21 testdb
[oracle12c@uit144 testdb]$ ls -lah
total 1.2G
drwxr-xr-x 2 oracle12c oinstall 4.0K Sep 16 17:21 .
drwxr-x--- 7 oracle12c oinstall 4.0K Sep 7 18:59 ..
-rw-r----- 1 oracle12c oinstall 8.1M Sep 18 13:28 c2.ctl
-rw-r----- 1 oracle12c oinstall 8.1M Sep 18 13:28 control.ctl
-rw-r----- 1 oracle12c oinstall 201M Sep 18 11:59 dmts.dbf
-rw-r----- 1 oracle12c oinstall 31M Sep 18 11:59 hr.dbf
-rw-r----- 1 oracle12c oinstall 21M Sep 18 11:59 production01.dbf
[oracle12c@uit144 testdb]$ cp *.dbf *.ctl *.log /u02/backup_18sep15/
cp: cannot create regular file `/u02/backup_18sep15/dmts.dbf': Permission denied
cp: cannot create regular file `/u02/backup_18sep15/hr.dbf': Permission denied
cp: cannot create regular file `/u02/backup_18sep15/production01.dbf': Permission denied
cp: cannot create regular file `/u02/backup_18sep15/production.dbf': Permission denied
cp: cannot create regular file `/u02/backup_18sep15/sales01.dbf': Permission denied
----------------------
NOW OPEN ROOT USER
AND PASS THIS COMMAND AS SUPER USER
[root@uit144 u02]# sudo chmod -R 777 /u02
[root@uit144 u02]$ mkdir backup_18sep15
created
now cpoy the file
[oracle12c@uit144 testdb]$ cp *.dbf *.ctl *.log /u02/backup_18sep15/
cheking the status in that folder
[oracle12c@uit144 u02]$ cd backup_18sep15/
[oracle12c@uit144 backup_18sep15]$ ls
c2.ctl redo1b.log redo2e.log redo45.log temp1.dbf
control.ctl redo1c.log redo3a.log redo4b.log temp2.dbf
dmts.dbf redo1d.log redo3b.log redo4.log temp3.dbf
hr.dbf redo2a.log redo3c.log redo5a.log temp.dbf
production01.dbf redo2b.log redo3d.log sales01.dbf undo1.dbf
production.dbf redo2c.log redo3e.log sysaux.dbf undo2.dbf
redo1a.log redo2d.log redo456666666.log system.dbf users.dbf
[oracle12c@uit144 backup_18sep15]$
=================================================
HOW CAN YOU ENABLE ARCHIVELOG MODE
SQL> strartup
SP2-0042: unknown command "strartup" - rest of line ignored.
SQL> startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
Database mounted.
Database opened.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> alter database archivelog
2 ;
alter database archivelog
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup mount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle12c/oradata/testdb/arch
Oldest online log sequence 341
Next log sequence to archive 344
Current log sequence 344
CHECK FOR ARCHIVELOG MODE WORKING OR NOT
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle12c/oradata/testdb/arch
Oldest online log sequence 341
Next log sequence to archive 344
Current log sequence 344
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle12c/oradata/testdb/arch
Oldest online log sequence 346
Next log sequence to archive 349
Current log sequence 349
CHECK AT PHYSICAL LOCATION
[oracle12c@uit144 arch]$ du -sh
6.6M .
[oracle12c@uit144 arch]$ ls
1_344_889816016.dbf 1_346_889816016.dbf 1_348_889816016.dbf
1_345_889816016.dbf 1_347_889816016.dbf
[oracle12c@uit144 arch]$ du -sh
6.7M .
[oracle12c@uit144 arch]$ ls -lah
total 6.7M
drwxr-xr-x 2 oracle12c oinstall 4.0K Sep 18 15:39 .
drwxr-xr-x 3 oracle12c oinstall 4.0K Sep 18 15:10 ..
-rw-r----- 1 oracle12c oinstall 6.6M Sep 18 15:37 1_344_889816016.dbf
-rw-r----- 1 oracle12c oinstall 6.5K Sep 18 15:39 1_345_889816016.dbf
-rw-r----- 1 oracle12c oinstall 1.0K Sep 18 15:39 1_346_889816016.dbf
-rw-r----- 1 oracle12c oinstall 1.5K Sep 18 15:39 1_347_889816016.dbf
-rw-r----- 1 oracle12c oinstall 1.0K Sep 18 15:39 1_348_889816016.dbf
[oracle12c@uit144 arch]$
=======================================================
USING OMF (ORACLE MANAGED FILES)
1.GO TO PARAMETER FILE LOCATION N CREATE ANOTHER COPY OF PARAMETER FILE
cd /u01/app/oracle12c/product/12.1.0/db_1/dbs
[oracle12c@uit144 dbs]$ ls
core_3897 hc_sky.dat init.ora lkMOONDB orapwmoon
core_4129 hc_test.dat inittest.ora lkSKYDB orapwsky
hc_lg.dat hc_test.dat inittest.ora lkTESTDB spfilemoon.ora
hc_moon.dat initlg.ora lkLGDB lktestDB spfilesky.ora
[oracle12c@uit144 dbs]$
[oracle12c@uit144 dbs]$ cp inittest.ora initomfdb.ora
[oracle12c@uit144 dbs]$ vi initomfdb.ora
db_name='omfdb'
memory_target=600m
processes = 350
#audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
#audit_trail ='db'
#db_block_size=8192
#db_domain=''
#db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area'
#db_recovery_file_dest_size=2G
#diagnostic_dest='<ORACLE_BASE>'
#dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
#open_cursors=300
#remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
#control_files = /u01/app/oracle12c/oradata/testdb/control.ctl,
# /u01/app/oracle12c/oradata/testdb/c2.ctl
compatible ='11.2.0'
3. CONNNECT / AS SYSDBA
SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 18 16:00:30 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create database;
create database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup nomount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
SQL>
SQL> create database;
Database created.
CHEKING FILES
SQL> select name from v$database;
NAME
---------
OMFDB
SQL> select name from v$controlfile;'
2 ;
ERROR:
ORA-01756: quoted string not properly terminated
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle12c/product/12.1.0/db_1/dbs/cntrlomfdb.dbf
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
omfdb
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle12c/product/12.1.0/db_1/dbs/log1omfdb.dbf
/u01/app/oracle12c/product/12.1.0/db_1/dbs/log2omfdb.dbf
SQL>
SQL> create tablespace ts1;
create tablespace ts1
*
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
==================================================
experiment with CONTROL FILE.
THERE IS NO STATIC VIEWS FOR CONTROL FILE.
1>select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle12c/oradata/testdb/control.ctl
/u01/app/oracle12c/oradata/testdb/c2.ctl
SQL> show parameter control_files;
NAME TYPE VALUE
--------------- -------- ----------------------
control_files string /u01/app/oracle12c/oradata/wip
rodb/control.ctl, /u01/app/ora
cle12c/oradata/testdb/c2.ctl
SQL> show parameter spfile;
NAME TYPE VALUE
--------- ----------- ------------------------------
spfile string
value=nothing then spfile
value=something pfile
A>
MULTIPLEXING(ADDITING) OF CONTROL FILE
i>shutdown db gracefully
shutdown immediate
ii>add controlfile at os level
[oracle12c@uit144 testdb]$ cp c2.ctl c3.ctl
iii>edit pfile
[oracle12c@uit144 dbs]$ vi inittest.ora
control_files = /u01/app/oracle12c/oradata/testdb/control.ctl,
/u01/app/oracle12c/oradata/testdb/c2.ctl,/u01/app/oracle12c/oradata/testdb/c3.ctl
iv>startup the database normal
ORA-00214: control file '/u01/app/oracle12c/oradata/testdb/control.ctl'
version 2843 inconsistent with file '/u01/app/oracle12c/oradata/testdb/c3.ctl'
version 2840
error due to changing time parameter file , db not shutdown.
SQL> startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
Database mounted.
Database opened.
SQL> show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle12c/oradata/wip
rodb/control.ctl, /u01/app/ora
cle12c/oradata/testdb/c2.ctl,
/u01/app/oracle12c/oradata/wi
prodb/c3.ctl
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
=========================================================
RECREATION OF CONTROL FILE
[oracle12c@uit144 trace]$ pwd
/u01/app/oracle12c/diag/rdbms/testdb/test/trace
SQL> alter database backup controlfile to trace;
Database altered.
oracle12c@uit144 testdb]$ mv c3.ctl /home/oracle12c/controlfilebackup/
[oracle12c@uit144 testdb]$ mv control.ctl /home/oracle12c/controlfilebackup/
[oracle12c@uit144 testdb]$
afiedt.buf c.sql dba_users.sql log.sql s.sql
a.txt dba_data_files.sql db_str.sql Music Templates
backup_own_purpose dba_extents.sql Desktop Pictures u1.sql
controlfilebackup dba_segments.sql Documents Public Videos
cr8dball.sql dba_ts1.sql Downloads runall.sql wipdb.sql
[oracle12c@uit144 ~]$ cd controlfilebackup/
[oracle12c@uit144 controlfilebackup]$ ls
c2.ctl
[oracle12c@uit144 controlfilebackup]$ ls
c2.ctl c3.ctl
[oracle12c@uit144 controlfilebackup]$ ls
c2.ctl c3.ctl control.ctl
[oracle12c@uit144 controlfilebackup]$
NO CONTROL FILE IN TESTDB LOCATION
Start the db at nomount
startup nomount
Done.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
SQL>
now go to SYS>>@/home/oracle12c/c.sql
SQL> @/home/oracle12c/c.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
===================================
RENAMING THE DATBASE
TAKE BACKUP OF CONTROLFILE
[oracle12c@uit144 dbs]$ vi inittest.ora
[oracle12c@uit144 dbs]$ cd
[oracle12c@uit144 testdb]$ rm c2.ctl c3.ctl control.ctl
SQL> startup nomount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 394266864 bytes
Database Buffers 226492416 bytes
Redo Buffers 3276800 bytes
SQL> @/home/oracle12c/c.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
SQL>
SQL> alter database open resetlog;
alter database open resetlog
*
ERROR at line 1:
ORA-02288: invalid OPEN mode
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$database;
NAME
---------
testDB1
================================
SCOTT USER file LOCATION
[oracle12c@uit144 admin]$ cp utlsampl.sql ~/demo.sql
[oracle12c@uit144 admin]$ pwd
/u01/app/oracle12c/product/12.1.0/db_1/rdbms/admin
[oracle12c@uit144 admin]$
=======================================
CHANGING GLOGIN STATUS
[oracle12c@uit144 admin]$ pwd
/u01/app/oracle12c/product/12.1.0/db_1/sqlplus/admin
[oracle12c@uit144 admin]$
SYS>>@u1
connecting to user1
Connected.
Done.
U1>>@s
Now connecting to oracle with sysdba privileges
Connected.
connected successfully
Enjoyed!!!!!!!!!!
SYS>>conn scott/tiger;
Connected.
SCOTT>>
ALTER SESSION SET nls_date_format = 'HH:MI:SS';
-- SET the SQLPROMPT to include the _USER, _CONNECT_IDENTIFIER
-- and _DATE variables.
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "
-- To set the number of lines to display in a report page to 24.
SET PAGESIZE 24
-- To set the number of characters to display on each report line to 78.
SET LINESIZE 78
-- To set the number format used in a report to $99,999.
SET NUMFORMAT $99,999
========================
SHOW ERRORLOGGING CONCEPT
select username,statement,message
from sperrorlog;
USERNAME
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
SYS
set errorlogging on
SP2-0310: unable to open file "dba_file.sql"
SYS
set errorlogging on
SP2-0310: unable to open file "dba_files.sql"
SYS
select username,statement,message
from sperrorlog;
ORA-00933: SQL command not properly ended
3 rows selected.
SYS>>
=====================================
STORE Command
During a given SQL*Plus session, it’s likely that you’ll need to change your environment settings in
order to run a specific SQL script or command. If you want to preserve these settings for future use,
you can do so with the help of the STORE command. Once you store the values in a script, you can run
116 CHAPTER 4 ■ U SING SQL*PLUS AND ORACLE EN TERPR ISE MANAGER
that script anytime to restore the original values of all variables. Thus, if you run a report that requires
changing some variable values, just run the script that contains the original values after you finish the
report to restore the original values of the variables.
The following example shows how to use the STORE command to save your SQL*Plus environment
settings:
SQL> STORE SET mysqlplus.sql
Created file mysqlplus.sql
Executing the previous command will result in the storing of all the current environmental
values in the file named sqlplus.sql. Once you store your favorite environment variables in a file,
you can easily reuse them anytime you want by simply executing the script. (I explain the execution
of SQL scripts in the following sections.) In order to restore the stored values of all system variables,
enter
SQL> START mysqlplus.sql
If you wish, you can just enter @mysqlplus.sql
SHOW USER
SHOW RECYCLEBIN
SHOW TTITLE
SHOW BTITLE
SHOW ALL
SHOW SGA
SHOW PARAMETERS
SHOW PARAMETERS MEMORY
SHOW SPPARAMETER DB_NAME
SHOW ERR PROCEDURE TEST_PROC
SQLPROMPT Command
SQL> SET SQLPROMPT '_CONNECT_IDENTIFIER > '
nick >
SQL> SET SQLPROMPT "_USER > "
APPOWNER >
SQL> SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
APPOWNER@nick >
SQL> SET SQLPROMPT "_USER _PRIVILEGE> "
SYS AS SYSDBA>
SQL> SET SQLPROMPT "_USER 'on' _DATE 'at' _CONNECT_IDENTIFIER > "
SYS on 20-JUN-05 at nick>
============================
HOST Command :
The HOST command enables you to use operating system commands from within SQL*Plus. You
may, for example, want to see if a file exists in a certain directory, or you may want to use the cp or
tar commands at the UNIX level and return to your SQL*Plus session to resume interacting with the
Oracle database.
Here is an example showing how to use the HOST command:
SQL> HOST cp /u01/app/oracle/new.sql /tmp
The HOST command in the preceding example will help you copy the new.sql file from the specified
directory to the tmp directory.
Just about any command you can use at the operating system level can be executed using the
HOST command. You can replace the HOST command with ! (bang, or exclamation point) to run operating
system commands from within SQL*Plus, as in the following example:
SQL> ! cp /u01/app/oracle/new.sql /tmp
■Note If you just type the command by itself, as in HOST or !, you’ll be transported to the operating system directory
from which you logged into the SQL*Plus session.
When you’re done with your operating system task, just type exit on the command line, and
you’ll return to the SQL*Plus session you just left. Here’s an example:
SQL> HOST
$ exit
SQL>
==================================
The ACCEPT and PROMPT commands are usually used together in a SQL script, typically to request
user input and save the input in variables that can be used later in the program. The following example
illustrates the use of these commands:
SQL> PROMPT 'Please enter your last name'
SQL> ACCEPT lastname CHAR FORMAT a20 alapati
==========================
SQL> SHOW PAUSE
PAUSE is OFF
SQL> SET PAUSE ON
SQL> SHOW PAUSE
PAUSE is ON and set to ""
SQL> BREAK ON DEPT_ID SKIP PAGE ON JOB_ID SKIP 1 ON SALARY SKIP 1
SQL> SELECT dept_id, job_id, salary, emp_name
FROM employees
WHERE salary > 50000
ORDER BY dept_id, job_id, salary, emp_name;
SQL> COLUMN dept FORMAT a15 HEADING 'Department'
SQL> COLUMN cost FORMAT $9999
SQL> COMPUTE AVG OF sales ON district
SQL> SELECT region, district, sales
FROM total_sales
WHERE district = 'NORTH';
SQL> REPFOOTER PAGE RIGHT 'END OF THE 1st QUARTER RESULTS REPORT'
SQL> REPHEADER PAGE CENTER '1st QUARTER RESULTS REPORT FOR 2008'
SQL> TTITLE 'Annual Financial Report for the Women's Club, 2008'
SQL> BTITLE '2005 Report'
Caution After you use the BTITLE and TTITLE commands, as well as many other SQL*Plus commands,
you have to manually turn them off to prevent all the ensuing SQL commands in that session from inheriting those
settings. For example, if you don’t turn the title off after you create a report, all the subsequent output for any
command will be printed with the same title.
Saving the SQL Buffer Contents to a File
SQL> SELECT username,process,sid,serial#
FROM v$session
WHERE status = 'ACTIVE'
.
SQL> SAVE status.sql
Created file status.sql
SQL> SAVE REPLACE status.sql
SQL> SAVE APPEND status.sql
=====================================
On UNIX systems, you can configure an environment variable called ORACLE_PATH to tell SQL*Plus
where to look for a script. This way, you can put all your routine SQL scripts in one location, and you
don’t need to specify the complete path for the file each time you want to execute an existing script.
On my UNIX servers, for example, this is how I set the variable:
$ export $ORACLE_PATH=/u01/app/oracle/admin/dba/sql
=====================================
Creating a Windows Batch Script
You can easily create a batch script in a Windows system to run your SQL*Plus commands. For instance,
say you have a script file called testscript.sql that provides information about the users in your
database. If you want to schedule this script to run at a specified time, you must first create a Windows
batch file that invokes the testscript.sql script file. You can then use the Windows at scheduling
utility, if you wish, to schedule the batch script.
Here’s a simple example. First create a batch file, named testbatch.bat, containing the following
(the testscript.sql script writes its output to the output.txt file):
sqlplus username/password@connect_identifier @C:\temp\testscript.sql
notepad.exe C:\temp\output.txt
The preceding batch file will start a SQL*Plus session, run the testscript.sql script, and output
the results of the testscript.sql file into the output.txt file using the notepad executable.
SQL> DEFINE dept = finance
SQL> UNDEFINE dept
SQL> col segment_name for a27
DEFINE owner = '&1'
SELECT segment_name,segment_type,extents
FROM dba_segments
WHERE owner = upper ('&owner')
AND extents > 10
AND segment_name NOT LIKE 'TMP%'
ORDER BY segment_type,extents desc
SQL> @extents.sql
Enter value for 1: system
SEGMENT_NAME SEGMENT_TYPE EXTENTS
HELP_TOPIC_SEQ INDEX 18
PRODUCT_PROFILE TABLE 22
SQL>
Predefined SQL*Plus Variables
SQL> DEFINE
DEFINE _DATE = "23-DEC-07" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl2" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release
11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options"
(CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)
SQL> exit
SQL>
======================
Listing SQL Commands
SQL> l
1 SELECT username, status, process, sid, serial#
2 FROM v$session
3* WHERE status = 'ACTIVE'
SQL>
If you want to see what’s in your SQL script before you execute it, load it from the operating
system into the SQL buffer by using the GET command, as follows:
SQL> GET status.sql
1 SELECT username,status,process,sid,serial#
2 FROM v$session
3* WHERE status = 'ACTIVE'
SQL> GET one_script.sql
1 @check.sql
2 @create_table.sql
3 @insert_table.sql
4* @create_constraint.sql
c/serial/serial#
SQL> SELECT username, profile
2 FROM dba_users
3 .
SQL> 1
1* SELECT username, profile
SQL> APPEND , created_date
1* select username, profile, created_date
SQL> l
1 SELECT username, profile, created_date
2* FROM dba_users
SQL>
SET SQLTERMINATOR OFF
SQL> INSERT INTO EMPLOYEES VALUES ('BEGIN
2 LOAD_PROCEDURE);
3* END')
SQL> ARCHIVE LOG START
Statement processed.
SQL> ARCHIVE LOG STOP
Statement processed.
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /a03/app/oracle/admin/NICKO/arch/
Oldest online log sequence 933
Next log sequence to archive 937
Current log sequence 937
SQL>
Here is an example that you are likely to be familiar with:
1. Set the environment variables:
SQL> SET ECHO OFF HEADING OFF FEEDBACK OFF
2. Name a spool file, to which the output of the first script will be written:
SQL> SPOOL test.txt
3. Execute the SQL that creates more SQL:
SQL> SELECT 'grant connect, resource to '||username||';' FROM dba_users;
This is part of the output of the preceding command:
GRANT CONNECT, RESOURCE TO DBA1;
GRANT CONNECT, RESOURCE TO MAMIDI;
GRANT CONNECT, RESOURCE TO JEFFRESS;
GRANT CONNECT, RESOURCE TO CAMPBELL;
GRANT CONNECT, RESOURCE TO ALAPATI;
GRANT CONNECT, RESOURCE TO BOLLU;
GRANT CONNECT, RESOURCE TO BOGAVELLI;
SQL> SPOOL OFF
4. The spooled script will have captured the preceding commands. Now run that script
(test.txt in this example):
SQL> @test.txt
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
SQL>s
===========================================================
No comments:
Post a Comment