Oracle
Set date+time (NLS_DATE_FORMAT)
SQL> alter session set nls_date_format=’DD/MON/YY HH:MI:SS PM’; Session altered. SQL> select sysdate from dual; SYSDATE ——————— 27/MAY/11 10:33:31 AM
Create a DBMS_JOB
–schedules this job to run immediately and then every hour after that DECLARE JobNo dba_jobs.job%TYPE; BEGIN DBMS_JOB.SUBMIT ( job => JobNo, what => ‘begin SESSIONS_RM(minutes=>180); end;’, next_date => SYSDATE, interval => ‘SYSDATE + 1/24’); COMMIT; END; /
COLD BACKUP Explained
Determine the files you will need: The datafiles: SQL> select name from v$datafile The control files: SQL> select name from v$controlfile The temp datafiles: SQL> select name from v$tempfile The online redo logs: SQL> select member from v$logfile The parameter file (spfile): SQL> show parameter spfile; Take a few backup precautions: Backup the control file: […]
view_fra.sql
set lines 185 col df format 999,999,999 heading “Datafile Size (in MB)” col tf format 999,999,999 heading “Tempfile Size (in MB)” col rd format 999,999,999 heading “Redo Logfile Size (in MB)” col nm format a50 heading “Location” col sl format 999,999,999 heading “Size (in MB)” col su format 999,999,999 heading “Used (in MB)” col sr […]
Get good idea for open_cursors value
SQL> select value, name from v$sysstat where name like ‘%cursor%’; SQL> select ‘session_cached_cursors’ parameter, lpad(value, 5) value, decode(value, 0, ‘ n/a’, to_char(100 * used / value, ‘990’) || ‘%’) usage from ( select max(s.value) used from v$statname n, v$sesstat s where n.name = ‘session cursor cache count’ and s.statistic# = n.statistic# ), ( select value […]
Resize tablespaces including UNDO workaround
We can resize the database datafiles with: SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs01.dbf’ RESIZE 500M; Most of the times when I try to resize the undo tablespace I encounter ORA-3297 error: file contains used data beyond the requested RESIZE value. This means that some undo information stills stored above the datafile size we want to set. […]
show_users.sql
column mu format a40 heading “Machine – User” select machine||’ – ‘||nvl(username,'[BKGD PROCESS]’) mu, count(*) from v$session group by machine||’ – ‘||nvl(username,'[BKGD PROCESS]’); select count(*) “Total Count of Sessions” from v$session;
Data pump export (expdp) and import (impdp) examples
Export parfile: userid=”/ as sysdba” directory=COG83_EXPORT dumpfile=cog83_export_092710.dmp logfile=cog83_export_092710.log parallel=4 compression=none content=ALL schemas=cog_cm,cog_log Import parfile: userid=”/ as sysdba” directory=COG83_EXPORT dumpfile=cog83_export_092710.dmp logfile=cog83_import_092710.log parallel=4 remap_schema=cog_log:cog_log_83 remap_schema=cog_cm:cog_cm_83 remap_tablespace=data_cog_log:data_cog_log_83 remap_tablespace=data_cog_cm:data_cog_cm_83 remap_tablespace=data_lob_cog_cm:data_lob_cog_cm_83
Get CLOB sizes
SELECT AVG(DBMS_LOB.GETLENGTH(&&vCOLUMN_NAME)), MIN(DBMS_LOB.GETLENGTH(&&vCOLUMN_NAME)), MAX(DBMS_LOB.GETLENGTH(&&vCOLUMN_NAME)), COUNT(CASE WHEN DBMS_LOB.GETLENGTH(COMM_COMMENTS) > 4000 THEN 1 ELSE NULL END ), COUNT(*) FROM UD_STW.ZZZ_PS_COMMUNICATION ; AVG MIN MAX COUNT COUNT(*) ———- — —- ——- ——– 14.2310298 1 1396 0 3108718 1 row selected. what that means is that out of the 500 MB the lobs take only around 40 MB, which […]
Research I did on Character Set conversions
We could put both DBs on AL32UTF8. Right now Cognos is AL32UTF8 and DW is WE8ISO8859P1. UTF8 is a logical superset of WE8ISO8859P1, but not a binary superset. Meaning some characters may have different binary representation. For most characters (A-Z, 0-9, and most keyboard symbols) the representation will be the same single byte representation in […]