TOP undo usage

Top 10 UNDO usage SQL
select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID in
(select distinct MAXQUERYSQLID from
(select * from
(select SNAP_ID,
MAXQUERYLEN,
UNDOBLKS,
MAXQUERYSQLID,
to_char(BEGIN_TIME,’yyyy/mm/dd hh24:mi’) begin,
to_char(END_TIME,’yyyy/mm/dd hh24:mi’) end
from DBA_HIST_UNDOSTAT
order by UNDOBLKS desc,
MAXQUERYLEN desc
) where rownum<11
)
);

how to get ddl

set heading off;

set echo off;

Set pages 999;

set long 10000;

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLE','TABLENAME','USERNAME') from dual;

select dbms_metadata.get_ddl('INDEX','INDEXNAME','USERNAME') from dual;

spool off;

grant select on all objects in a schema to another user

Spool priv.sql
select 'grant select on &&USER'||'.'||object_name|| ' to &ROLE;' from dba_objects where owner='&&USER';
@priv.sql

Output of CLOB

set long 32767;
select max(dbms_lob.getlength(e)) from d;

select dbms_lob.substr(MYCLOBCOL,4000,1) from TABLE

dbms_lob.substr(col_2,4000,1) col_2_1
,dbms_lob.substr(col_2,4000,4001) col_2_2
,dbms_lob.substr(col_2,4000,8001) col_2_3
from clob_table
/



select max(dbms_lob.getlength(e)) from d;

who is using my temp tablespace

SELECT
a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;

http://www.dbspecialists.com/files/presentations/temp_space.html

Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about
temporary tablespace usage.


SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-------------------------- ---------------             --------------                 ----------
TEMP
                                                        134217728 
                   134217728                    133169152

With this information, you can perform an online shrink of a temporary tablespace
using the ALTER TABLESPACE command.

SQL> alter tablespace temp shrink space keep 100m;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------------------    ---------------                  ---------------           ------------
TEMP                           
                              105906176                           
1048576              104857600

The shrink can also be directed to a specific tempfile using the TEMPFILE clause.

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/grdbms/32bit/app/oracle/oradata/S111W3/temp01.dbf' KEEP 30M;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------        ---------------              ---------------            ------------
TEMP                               
                               31522816                           
1040384                    31457280

1 row selected.

The
KEEP clause specifies the minimum size of the tablespace or tempfile. If
this is omitted, the database will shrink the tablespace or tempfile to
the smallest possible size.

SQL> ALTER TABLESPACE temp SHRINK SPACE;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-------------------------        ---------------           ---------------            -------------
TEMP                                  2088960                        1040384                     1048576
set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr

Interesting Requirement

I have been asked to give user A account lock and unlock so that it can lock and unlock only user B but if I happen to give it to user A then it can be used against any user.

Researched and came to following conclusion which looks promising


SQL> create or replace procedure MYLOCK as
2 begin
3 execute immediate 'alter user A account lock';
4 end;
5 /

Procedure created.

SQL> create or replace procedure MYUNLOCK as
2 begin
3 execute immediate 'alter user A account unlock';
4 end;
5 /

Procedure created.

SQL> grant execute on mylock to B;

Grant succeeded.

SQL> grant execute on myunlock to B;

Grant succeeded.

SQL> conn b/b
Connected.

SQL> conn a/a
Connected.

SQL> conn b/b
Connected.

SQL> exec sys.mylock;

PL/SQL procedure successfully completed.

SQL> conn a/a
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> conn b/b
Connected.
SQL> exec sys.myunlock;

PL/SQL procedure successfully completed.

SQL> conn a/a
Connected.

Dadosa

First blog