set lines 132 set pagesize 3000 spool lob_mvmt select ‘alter table ‘ || table_name || ‘ move lob(‘|| column_name || ‘) store as ( tablespace pnet_data ) ;’ from user_tab_columns where data_type like ‘%LOB%’; spool off
In this Document Goal Solution References Applies to: PL/SQL – Version: 8.1.7.4 to 10.1.0.2 Information in this document applies to any platform. Goal This article presents one suggested way of killing a session using an “ALTER SYSTEM KILL SESSION ‘sid,serial#’ ” statement in PL/SQL dynamically where the sid and serial# are values being passed in…
Read More “Kill a session dynanically using execute immediate” »
PURPOSE This article is a step by step demonstration on how to use the Oracle10g SQLAccess Advisor (DBMS_ADVISOR) package to get recommendations on what Materialized Views to create in order to support a given set of queries (a workload) issued on the database. The workload is imported from the Oracle10g Automatic Workload Repository using a…
Checked for relevance on 12-May-2008. Clarification/Explanation ========================== The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information,leading to poor execution plans. Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications,…
Temporary Tablespace (1) The DEFAULT TEMPORARY TABLESPACE must be of TEMPORARY type. (2) The DEFAULT TEMPORARY TABLESPACE cannot be converted to PERMANENT once it has been defined as the DEFAULT TEMPORARY TABLESPACE. (3) Before dropping the DEFAULT TEMPORARY TABLESPACE, create another one. (4) The DEFAULT TEMPORARY TABLESPACE cannot be taken OFFLINE: (5) The temporary tablespace…
Read More “Temporary Tablespace Information and restriction.” »
Step-1 Run @temp_use.sql as following. Following is the code for temp_use.sql SELECT b.TABLESPACE, b.segfile#, b.segblk#, b.blocks, a.SID, a.serial#, a.username, a.osuser, a.status, c.sql_text FROM v$session a, v$sort_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND a.SQL_HASH_VALUE = c.HASH_VALUE and a.sid not in (select distinct sid from v$mystat) ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks / set pagesize…
Read More “When error comes for temporary tablespace with version <= 9i” »
Find long running transaction that are running for more than 5 minutes with transaction id. col machine format a30 col sid format 99999 col Transaction_id format a20 col username format a10 col days format 99.99 col minutes format 999999 set lines 120 pages 200 select a.sid, a.username, a.machine, round((sysdate – to_date(b.start_time, ‘MM/DD/YY HH24:MI:SS’)) * 1440)…
Notes on v$transaction The information here contains the view description from the Oracle9i documentation Earlier versions of Oracle may not include all columns. The v$transaction view lists the active transactions in the system. Column Datatype Description ————- ————– ————————————– ADDR RAW(4) Address of transaction state object XIDUSN NUMBER Undo segment number XIDSLOT NUMBER Slot number…
Read More “V$transaction notes for finding XID composition.” »
Declare v_filehandle UTL_FILE.FILE_TYPE; file_dir VARCHAR2(50) :=’/db3/oracle/utlfiles’; begin t_file_name :=’Vatti_test.dat’; v_filehandle := UTL_FILE.FOPEN(file_dir, t_file_name, ‘w’); UTL_FILE.PUT_LINE(v_filehandle, ‘Hello TEST’); UTL_FILE.FCLOSE(v_filehandle); exception when others then dbms_output.put_line(sqlcode || ‘ ‘ || sqlerrm); end; /
TYPE VARCHAR2(17) Identifies the type of record section: DATABASE, CKPT PROGRESS, REDO THREAD, REDO LOG, DATAFILE, FILENAME, TABLESPACE, LOG HISTORY, OFFLINE RANGE, ARCHIVED LOG, BACKUP SET, BACKUP PIECE, BACKUP DATAFILE, BACKUP REDOLOG, DATAFILE COPY, BACKUP CORRUPTION, COPY CORRUPTION, DELETED OBJECT, or PROXY COPY RECORD_SIZE NUMBER Record size in bytes RECORDS_TOTAL NUMBER Number of records allocated…
