Dev: get client variable in pl/sql

SQL> var val varchar2(4000)
SQL> set autoprint on
SQL> exec sys.dbms_system.get_env('nls_lang',:val);

PL/SQL procedure successfully completed.


VAL
--------------------------------------------------------------------------------
AMERICAN_AMERICA.CL8MSWIN1251

shared pool free memory

--
-- Free memory in shared pool and in reserved_pool separately, percent of free space current and min for reserved pool
--
select s.pool, round((s.bytes - p.free_space)/1024/1024, 3) fm, round((p.free_space)/1024/1024, 3) fm_reserved
, round(((s.bytes - p.free_space)/decode(sps.value, 0, NULL, sps.value))*100, 3) pct_free
, round(((sps_reserved.value - p.max_used_size)/sps_reserved.value)*100, 3) pct_free_reserved_min
from v$sgastat s, v$shared_pool_reserved p, v$parameter sps, v$parameter sps_reserved
where s.pool = 'shared pool' and s.name = 'free memory'
and sps.name='shared_pool_size'
and sps_reserved.name='shared_pool_reserved_size'
;

peformance issue - nls_lang

set NLS_LANG=american_america.utf8
--
19:54:07 SQL> select * from character_set;

171080 rows selected.

Elapsed: 00:00:29.68

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3808 consistent gets
2009 physical reads
0 redo size
16942067 bytes sent via SQL*Net to client
1026 bytes received via SQL*Net from client
59 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
171080 rows processed
--
set NLS_LANG=
--
SQL> select * from character_set;

171080 rows selected.

Elapsed: 00:00:02.26

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14909 consistent gets
2855 physical reads
0 redo size
18383263 bytes sent via SQL*Net to client
125854 bytes received via SQL*Net from client
11407 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
171080 rows processed

07:31:46 155 147 TEST@orcl> select start_scn, commit_scn, undo_sql from flashback_transaction_query where table_name = 'F';
ORA-01031: insufficient privileges
--
07:48:00 155 4294967295 SYS@orcl> grant select any transaction to test;
07:48:26 155 148 TEST@orcl> select start_scn, commit_scn, undo_sql from flashback_transaction_query where table_name = 'F';

START_SCN | COMMIT_SCN
------------------------------------ | ------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------

646,961.0 | 646,963.0
delete from "TEST"."F" where ROWID = 'AAAMi7AAFAAAAANAAA';

648,154.0 | 648,155.0
delete from "TEST"."F" where ROWID = 'AAAMi7AAAAAAAAAAAA';

647,577.0 | 647,579.0
delete from "TEST"."F" where ROWID = 'AAAMi7AAFAAAAAMAAA';

648,011.0 | 648,013.0
insert into "TEST"."F"("ID") values ('3');

shrink lob

-- create table (should be lmt tablespace or ORA-10635: Invalid segment or tablespace type will raise)
create table t_lob (id clob);
-- insert data
insert into t_lob select to_clob(lpad('x', 4000))||to_clob(lpad('x', 4000)) from dual connect by rownum <= 3000;
-- check size
select segment_name from dba_lobs where table_name = 'T_LOB';
SEGMENT_NAME
------------------------------
SYS_LOB0000015701C00001$$
select bytes/1024/1024 m from dba_segments where segment_name = 'SYS_LOB0000015701C00001$$';
M
------------------------------------
24.0
-- delete rows
delete from t_lob;
commit;
-- shrink
alter table t_lob shrink space cascade;
ORA-10636: ROW MOVEMENT is not enabled
--
alter table t_lob enable row movement;
alter table t_lob shrink space cascade;
Table altered.
select bytes/1024/1024 m from dba_segments where segment_name = 'SYS_LOB0000015701C00001$$';

M
------------------------------------
.0625

How to find out sql for ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

-- First session set resumable timeout 30 sec
alter session enable resumable timeout 30;
-- or for all system
alter system set resumable_timeout=30;
-- Temp file should be 3 meg
select * from dba_objects, dba_tables order by 1,2,3,4,5,6,7,8,9;
-- Other session
select session_id, sql_text, error_msg from dba_resumable;
Output:
...
153.0
select * from dba_objects, dba_tables order by 1,2,3,4,5,6,7,8,9
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
...
-- Monitor of tempspace usage
select s.sid, t.blocks*p.value/1024/1024 m
from v$tempseg_usage t, v$parameter p , v$session s
where p.name = 'db_block_size' and
s.saddr = t.session_addr;
-- Abort resumed session
exec dbms_resumable.abort(153);

Validate structure partitioned table

-- Create table
create table t_p (id number)
partition by range (id)
(partition t_p_1 values less than (1)
, partition t_p_2 values less than (2)
, partition t_p_3 values less than(3));
-- First attempt
analyze table t_p partition (t_p_1) validate structure;
ORA-14508: specified VALIDATE INTO table not found
-- Need to create validate table
@rdbms\admin\utlvalid.sql;
-- Now works
analyze table t_p validate structure;
analyze table t_p partition (t_p_1) validate structure;