ORA-12916: Cannot use default permanent tablespace with this release

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200

SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

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

DEFAULT_TEMP_TABLESPACE TEMP1 ID of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE SYSTEM Default Permanent Tablespace ID





SQL> ALTER DATABASE DEFAULT TABLESPACE USERS ;

ALTER DATABASE DEFAULT TABLESPACE USERS

*

ERROR at line 1:

ORA-12916: Cannot use default permanent tablespace with this release




if " *.compatible='9.2.0.0.0' " this parameter set then you can't change .

Is My Linux 32 bit or 64 bit ?

Quick way to find out

anuj-amd-02:/home/anujs/Downloads/Goldengate # uname -m
x86_64


oracle@anuj:~> uname -m
i686

How to grant select on v$session (or v$ ) views

v$session is synonyms of v_$session .


SQL> col OWNER format a15

SQL> col OBJECT_NAME like OWNER

SQL> col OBJECT_TYPE like OWNER



SQL> r

1* select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V$SESSION'



OWNER OBJECT_NAME OBJECT_TYPE

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

PUBLIC V$SESSION SYNONYM


SQL> grant select on v$session to prod1;

grant select on v$session to cccprod1
*
ERROR at line 1:

ORA-02030: can only select from fixed tables/views


you can't give grant to synonyms



SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V_$SESSION';



OWNER OBJECT_NAME OBJECT_TYPE

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

SYS V_$SESSION VIEW





SQL> grant select on v_$session to prod1;



Grant succeeded.

Oracle ORA_ROWSCN Pseudocolumn and AS OF Flashback Query

SQL> CREATE TABLE anuj (c1 NUMBER, c2 VARCHAR2(16), c3 DATE) ;


SQL> INSERT INTO anuj VALUES (1,'A',SYSDATE);



1 row created.



SQL> INSERT INTO anuj VALUES (1,'B',SYSDATE);



1 row created.



SQL> commit;



Commit complete.



SQL> SELECT ORA_ROWSCN, a.* FROM anuj a;



ORA_ROWSCN C1 C2

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

C3

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

45111390 1 A

18-AUG-10



45111390 1 B

18-AUG-10

Oracle The OPatch Utility to check oracle patch information

cd $ORACLE_HOME
oracle@solomon:/opt/oracle/product/10.2/OPatch>

cd OPatch

oracle@solomon:/opt/oracle/product/10.2/OPatch> ./opatch lsinventory –detail –oh /opt/oracle/product/10.2
Invoking OPatch 10.2.0.4.2

Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/product/10.2
Central Inventory : /opt/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.2
OUI version : 10.2.0.4.0
OUI location : /opt/oracle/product/10.2/oui
Log file location : /opt/oracle/product/10.2/cfgtoollogs/opatch/opatch2010-08-16_14-26-22PM.log

Lsinventory Output file location : /opt/oracle/product/10.2/cfgtoollogs/opatch/lsinv/lsinventory2010-08-16_14-26-22PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (3):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Products 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0
There are 3 products installed in this Oracle Home.


Interim patches (1) :

Patch 8340387 : applied on Tue Apr 14 11:50:02 GMT 2009
Created on 24 Mar 2009, 00:28:01 hrs PST8PDT
Bugs fixed:
8340387, 7528105, 7196894



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

OPatch succeeded.



For Path !!!
PATH=$PATH:$ORACLE_HOME/OPatch




env|grep -i ora
HOSTNAME=cloud-ora
ORACLE_UNQNAME=ora12
USER=oracle
LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/db_1/lib:/lib:/usr/lib
ORACLE_SID=ora12c
ORACLE_BASE=/u01/app/oracle
MAIL=/var/spool/mail/oracle
PATH=/u01/app/oracle/product/12.1.0/db_1/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.1.0/db_1/OPatch
PWD=/home/oracle
HOME=/home/oracle
LOGNAME=oracle
CLASSPATH=/u01/app/oracle/product/12.1.0/db_1/jlib:/u01/app/oracle/product/12.1.0/db_1/rdbms/jlib
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1


PATH=$PATH:$ORACLE_HOME/OPatch



opatch lsinventory -oh /u01/app/oracle/product/12.1.0/db_1 -detail
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2018-01-06_12-23-36PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2018-01-06_12-23-36PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 12c                                                  12.1.0.2.0
There are 1 products installed in this Oracle Home.


Installed Products (135):

Assistant Common Files                                               12.1.0.2.0
Buildtools Common Files                                              12.1.0.2.0
Cluster Verification Utility Common Files                            12.1.0.2.0
Database Configuration and Upgrade Assistants                        12.1.0.2.0
Database Migration Assistant for Unicode                             12.1.0.2.0
Database SQL Scripts                                                 12.1.0.2.0
Database Workspace Manager                                           12.1.0.2.0

opatch util listorderedinactivepatches

--  opatch util deleteinactivepatches

opatch util cleanup

$ORACLE_HOME/OPatch/opatch lsinventory | grep -E "(^Patch.*applied)|(^Sub-patch)"

sqlplus / as sysdba hanged ...

if sqlplus / as sysdba hanged .... ( and database down ) .. then try this




oracle@solomon:~> rman

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 16 11:02:28 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN> startup;

Oracle instance started
database mounted
database opened

Total System Global Area 608174080 bytes

Fixed Size 1268896 bytes
Variable Size 213910368 bytes
Database Buffers 385875968 bytes
Redo Buffers 7118848 bytes

Oracle data pump export through cron

# !/bin/bash

TODAY=`date`

ORACLE_SID=cccdb ; export ORACLE_SID

ORACLE_BASE=/opt/oracle ; export ORACLE_BASE

ORACLE_HOME=/opt/oracle/product/10.2 ; export ORACLE_HOME

PATH=${ORACLE_HOME}/bin:$PATH ; export PATH

EXPFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.dmp; export EXPFILE

ERRFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.err; export ERRFILE

LOGFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.log ; export LOGFILE

SUCC=/opt/oracle/backup/DataPump/Backup/SUCC.txt ; export SUCC



# echo $EXPFILE



find /opt/oracle/backup/DataPump/Backup -name "*.err" -ctime +1 -exec rm {} \;





find /opt/oracle/backup/DataPump/Backup -name "*.dmp*" -ctime +1 -exec rm {} \;



ps -ef | grep -i smon|grep -v grep|wc -l> oracle.exp



if [ `cat oracle.exp|grep -v grep|grep -i "1"|wc -l` -eq 1 ]; then



[ -f $ERRFILE ]; rm $ERRFILE



expdp "'/ as sysdba'" directory=datapump DUMPFILE=$EXPFILE SCHEMAS=CCCPROD1,CCCUAT2,ESEARCH logfile=$LOGFILE EXCLUDE=STATISTICS

# parallel=4



fi



# find /opt/oracle/backup/DataPump/Backup -name "$ORACLE_SID.export.*" -mtime +2 -exec rm {} \;

List all srvctl commands

List all srvctl commands

srvctl –h

Shutdown service

srvctl stop service –d database_name

Shutdown the database on all nodes

srvctl stop database –d database_name

Shutdown a specific instance

srvctl stop instance –d database_name -i instance_name

Shutdown nodeapps (includes listener)

srvctl stop nodeapps –n node_name

! node_name is usually the hostname of the machine

Shutdown the listener

srvctl stop listener –n node_name

Shutdown ASM

srvctl stop asm –n node_name

To Startup, replace stop with start.

If you shutdown the database it will stop the service since the service is dependent upon the database. The reverse is also true. If you startup the service it will implicitly startup the database.

To check to see if your services are running.

$CRS_HOME/bin/crs_stat –t

If you want more detailed information, drop the –t.

$CRS_HOME/bin/crs_stat

You may include the $CRS_HOME/bin in your $PATH for ease of use

=====================================================================================
crs_stat command



crs_stat | awk -F= '/NAME=/{n=$2}/TYPE=/{t=$2}/TARGET=/{g=$2}/STATE=/{s=$2; printf("%-30s%-30s%-10s%-30s\n", n,t,g,s)}'
ora.DATA.dg ora.diskgroup.type ONLINE ONLINE on oracle-host01
ora.LISTENER.lsnr ora.listener.type ONLINE ONLINE on oracle-host01
ora.asm ora.asm.type ONLINE ONLINE on oracle-host01
ora.cssd ora.cssd.type ONLINE ONLINE on oracle-host01
ora.diskmon ora.diskmon.type ONLINE ONLINE on oracle-host01
ora.evmd ora.evm.type ONLINE ONLINE on oracle-host01
ora.ons ora.ons.type ONLINE ONLINE on oracle-host01
ora.orc11202.db ora.database.type ONLINE ONLINE on oracle-host01

Oracle Invalid objects .... 

object info 

define OBJECT_NAME='xxxxxxx'

set linesize 300 

col OWNER for a15

col OBJECT_NAME for a25

select  *   from   (select 1 con_id,OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where OBJECT_NAME='&OBJECT_NAME'

                    union 

                    select con_id, OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from cdb_objects where OBJECT_NAME='&&OBJECT_NAME'

                  )

;

======

@$ORACLE_HOME/rdbms/admin/utlrp.sql

utlprp.sql =

DECLARE

   threads pls_integer := &&1;

BEGIN

   utl_recomp.recomp_parallel(threads);

END;

/

declare

threads pls_iteger :=&&1;

begin 

utl_recomp.recomp_parallel(threds, flags =>utl_recomp.oracle_maintained);

end;

or 

define threds=4

exec utl_recomp.recomp_parallel(&threds, flags =>utl_recomp.oracle_maintained);  --- <<<<< Only for Oracle manage objects 

======================

set recsep off

column invalid_object format A30

column likely_reason format A35 word_wrapped

break on type on invalid_object

select owner || '.' || object_name invalid_object,'--- ' || object_type || ' ---' likely_reason from dba_objects

where status = 'INVALID'

union

select d.owner || '.' || d.name,'Non-existent referenced db link ' || d.referenced_link_name from dba_dependencies d

where not exists (select 'x'from dba_db_links

                  where owner in ('PUBLIC', d.owner) 

                  and db_link = d.referenced_link_name

                 )

and d.referenced_link_name is not null

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')

union

select d.owner || '.' || d.name,'Depends on invalid ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d

where ro.status = 'INVALID'

and ro.owner    = d.referenced_owner

and ro.object_name = d.referenced_name

and ro.object_type = d.referenced_type

and d.referenced_link_name is null

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from  dba_objects where status = 'INVALID')

union

select d.owner || '.' || d.name,'Depends on newer ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d,dba_objects o

where nvl(ro.last_ddl_time, ro.created) > nvl(o.last_ddl_time, o.created)

and ro.owner = d.referenced_owner

and ro.object_name = d.referenced_name

and ro.object_type = d.referenced_type

and d.referenced_link_name is null

and d.owner  = o.owner

and d.name   = o.object_name

and d.type   = o.object_type

and o.status = 'INVALID'

union

select d.owner || '.' || d.name,'Depends on ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name|| decode(d.referenced_link_name,NULL, '','@' || d.referenced_link_name) from dba_dependencies d

where d.referenced_owner != 'PUBLIC' -- Public synonyms generate noise

and d.referenced_type = 'NON-EXISTENT'

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')

union

select d.owner || '.' || d.name invalid_object,'No privilege on referenced ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d 

where not exists (select 'x' from dba_tab_privs p

                  where p.owner = d.referenced_owner

                  and p.table_name = d.referenced_name

                  and p.grantee in ('PUBLIC', d.owner)

                  )

and ro.status = 'VALID'

and ro.owner  = d.referenced_owner

and ro.object_name = d.referenced_name

and d.referenced_link_name is not null

and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')

union

select o.owner || '.' || o.object_name,e.text from dba_errors e,dba_objects o

where e.text like 'PLS-%'

and e.owner  = o.owner

and e.name   = o.object_name

and e.type   = o.object_type

and o.status = 'INVALID';

===============

Components valid / invalid 

set lines 90

col version  for a12

col comp_id  for a8

col comp_name  for a35

col status for  a12

col schema like version

select comp_id,schema,status,version,comp_name from dba_registry 

order by 1;

============

set lines 120

col status   for a9

col object_type  for a20;

col owner.object for a50

select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"

from dba_objects

where 0=0 

and status != 'VALID' 

and object_name not like 'BIN$%' 

order by 4,2;

=====

set serveroutput on

BEGIN

   FOR r

   IN (  SELECT      'alter '

                  || REPLACE (object_type, 'BODY', '')

                  || ' '

                  || owner

                  || '.'

                  || object_name

                  || ' compile '

                  || DECODE (object_type, 'PACKAGE BODY', 'BODY')

                     stmt

           FROM   dba_objects

          WHERE   status = 'INVALID'

       ORDER BY   owner,

               DECODE (object_type,

                          'SYNONYM', 1,

                          'VIEW', 2,

                          'FUNCTION', 3,

                          'PROCEDURE', 4,

                          'PACKAGE', 7,

                          'PACKAGE BODY', 8,

                          9))

   LOOP

      BEGIN

        -- EXECUTE IMMEDIATE (r.stmt);

dbms_output.put_line(r.stmt||' ;');

      EXCEPTION

         WHEN OTHERS

         THEN

            NULL;

      END;

   END LOOP;

END;

/

===

SET PAGESIZE 9999
	clear columns
	clear breaks
	clear computes
	column owner format a25 heading 'Owner'
	column object_name format a30 heading 'Object Name'
	column object_type format a20 heading 'Object Type'
	column count format 999,999 heading 'Count'
	break on owner skip 2 on report
	compute sum label "Count: " of count on owner
	compute sum label "Grand Total: " of count on report
	SELECT
	owner
	, object_type
	, count(*) Count
	FROM dba_objects
	WHERE status <> 'VALID'
	GROUP BY owner, object_type
	/

=====

set pagesize 1000  LINESIZE 300

col COMP_ID format A9

col COMP_NAME format A40

col STATUS format A15

col VERSION format A12

col owner format a30

col object_name format a30

col object_type format a30

col COMMENTS for a60

col ACTION_TIME for a30

col VERSION for a25

--SPOOL DB_DD_CHECK.TXT

prompt /* Database identification */

select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;

select * from V$version where banner like 'Oracle Database%';

select INSTANCE_NAME, to_char(STARTUP_TIME,'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME" from v$instance;

prompt  /* Database Component Registry status */

select comp_id, comp_name, status, version from dba_registry;

prompt  /* Level of patch in the database */

select * from dba_registry_history;

prompt /* INVALID objects count, by type -in detail */

select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';

select owner, object_type, count(*) from dba_objects where status !='VALID' group by owner, object_type order by owner, object_type;

select owner, object_type, object_name, status from dba_objects where status !='VALID' order by owner, object_type, object_name;

Oracle Number of Objects in Tablespaces

Oracle  Users and Number of Objects in Tablespaces

set pagesize 60  echo off
column tablespace_name         format a20
column owner format a20
column objects format a20
break on owner on tablespace_name 
spool users_ts
select substr(owner,1,20) owner,substr(tablespace_name,1,32) tablespace_name,count(*)||decode(count(*),1,' table',' tables') objects
from sys.dba_tables
group by substr(owner,1,20),substr(tablespace_name,1,32) 
union
select substr(owner,1,20) owner,substr(tablespace_name,1,32) tablespace_name,count(*)||decode(count(*),1,' index',' indexes') objects
from sys.dba_indexes
group by substr(owner,1,20),substr(tablespace_name,1,32) 
/

spool off

OWNER                TABLESPACE_NAME      OBJECTS

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

ANUJ                 USERS                2 tables

APEX_040200          SYSAUX               1518 indexes

                                          450 tables

                                          2 tables

AUDSYS                                    1 table

                                          4 indexes

Oracle Instance up time

select
trunc(sysdate - startup_time) days,
trunc(mod(24 * (sysdate - startup_time), 24)) hours,
round(mod(60 * 24 * (sysdate - startup_time), 60)) minutes
from
sys.v_$instance
/

Oracle Col name search

col structure for a10
col "Data type" for a12

accept x_owner prompt "[= allowed ] Owner : ";
accept x_column prompt "[= allowed ] Column Name : ";

select owner,
table_name "Table name",
column_name "Column name",
data_type "Data type",
decode( upper( data_type ), 'NUMBER', to_char( data_precision ) || ',' || to_char( data_scale ),
'DATE', ' ',
'LONG', ' ',
to_char( data_length ) ) "Structure",
nullable "Nullable"
from dba_tab_columns
where ( ( substr( upper( nvl( '&&x_owner', 'x' ) ), 1, 1 ) <> '=' and owner like upper( '%&&x_owner%' ) )
or
( substr( upper( '&&x_owner' ), 1, 1 ) = '=' and owner = upper( substr( '&&x_owner', 2 ) ) )
)
and ( ( substr( upper( nvl( '&&x_column', 'x' ) ), 1, 1 ) <> '=' and column_name like upper( '%&&x_column%' ) )
or
( substr( upper( '&&x_column' ), 1, 1 ) = '=' and column_name = upper( substr( '&&x_column', 2 ) ) )
)
order by owner, table_name, column_name
/

Oracle user details

accept xs_username prompt '[= allowed ] User : ';

declare
ls_exact varchar2(1) := 'N';
ls_username varchar2( 30 ) := upper( '&xs_username' );

cursor csr_user_dets is
select username, default_tablespace, temporary_tablespace, created, profile
from dba_users
where ( ( ls_exact <> 'Y'
and username like upper( '%&xs_username%' ) )
or
( ls_exact = 'Y'
and username = ls_username )
)
and username not in ( 'SYS', 'SYSTEM' )
order by username;

cursor csr_sys_privs( xs_username in varchar2 ) is
select *
from dba_sys_privs
where grantee = xs_username
order by privilege;

cursor c_dba_role_privs is
select *
from dba_role_privs
where ( ( ls_exact <> 'Y'
and grantee like upper( '%&xs_username%' ) )
or
( ls_exact = 'Y'
and grantee = ls_username )
)
order by grantee;

cursor c_ts_quotas is
select *
from dba_ts_quotas
where ( ( ls_exact <> 'Y'
and username like upper( '%&xs_username%' ) )
or
( ls_exact = 'Y'
and username = ls_username )
)
order by username;

i_loop_counter integer;
s_sql_text varchar2( 200 );

begin

if substr( '&xs_username', 1, 1) = '=' then
ls_exact := 'Y';
ls_username := substr( upper( '&xs_username' ), 2 );
else
ls_exact := 'N';
ls_username := upper( '&xs_username' );
end if;
dbms_output.put_line( ls_exact || ' ' || ls_username );

if ls_exact = 'Y' then
dbms_output.put_line( 'User Details : Search for exact match on ' || '''' || ls_username || '''' || chr(10) );
else
dbms_output.put_line( 'User Details : Search for all like ' || '''' || ls_username || '''' || chr(10) );
end if;

dbms_output.put_line( rpad( 'User Name', 20 ) || ' ' ||
rpad( 'Default T Space', 25 ) || ' ' ||
rpad( 'Temp T Space', 25 ) || ' ' ||
'Created ' || ' ' ||
rpad( 'Profile', 25 ) );
dbms_output.put_line( rpad( '---------', 20 ) || ' ' ||
rpad( '---------------', 25 ) || ' ' ||
rpad( '------------', 25 ) || ' ' ||
'------- ' || ' ' ||
rpad( '-------', 25 ) );

for i_csr_user_dets in csr_user_dets loop
dbms_output.put_line( rpad( i_csr_user_dets.username, 20 ) || ' ' ||
rpad( i_csr_user_dets.default_tablespace, 25 ) || ' ' ||
rpad( i_csr_user_dets.temporary_tablespace, 25 ) || ' ' ||
to_char( i_csr_user_dets.created, 'dd/mm/yyyy hh24:mi' ) || ' ' ||
rpad( i_csr_user_dets.profile, 25 ) );
end loop;

dbms_output.put_line( chr(9) );
dbms_output.put_line( 'System privileges' );
for i_csr_user_dets in csr_user_dets loop
i_loop_counter := 0;
for i_csr_sys_privs in csr_sys_privs( i_csr_user_dets.username ) loop
if i_loop_counter = 0 then
s_sql_text := rpad( i_csr_user_dets.username, 14 ) || chr(9);
else
s_sql_text := chr(9) || chr(9);
end if;
if i_csr_sys_privs.admin_option = 'YES' then
s_sql_text := s_sql_text || i_csr_sys_privs.privilege || ' with admin';
else
s_sql_text := s_sql_text || i_csr_sys_privs.privilege || ' (no admin)';
end if;
dbms_output.put_line( s_sql_text );
i_loop_counter := i_loop_counter + 1 ;
end loop;
if i_loop_counter > 0 then
dbms_output.put_line( chr(9) );
end if;
end loop;

dbms_output.put_line( chr(9) );
dbms_output.put_line( 'Roles' );

dbms_output.put_line( rpad( 'Grantee', 30 ) || ' ' ||
rpad( 'Role', 30 ) || ' ' ||
rpad( 'Admin', 4 ) || ' ' ||
rpad( 'Default', 4 ) );

dbms_output.put_line( rpad( '-------', 30 ) || ' ' ||
rpad( '----', 30 ) || ' ' ||
rpad( '-----', 4 ) || ' ' ||
rpad( '-------', 4 ) );

for i_c_dba_role_privs in c_dba_role_privs loop
dbms_output.put_line( rpad( i_c_dba_role_privs.grantee, 30 ) || ' ' ||
rpad( i_c_dba_role_privs.granted_role, 30 ) || ' ' ||
rpad( i_c_dba_role_privs.admin_option, 4 ) || ' ' ||
rpad( i_c_dba_role_privs.default_role, 4 ) );

end loop;

dbms_output.put_line( chr(10) );

dbms_output.put_line( 'Tablespace quotas' || chr(10) );

dbms_output.put_line( rpad( 'Tablespace', 30 ) || ' ' ||
rpad( 'User', 30 ) || ' ' ||
lpad( 'Bytes', 14 ) || ' ' ||
lpad( 'Max Bytes', 14 ) || ' ' ||
lpad( 'Blocks', 12 ) || ' ' ||
lpad('Max Blocks', 12 ) );

dbms_output.put_line( rpad( '-------', 30 ) || ' ' ||
rpad( '----', 30 ) || ' ' ||
lpad( '--------', 14 ) || ' ' ||
lpad( '------------', 14 ) || ' ' ||
lpad( '------', 12 ) || ' ' ||
lpad( '----------', 12 ) );

for i_c_ts_quotas in c_ts_quotas loop
dbms_output.put_line( rpad( i_c_ts_quotas.tablespace_name, 30 ) || ' ' ||
rpad( i_c_ts_quotas.username, 30 ) || ' ' ||
to_char( ( i_c_ts_quotas.bytes ), '99999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.max_bytes ), '99999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.blocks ), '999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.max_blocks ), '999,999,999' ) );


end loop;


end;
/

user detail

col external_name for a20

select username, account_status, default_tablespace, temporary_tablespace, created, profile,
password, lock_date, expiry_date, user_id, initial_rsrc_consumer_group, external_name
from dba_users
where username like upper( '%&LikeUser%' )
order by username
/

Oracle User Object Summary

prompt SYNONYM, CLUSTER, CONSUMER GROUP, INDEX PARTITION, TABLE PARTITION, LIBRARY, QUEUE
prompt
accept x_exclude prompt "Exclude above types ? : "
accept x_sys_tem prompt "Include SYS/SYSTEM ? : "
accept x_all_users prompt "Include users without objects ? : "

declare
ls_exclude varchar2( 1 ) := upper( substr( nvl( '&x_exclude', 'N' ), 1, 1 ) );
ls_all_users varchar2( 1 ) := upper( substr( nvl( '&x_all_users', 'N' ), 1, 1 ) );
ls_sys_tem varchar2( 1 ) := upper( substr( nvl( '&x_sys_tem', 'N' ), 1, 1 ) );

cursor c_users is
select *
from dba_users
where ( username not in ( 'SYS', 'SYSTEM' ) or ls_sys_tem <> 'N' )
order by username;

cursor c_object_types is
select unique object_type
from dba_objects
where ( ls_exclude <> 'Y'
or object_type not in ( 'SYNONYM', 'CLUSTER', 'CONSUMER GROUP', 'INDEX PARTITION', 'TABLE PARTITION',
'LIBRARY', 'QUEUE' )
)
and ( owner not in ( 'SYS', 'SYSTEM' ) or ls_sys_tem <> 'N' )
order by 1;


cursor c_u_objects ( x_user in varchar2, x_obj_type in varchar2 ) is
select count(*)
from dba_objects
where owner = x_user
and object_type = x_obj_type;
li_num_objects integer;

type table_obj_type is table of dba_objects.object_type%type index by binary_integer;
t_obj_type table_obj_type;
li_counter integer := 1;
li_no_users integer := 0;

li_column_width integer := 8;
ls_header1 varchar2( 255 );
ls_header2 varchar2( 255 );
ls_header3 varchar2( 255 );
ls_out_string varchar2( 255 );

begin

for i_c_object_types in c_object_types loop
t_obj_type( t_obj_type.count+1 ) := i_c_object_types.object_type;
end loop;

ls_header1 := ' ';
ls_header2 := ' ';
ls_header3 := ' ';

for x in 1..t_obj_type.count loop
ls_header1 := ls_header1 || lpad( t_obj_type( x ), li_column_width ) || ' ';
ls_header2 := ls_header2 || lpad( nvl( substr( t_obj_type( x ), li_column_width + 1 ), ' ' ), li_column_width ) || ' ';
ls_header3 := ls_header3 || lpad( rpad( '-', length( t_obj_type( x ) ), '-' ), li_column_width ) || ' ';
end loop;

dbms_output.put_line( ls_header1 );
dbms_output.put_line( ls_header2 );
dbms_output.put_line( ls_header3 );

for i_c_users in c_users loop

ls_out_string := rpad( i_c_users.username, 15 );
li_counter := 0;

for x in 1..t_obj_type.count loop
li_num_objects := 0;
open c_u_objects ( i_c_users.username, t_obj_type( x ) );
fetch c_u_objects into li_num_objects;
close c_u_objects;
li_counter := li_counter + li_num_objects;
if li_num_objects > 0 then
ls_out_string := ls_out_string || lpad( to_char( li_num_objects, '999,999' ), li_column_width ) || ' ';
else
ls_out_string := ls_out_string || lpad( ' .', li_column_width ) || ' ';
end if;
end loop;

if li_counter > 0 or ls_all_users <> 'N' then
li_no_users := li_no_users + 1;
dbms_output.put_line( ls_out_string || lpad( to_char( li_counter, '999,999' ), li_column_width ) );
end if;

end loop;

dbms_output.put_line( chr(10) );
dbms_output.put_line( 'No of users displayed : ' || li_no_users );



end;
/

Oracle Transaction Monitor

accept x_revolutions prompt "No of cycles: "
accept x_sleep_secs prompt "Seconds for sleep interval: "
prompt

declare

cursor c_trans_sess is
select a.addr,
a.status "TStatus",
a.start_time,
a.name,
a.used_ublk,
a.used_urec,
a.log_io,
a.phy_io,
b.sid,
b.serial#,
b.username,
b.taddr,
b.status "SStatus",
b.osuser,
b.program,
b.client_info,
b.logon_time
from v$transaction a,
v$session b
where b.taddr = a.addr
order by b.sid;

type tr_trans_sess is record
( t_addr v$transaction.addr%type,
t_status v$transaction.status%type,
t_start_time v$transaction.start_time%type,
t_name v$transaction.name%type,
t_used_ublk v$transaction.used_ublk%type,
t_used_urec v$transaction.used_urec%type,
t_log_io v$transaction.log_io%type,
t_phy_io v$transaction.phy_io%type,
s_sid v$session.sid%type,
s_serial# v$session.serial#%type,
s_username v$session.username%type,
s_taddr v$session.taddr%type,
s_status v$session.status%type,
s_osuser v$session.osuser%type,
s_program v$session.program%type,
s_client_info v$session.client_info%type,
s_logon_time v$session.logon_time%type
);
type tt_trans_sess is table of tr_trans_sess index by binary_integer;

lt_trans_sess tt_trans_sess;

ln_counter number := 0;

ln_rec_loc number := -1;
ls_sql varchar2( 2000 );
ls_head1 varchar2( 2000 );
ls_head2 varchar2( 2000 );

ln_revolutions number := to_number( nvl( '&x_revolutions', '1' ) );
ln_sleep_secs number := to_number( nvl( '&x_sleep_secs', '5' ) );

begin
for i in c_trans_sess loop
ln_counter := ln_counter + 1;

lt_trans_sess( ln_counter ).t_addr := i.addr;
lt_trans_sess( ln_counter ).t_status := i."TStatus";
lt_trans_sess( ln_counter ).t_start_time := i.start_time;
lt_trans_sess( ln_counter ).t_name := i.name;
lt_trans_sess( ln_counter ).t_used_ublk := i.used_ublk;
lt_trans_sess( ln_counter ).t_used_urec := i.used_urec;
lt_trans_sess( ln_counter ).t_log_io := i.log_io;
lt_trans_sess( ln_counter ).t_phy_io := i.phy_io;
lt_trans_sess( ln_counter ).s_sid := i.sid;
lt_trans_sess( ln_counter ).s_serial# := i.serial#;
lt_trans_sess( ln_counter ).s_username := i.username;
lt_trans_sess( ln_counter ).s_taddr := i.taddr;
lt_trans_sess( ln_counter ).s_status := i."SStatus";
lt_trans_sess( ln_counter ).s_osuser := i.osuser;
lt_trans_sess( ln_counter ).s_program := i.program;
lt_trans_sess( ln_counter ).s_client_info := i.client_info;
lt_trans_sess( ln_counter ).s_logon_time := i.logon_time;

end loop;

for cycles in 1..ln_revolutions loop
dbms_lock.sleep( ln_sleep_secs );

dbms_output.put_line( '' );
dbms_output.put_line( 'Cycle: ' || cycles );
dbms_output.put_line( '' );

ls_head1 := rpad( 'Username', 30 ) || ' ' ||
lpad( 'SID', 5 ) || ' ' ||
rpad( 'Usr Status', 10 ) || ' ' ||
rpad( 'Tran Stat', 10 ) || ' ' ||
'Used Blocks1' || ' ' ||
'Used Blocks2' || ' ' ||
' Used Recs1' || ' ' ||
' Used Recs2' || ' ' ||
' Rec Change' || ' ' ||
rpad( 'Tran Start', 20 );
ls_head2 := rpad( '--------', 30 ) || ' ' ||
lpad( '---', 5 ) || ' ' ||
rpad( '----------', 10 ) || ' ' ||
rpad( '---------', 10 ) || ' ' ||
'------------' || ' ' ||
'------------' || ' ' ||
' ----------' || ' ' ||
' ----------' || ' ' ||
' ----------' || ' ' ||
rpad( '----------', 20 );

dbms_output.put_line( ls_head1 );
dbms_output.put_line( ls_head2 );

for i in c_trans_sess loop
ln_rec_loc := -1;
-- locate the transaction
for j in 1..ln_counter loop
if lt_trans_sess( j ).t_addr = i.addr then
ln_rec_loc := j;
exit;
end if;
end loop;
if ln_rec_loc > 0 then
ls_sql := rpad( nvl( lt_trans_sess( ln_rec_loc ).s_username, ' '), 30 ) || ' ' ||
to_char( lt_trans_sess( ln_rec_loc ).s_sid, '9999' ) || ' ' ||
rpad( lt_trans_sess( ln_rec_loc ).s_status, 10 ) || ' ' ||
rpad( lt_trans_sess( ln_rec_loc ).t_status, 10 ) || ' ' ||
to_char( lt_trans_sess( ln_rec_loc ).t_used_ublk, '999,999,999' ) || ' ' ||
to_char( i.used_ublk, '999,999,999' ) || ' ' ||
to_char( lt_trans_sess( ln_rec_loc ).t_used_urec, '999,999,999' ) || ' ' ||
to_char( i.used_urec, '999,999,999' ) || ' ' ||
to_char( i.used_urec - lt_trans_sess( ln_rec_loc ).t_used_urec, '999,999,999' ) || ' ' ||
lt_trans_sess( ln_rec_loc ).t_start_time;
if lt_trans_sess( ln_rec_loc ).t_used_urec > i.used_urec then
ls_sql := ls_sql || ' ' || 'Rolling back?';
end if;
else
ls_sql := rpad( nvl( i.username, ' ' ), 30 ) || ' ' ||
to_char( i.sid, '9999' ) || ' ' ||
rpad( i."SStatus", 10 ) || ' ' ||
rpad( i."TStatus", 10 ) || ' ' ||
rpad( ' ', 12 ) || ' ' ||
to_char( i.used_ublk, '999,999,999' ) || ' ' ||
rpad( ' ', 12 ) || ' ' ||
to_char( i.used_urec, '999,999,999' ) || ' ' ||
' ' || ' ' ||
i.start_time;

end if;
if ln_rec_loc > 0 then
dbms_output.put_line( ls_sql );
end if;
end loop;

end loop; -- end revolution cycles

end;
/

Oracle Resource Intensive SQL

accept x_ignore_sys prompt "Ignore SYS? [Y/N]: "
accept x_owner prompt "Owner: "
accept x_num_rows prompt "Number of top records to return: "

prompt Choose sort order:
prompt action
prompt buffer_gets
prompt cpu_time
prompt disk_reads
prompt elapsed_time
prompt executions
prompt fetches
prompt invalidations
prompt kept_versions
prompt loaded_versions
prompt loads
prompt module
prompt open_versions
prompt parse_calls
prompt parsing_schema_id
prompt parsing_user_id
prompt persistent_mem
prompt rows_processed
prompt runtime_mem
prompt serializable_aborts
prompt sharable_mem
prompt sorts
prompt users_executing
prompt users_opening
prompt version_count

accept x_order prompt "Enter sort order of inner select: "

declare

cursor c_top_records is
select *
from ( select *
from v$sqlarea a
where exists ( select 1
from v$sql_plan
where address = a.address
and hash_value = a.hash_value
and ( object_owner <> 'SYS' or upper( nvl( '&x_ignore_sys', 'N' ) ) <> 'Y' )
and nvl( upper( '&x_owner' ), object_owner ) = object_owner
)
order by &x_order desc, cpu_time desc
)
where rownum < ( to_number( nvl( '&x_num_rows', '10' ) ) + 1 )
order by &x_order desc, cpu_time desc;

cursor c_vsql_plan ( x_address in varchar2, x_hash in number ) is
select *
from v$sql_plan
where address = x_address
and hash_value = x_hash
order by address, hash_value, depth;

cursor c_sql ( x_address in varchar2, x_hash in number ) is
select *
from v$sqltext
where address = x_address
and hash_value = x_hash
order by piece;

cursor c_sqlarea ( x_address in varchar2, x_hash in number ) is
select *
from v$sqlarea
where address = x_address
and hash_value = x_hash;
lt_sqlarea v$sqlarea%rowtype;

ls_sql_text varchar2( 10000 );

begin

dbms_output.put_line( '' );

for i in c_top_records loop
dbms_output.put_line( rpad( '-', 150, '-' ) );
open c_sqlarea( i.address, i.hash_value );
fetch c_sqlarea into lt_sqlarea;
close c_sqlarea;

case lower( '&x_order' )
when lower( 'action' ) then
dbms_output.put_line( 'Action :' || lt_sqlarea.action );
when lower( 'buffer_gets' ) then
dbms_output.put_line( 'Buffer gets :' || lt_sqlarea.buffer_gets );
when lower( 'cpu_time' ) then
dbms_output.put_line( 'CPU time:' || lt_sqlarea.cpu_time );
when lower( 'disk_reads' ) then
dbms_output.put_line( 'Disk reads :' || lt_sqlarea.disk_reads );
when lower( 'elapsed_time' ) then
dbms_output.put_line( 'Elapsed time :' || lt_sqlarea.elapsed_time );
when lower( 'executions' ) then
dbms_output.put_line( 'Executions :' || lt_sqlarea.executions );
when lower( 'fetches' ) then
dbms_output.put_line( 'Fetches :' || lt_sqlarea.fetches );
when lower( 'invalidations' ) then
dbms_output.put_line( 'Invalidations :' || lt_sqlarea.invalidations );
when lower( 'kept_versions' ) then
dbms_output.put_line( 'Kept versions:' || lt_sqlarea.kept_versions );
when lower( 'loaded_versions' ) then
dbms_output.put_line( 'Loaded versions:' || lt_sqlarea.loaded_versions );
when lower( 'loads' ) then
dbms_output.put_line( 'Loads :' || lt_sqlarea.loads );
when lower( 'module' ) then
dbms_output.put_line( 'Module :' || lt_sqlarea.module );
when lower( 'open_versions' ) then
dbms_output.put_line( 'Open versions :' || lt_sqlarea.open_versions );
when lower( 'parse_calls' ) then
dbms_output.put_line( 'Parse calls:' || lt_sqlarea.parse_calls );
when lower( 'parsing_schema_id' ) then
dbms_output.put_line( 'Parsing Schema Id:' || lt_sqlarea.parsing_schema_id );
when lower( 'parsing_user_id' ) then
dbms_output.put_line( 'Parsing User Id:' || lt_sqlarea.parsing_user_id );
when lower( 'persistent_mem' ) then
dbms_output.put_line( 'Persistent mem :' || lt_sqlarea.persistent_mem );
when lower( 'rows_processed' ) then
dbms_output.put_line( 'Rows Processed :' || lt_sqlarea.rows_processed );
when lower( 'runtime_mem' ) then
dbms_output.put_line( 'Runtime mem:' || lt_sqlarea.runtime_mem );
when lower( 'serializable_aborts' ) then
dbms_output.put_line( 'Serialisable aborts :' || lt_sqlarea.serializable_aborts );
when lower( 'sharable_mem' ) then
dbms_output.put_line( 'Sharable mem:' || lt_sqlarea.sharable_mem );
when lower( 'sorts' ) then
dbms_output.put_line( 'Sorts :' || lt_sqlarea.sorts );
when lower( 'users_executing' ) then
dbms_output.put_line( 'Users executing:' || lt_sqlarea.users_executing );
when lower( 'users_opening' ) then
dbms_output.put_line( 'Users opening :' || lt_sqlarea.users_opening );
when lower( 'version_count' ) then
dbms_output.put_line( 'Version count :' || lt_sqlarea.version_count );
else
dbms_output.put_line( 'Sort order not recognised.' );
end case;

dbms_output.put_line( '' );

ls_sql_text := '';
for j in c_sql( i.address, i.hash_value ) loop
-- if procedure bigt is not available or not required, comment the line
-- out and uncomment the following line:
-- dbms_output.put_line( j.sql_text );
ls_sql_text := ls_sql_text || j.sql_text;
end loop;
bigt( ls_sql_text );

dbms_output.put_line( rpad( 'Operation', 30 ) || ' ' ||
rpad( 'Options', 25 ) || ' ' ||
rpad( 'Owner', 25 ) || ' ' ||
rpad( 'Obj Name', 30 ) || ' ' ||
' Bytes' || ' ' ||
' Cost' || ' ' ||
'Part Start' || ' ' ||
'Part Stop ' || ' ' ||
'Card.' );
dbms_output.put_line( rpad( '---------', 30 ) || ' ' ||
rpad( '-------', 25 ) || ' ' ||
rpad( '-----', 25 ) || ' ' ||
rpad( '-----', 30 ) || ' ' ||
' -----' || ' ' ||
' ----' || ' ' ||
'----------' || ' ' ||
'--------- ' || ' ' ||
'-----' );

for k in c_vsql_plan( i.address, i.hash_value ) loop
dbms_output.put_line( rpad( lpad( ' ', 2 * ( k.depth-1 ), '. ' ) || k.operation, 30 ) || ' ' ||
rpad( nvl( k.options, ' ' ), 25 ) || ' ' ||
rpad( nvl( k.object_owner, ' ' ), 25 ) || ' ' ||
rpad( nvl( k.object_name, ' ' ), 30 ) || ' ' ||
nvl( to_char( k.bytes, '99,999,999' ), ' ' ) || ' ' ||
nvl( to_char( k.cost, '99,999,999' ), ' ' ) || ' ' ||
rpad( nvl( k.partition_start, ' ' ), 10 ) || ' ' ||
rpad( nvl( k.partition_stop, ' ' ), 10 ) || ' ' ||
nvl( to_char( k.cardinality, '9999' ), ' ' ) );
end loop;

end loop;

end;
/