Oracle Application DBA Information: PRIVILEGES MANAGEMENT & TABLESPACE MANAGEMENT ( All Oracle Database Version )

DBA_TAB_PRIVS - which users/roles are granted which object privileges (select on user2.table)

DBA_SYS_PRIVS - which users/roles are granted which system (create session, drop any table, etc)

DBA_ROLES - which users/roles are granted which roles

SQL> desc dba_role_privs
Name Null? Type
-------------------------------- ----------------- ------------------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)

Which user assign DBA Role

SQL> select * from dba_role_privs where granted_role='DBA';

GRANTEE GRANTED_ROLE ADM DEF
------------------- ------------------------- --- ---
SYS DBA YES YES
SYSTEM DBA YES YES

SQL>

If you want to check schema quota limitation of tablespace

SQL> SELECT grantee , privilege FROM dba_sys_privs WHERE grantee = 'SCOTT' ORDER BY privilege;

GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
SCOTT UNLIMITED TABLESPACE

SQL>
SQL> SELECT grantee , COUNT(privilege) FROM dba_sys_privs GROUP BY grantee;

GRANTEE COUNT(PRIVILEGE)
------------------------------ ----------------
EXP_FULL_DATABASE 11
AQ_ADMINISTRATOR_ROLE 6
OWBSYS_AUDIT 2
MDSYS 20
DIP 1
DBA 201
OEM_ADVISOR 3
SPATIAL_WFS_ADMIN_USR 8
RECOVERY_CATALOG_OWNER 11
SCHEDULER_ADMIN 6
OLAPSYS 13
SPATIAL_CSW_ADMIN_USR 8
OWBSYS 22
HR 7
OLAP_USER 8
FLOWS_FILES 1
RESOURCE 8
CTXSYS 9
OUTLN 3
IMP_FULL_DATABASE 80
EXFSYS 9
OWB$CLIENT 11
APEX_030200 26
DATAPUMP_EXP_FULL_DATABASE 2
SCOTT 1
SYSTEM 5
ORACLE_OCM 1
CONNECT 1
DBSNMP 4
SYSMAN 7
OE 7
SH 12
ORDSYS 1
MDDATA 1
APEX_PUBLIC_USER 1
PM 1
XDB 10
OLAP_DBA 27
IX 17
BI 9
JAVADEBUGPRIV 2
ORDDATA 1
APPQOSSYS 3
SYS 200
DATAPUMP_IMP_FULL_DATABASE 14
OEM_MONITOR 7
WMSYS 30
MGMT_USER 1
SI_INFORMTN_SCHEMA 1
ANONYMOUS 1

50 rows selected.

SQL>

SQL> set long 2000

SQL> run

1 select dbms_metadata.get_ddl( 'USER', 'SCOTT' ) || '/' from dual

2 UNION ALL

3 select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SCOTT' ) || '/' from dual

4 UNION ALL

5 select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SCOTT' ) || '/' from dual

6 UNION ALL

7* select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SCOTT' ) || '/' from dual

DBMS_METADATA.GET_DDL('USER','SCOTT')||'/'

--------------------------------------------------------------------------------

CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:E3A82E7110775246280D51FE52340AB54

795E56E35D44FAD024CB7B92EDF;F894844C34402B67'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"/

GRANT UNLIMITED TABLESPACE TO "SCOTT"/

ERROR:

ORA-31608: specified object of type OBJECT_GRANT not found

ORA-06512: at "SYS.DBMS_METADATA", line 5805

ORA-06512: at "SYS.DBMS_METADATA", line 8492

ORA-06512: at line 1

SQL> SELECT PRIVILEGE
FROM ALL_TAB_PRIVS_RECD
WHERE PRIVILEGE = 'SELECT'
AND TABLE_NAME = 'GN_VERSION'
AND OWNER = 'USER_A'
UNION ALL
SELECT PRIVILEGE
FROM SESSION_PRIVS
WHERE PRIVILEGE = 'SELECT ANY TABLE';
2 3 4 5 6 7 8 9
PRIVILEGE
----------------------------------------
SELECT ANY TABLE
SQL> SELECT username, privilege FROM USER_SYS_PRIVS;

USERNAME PRIVILEGE
------------------------------ ----------------------------------------
SYS AUDIT SYSTEM
SYS ALTER SESSION
SYS ALTER ROLLBACK SEGMENT
SYS ALTER ANY CLUSTER
SYS CREATE ANY INDEX

........... 200 rows

SQL> select

lpad(' ', 2*level) || granted_role "User, his roles and privileges"

from

(

/* THE USERS */

select

null grantee,

username granted_role

from

dba_users

where

username like upper('%&enter_username%')

/* THE ROLES TO ROLES RELATIONS */

union

select

grantee,

granted_role

from

dba_role_privs

/* THE ROLES TO PRIVILEGE RELATIONS */

union

select

grantee,

privilege

from

dba_sys_privs

)

start with grantee is null

connect by grantee = prior granted_role;

If you want to check Tablespace free space of other information

SQL> SELECT a.tablespace_name,

ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,

c.BYTES / 1024 / 1024 space_allocated,

ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,

ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free,

c.DATAFILES

FROM dba_tablespaces a,

( SELECT tablespace_name,

SUM (BYTES) BYTES

FROM dba_free_space

GROUP BY tablespace_name

) b,

( SELECT COUNT (1) DATAFILES,

SUM (BYTES) BYTES,

tablespace_name

FROM dba_data_files

GROUP BY tablespace_name

) c

WHERE b.tablespace_name(+) = a.tablespace_name

AND c.tablespace_name(+) = a.tablespace_name

ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;

TABLESPACE_NAME PERCENTAGE_USED SPACE_ALLOCATED SPACE_USED SPACE_FREE DATAFILES

------------------------------ --------------- --------------- ---------- ---------- ----------

EXAMPLE 98.96 313.125 309.88 3.25 1

SYSTEM 98.63 750 739.75 10.25 1

SYSAUX 94.81 520 493 27 1

USERS 81.25 5 4.06 .94 1

UNDOTBS1 32.31 65 21 44 1

TEMP 0

6 rows selected.

SQL>

If you want to check and Add Datafile in Tablespace

SQL> col file_name format a50

SQL> run

1 SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES

2* WHERE TABLESPACE_NAME IN ('USERS','SYSTEM')

TABLESPACE_NAME FILE_NAME

------------------------------ --------------------------------------------------

USERS /u01/app/oracle/oradata/orcl/users01.dbf

SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf

SQL>

SQL> ALTER TABLESPACE USERS ADD DATAFILE '/u01/app/oracle/oradata/orcl/users02.dbf' size 2048m ;

Tablespace altered.

SQL> SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME IN ('USERS','SYSTEM') ORDER BY 1;

TABLESPACE_NAME FILE_NAME

------------------------------ --------------------------------------------------

USERS /u01/app/oracle/oradata/orcl/users01.dbf

USERS /u01/app/oracle/oradata/orcl/users02.dbf

SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf

SQL>

If you want to check temp tablespace free space & other information and add tempfile in temp tablespace

SQL> col file_name format a40
SQL> run
1 select
2 a.tablespace_name,
3 a.file_name,
4 a.bytes/1024 c3,
5 b.free_bytes/1024
6 FROM
7 dba_temp_files a,
8 (SELECT file_id, SUM(bytes) free_bytes
9 FROM dba_free_space b GROUP BY file_id) b
10 WHERE
11 a.file_id=b.file_id
12 ORDER BY
13* a.tablespace_name

TABLESPACE_NAME FILE_NAME C3 B.FREE_BYTES/1024
------------------------------ ---------------------------------------- ---------- -----------------
TEMP /u01/app/oracle/oradata/orcl/temp01.dbf 20480 10496

SQL> alter tablespace temp add tempfile ' /u01/app/oracle/oradata/orcl/temp02.dbf ' size 2048m ;

Tablespace altered.

SQL> col name format a50
SQL> run
1* select file#,status,name from v$tempfile

FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 ONLINE /oradata/proddb/data/temp01.dbf
2 ONLINE /oradata/proddb/data/temp02.dbf
3 ONLINE /oradata/proddb/data/temp03.dbf

SQL> col DESCRIPTION format a40
SQL>
SQL> SELECT * FROM database_properties WHERE property_name like '%TABLESPACE'

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ---------------------------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP ID of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE SYSTEM Default Permanent Tablespace ID

CREATE TEMPORARY TABLESPACE temp3 TEMPFILE '/oradata/proddb/data/temp_01' SIZE 1024M TABLESPACE GROUP temp;

alter tablespace temp3 add tempfile '/oradata/proddb/data/temp_02' SIZE 1024M;

SET Default TEMPORARY TABLESPACE

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp3;

SQL> SELECT * FROM dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP TEMP1
TEMP TEMP2

Delete temp tablespace with tempfile

DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

sql> select TABLESPACE_NAME,FILE_NAME from dba_temp_files

TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------
TEMP3 /oradata/proddb/data/temp_02
TEMP3 /oradata/proddb/data/temp_01

Oracle Application DBA Information

Tuesday, 3 May 2016

PRIVILEGES MANAGEMENT & TABLESPACE MANAGEMENT ( All Oracle Database Version )

No comments:

Post a Comment