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 […]
Check RPMs on Linux
rpm -q full_name or use this script: rpm -q –qf ‘%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n’ binutils \ compat-libstdc++-33 \ elfutils-libelf \ elfutils-libelf-devel \ gcc \ gcc-c++ \ glibc \ glibc-common \ glibc-devel \ glibc-headers \ ksh \ libaio \ libaio-devel \ libgcc \ libstdc++ \ libstdc++-devel \ make \ sysstat \ unixODBC \ unixODBC-devel
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 […]
Pinning a table to memory
ALTER TABLE PLAN_TABLE CACHE; If you have configured the KEEP buffer then you can ALTER TABLE PLAN_TABLE STORAGE(BUFFER_POOL KEEP);
Check PSU version
Make sure your opatch version is at or above 10.2.0.4.5 for version 10.2 ORACLE_HOMEs 11.1.0.6.5 for version 11.1 ORACLE_HOMEs 11.2.0.1.0 for version 11.1 ORACLE_HOMEs and run the following command opatch lsinv -bugs_fixed | grep PSU sample output: $ opatch version Invoking OPatch 10.2.0.4.8 OPatch Version: 10.2.0.4.8 OPatch succeeded. $ $ opatch lsinv -bugs_fixed | grep […]
Show DBMS_SCHEDULER job information.
To show details on job run: select log_date , job_name , status , req_start_date , actual_start_date , run_duration from dba_scheduler_job_run_details To show running jobs: select job_name , session_id , running_instance , elapsed_time , cpu_used from dba_scheduler_running_jobs; To show job history: select log_date , job_name , status from dba_scheduler_job_log; show all schedules: select schedule_name, schedule_type, start_date, […]