==== Drop system generated hidden columns (statistics) ==== BEGIN DBMS_STATS.DROP_EXTENDED_STATS('', '', '(X,Y,Z)'); END; / After procedure executed please verify extended statistics have been successfully removed SELECT column_name, virtual_column FROM all_tab_cols WHERE table_name = 'TABLE_NAME' AND owner = 'OWNER>' AND column_name LIKE 'SYS_STS%'; After dropping the extended statistics, the system-generated virtual columns should be removed. NOTE: To drop the individual column BEGIN DBMS_STATS.DROP_EXTENDED_STATS('OWNER', 'TABLE_NAME', 'SYS_STS7G6L9J_UUN9JW4XZQKXX_D5'); END; / ==== Compress Oracle database version into comparable numerals ==== From package omc_ash_viewer. Shows use of regular expressions in SQL. FUNCTION COMP_DB_VERSION(p_version IN VARCHAR2) RETURN VARCHAR2 IS l_version varchar2(30) := p_version; l_digit number; r_version varchar2(17); -- string to return BEGIN LOOP -- find the digit l_digit := regexp_substr(l_version,'(\d)+'); -- strip the digit and the . (if it exists) IF l_version is null THEN r_version := r_version || '00'; ELSE l_version := regexp_replace(l_version,'(\d)+.|(\d)+','',1,1); r_version := r_version || lpad(to_char(l_digit,'TM9'),2,'0'); END IF; exit when length(r_version) >= 10; END LOOP; RETURN r_version; END COMP_DB_VERSION; select username , profile from dba_users where ( oracle_maintained != 'Y' and username != 'XS$NULL' and username not like 'OPS$%' and not regexp_like (username, 'U\d+') ) ==== Move temp files in a temp tablespace from one location to another ==== set lines 1000 pages 100 col name for a80 col stmt for a1000 select name, status from v$tempfile; select 'alter tablespace '||tablespace_name||' add tempfile '''||replace(file_name,'exp','acc')||''' size 1g autoextend on next 1g maxsize 32767m;' from dba_temp_files; select 'alter database tempfile '''||name||''' offline;' stmt from v$tempfile where name like '%exp%'; select 'alter database tempfile '''||name||''' drop including datafiles;' stmt from v$tempfile where name like '%exp%'; select name, status from v$tempfile; ==== Enable resumable session ==== Some operations, such as loading rows into tables using Data Pump are potentially resumable in the case where a tablespace runs out of space.\\ Instead of failing with a "Failed to extend..." error, the session can be set to resumable and will appear to hang until the issue is resolved. It will then continue.\\ alter session enable resumable; To see if the session has hung, check the alertlog or the dba_resumable view ==== Access o/s filesystem sizes using Enterprise Manager tables ==== See filesystem size from inside SQL*Plus by accessing a view in Cloud Control repository set lines 2000 pages 100 col host_name for a20 col mount_point for a40 col target_value for a70 col filesystem for a70 select target_name host_name , key_value mount_point , MAX(DECODE(column_label,'Filesystem',value,'') ) filesystem , SUM(DECODE(column_label,'Filesystem Size (MB)',value,0) ) total , SUM(DECODE(column_label,'Filesystem Space Available (MB)',value,0) ) available , SUM(DECODE(column_label,'Filesystem Space Available (%)',value,0) ) available_perc from sysman.mgmt$metric_current where target_name = '&hostname..cln.be' and metric_name = 'Filesystems' group BY target_name , key_value / ==== ddclient.conf file when using Cloudflare ==== * [[https://adamtheautomator.com/cloudflare-dynamic-dns/|Find Global API token from Cloudflare]] ddclient nows keeps its configuration file in /etc/ddclient/ddclient.conf daemon=600 # check every n seconds syslog=yes # log update msgs to syslog #mail=root # mail all msgs to root mail-failure=root # mail failed update msgs to root pid=/var/run/ddclient/ddclient.pid # record PID in file. ssl=yes # use ssl-support. use=web, web=checkip.dyndns.org/, web-skip='IP Address: ' protocol=cloudflare, \ zone=stuartbarkley.com, \ server=www.cloudflare.com, \ login=, \ password= \ stuartbarkley.com,www.stuartbarkley.com Enable and start the service systemctl enable ddclient systemctl start ddclient systemctl status ddclient Did it fail to start? ddclient is run as its own user, not as root. So if it has previously been 'tested' as root, the cache file will be wrongly owned. ll /var/cache/ddclient/ddclient.cache sudo chown ddclient /var/cache/ddclient/ddclient.cache sudo chgrp ddclient /var/cache/ddclient/ddclient.cache Another reason could be due to the fact that there are duplicate lines in /etc/sysconfig/ddclient. Comment these out. Does it fail to update? Check the log. On fedora, check /var/log/dynuiuc.log WARNING: file /var/cache/ddclient/ddclient.cache, line 4: Invalid Value for keyword 'ip' = '' Stop the service, delete the cache file and restart the service sudo systemctl stop ddclient sudo rm /var/cache/ddclient/ddclient.cache sudo systemctl start ddclient sudo systemctl status ddclient ● ddclient.service - A Perl Client Used To Update Dynamic DNS Loaded: loaded (/usr/lib/systemd/system/ddclient.service; enabled; vendor preset: disabled) Active: active (running) since Sun 2022-01-16 17:05:38 CET; 5s ago Process: 4036 ExecStart=/usr/sbin/ddclient $DDCLIENT_OPTIONS (code=exited, status=0/SUCCESS) Process: 4033 ExecStartPre=/bin/touch /var/cache/ddclient/ddclient.cache (code=exited, status=0/SUCCESS) Main PID: 4047 (ddclient - read) Tasks: 1 (limit: 4915) Memory: 16.8M CGroup: /system.slice/ddclient.service └─4047 ddclient - reading from api.cloudflare.com port 443 Jan 16 17:05:38 fedora systemd[1]: Starting A Perl Client Used To Update Dynamic DNS... Jan 16 17:05:38 fedora systemd[1]: Started A Perl Client Used To Update Dynamic DNS. Jan 16 17:05:38 fedora ddclient[4048]: WARNING: file /var/cache/ddclient/ddclient.cache: file /var/cache/ddclient/ddclient.cache must be accessible only by its owner (fixed). Jan 16 17:05:43 fedora ddclient[4049]: SUCCESS: xxxxxxxx.com -- Updated Successfully to 109.128.99.69 ==== See the logging of what ddclient was doing ==== journalctl -f -u ddclient.service ==== Run ddclient in foreground and debug mode to check it is updating the dynamic dns correctly ==== sudo ddclient -daemon=0 -debug -verbose -noquiet or sudo ddclient -foreground -debug -verbose -use=web and check what settings are configured grep -v '^#' /etc/ddclient.conf ==== Default variables in SQL*Plus script ==== * [[https://stackoverflow.com/questions/3564283/how-to-declare-variable-and-use-it-in-the-same-oracle-sql-script|How to declare a variable and use it in the same Oracle SQL script?]] If calling an SQL script from a shell and parameters were not passed in, the variables could be defaulted. This is a long-winded trick but it works. rem setup default values def DEFAULT_WARN_LEVEL='97' def DEFAULT_CRIT_LEVEL='99' rem assign values to &1 and &2 so that SQL*Plus does not ask col warn_level new_value 1 col crit_level new_value 2 select null warn_level , null crit_level from dual where 1=2 / rem if values were passed in, use them, if not, use default select nvl('&1','&DEFAULT_WARN_LEVEL') warn_level , nvl('&2','&DEFAULT_CRIT_LEVEL') crit_level from dual / rem because of the way new_value works, we now have the values in &1 and &2 def warn_level='&1' def crit_level='&2' Another version with one variable var l_default_password varchar2(4000) exec :l_default_password := dbms_random.string('a',15)||'#'||dbms_random.string('x',16); set lines 1000 pages 100 verif off col cgkpwd new_value 1 nopri select null cgkpwd from dual where 1=2; select nvl('&1', :l_default_password) cgkpwd from dual; def cgkpwd = "&1" This can now be used in PL/SQL like this l_stmt := 'CREATE USER monitor IDENTIFIED BY "&cgkpwd" DEFAULT TABLESPACE '||l_perm_ts||' TEMPORARY TABLESPACE '||l_temp_ts||' PROFILE SERVICE_ACCOUNT ACCOUNT UNLOCK'; Here is an example found in $ORACLE_HOME/rdbms/admin/preupgrd.sql of an EBS installation Rem The below code will prevent any prompting if the script is Rem invoked without any parameters. Rem SET FEEDBACK OFF SET TERMOUT OFF COLUMN 1 NEW_VALUE 1 SELECT NULL "1" FROM SYS.DUAL WHERE ROWNUM = 0; SELECT NVL('&&1', 'FILE') FROM SYS.DUAL; COLUMN 2 NEW_VALUE 2 SELECT NULL "2" FROM SYS.DUAL WHERE ROWNUM = 0; SELECT NVL('&&2', 'TEXT') FROM SYS.DUAL; SET FEEDBACK ON SET TERMOUT ON SET SERVEROUTPUT ON FORMAT WRAPPED; SET ECHO OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 5000; ==== Get O/S information from SQL*Plus ==== set numwid 20 col stat_name for a30 select stat_name, max(value) from dba_hist_osstat group by stat_name order by 1; STAT_NAME MAX(VALUE) ------------------------------ -------------------- AVG_BUSY_TIME 159140244 AVG_IDLE_TIME 787689662 AVG_IOWAIT_TIME 15544079 AVG_SYS_TIME 56110393 AVG_USER_TIME 102429058 BUSY_TIME 11501599861 GLOBAL_RECEIVE_SIZE_MAX 1048576 GLOBAL_SEND_SIZE_MAX 1048576 IDLE_TIME 56757196650 IOWAIT_TIME 1161817504 LOAD 187.662109375 NUM_CPUS 72 NUM_CPU_CORES 18 NUM_LCPUS 72 NUM_VCPUS 18 OS_CPU_WAIT_TIME 17900079100 PHYSICAL_MEMORY_BYTES 279172874240 RSRC_MGR_CPU_WAIT_TIME 79293 SYS_TIME 4083234638 TCP_RECEIVE_SIZE_DEFAULT 16384 TCP_RECEIVE_SIZE_MAX 9223372036854775807 TCP_RECEIVE_SIZE_MIN 4096 TCP_SEND_SIZE_DEFAULT 16384 TCP_SEND_SIZE_MAX 9223372036854775807 TCP_SEND_SIZE_MIN 4096 USER_TIME 7418364852 VM_IN_BYTES 18446743879233654760 VM_OUT_BYTES 14928865943544 28 rows selected. ==== Using ex or ed (command line version of vi) where sed or perl will not do ==== Normally when editing a file inline, Perl is the way to go by using perl -p -i -e 's/this/that/g' textfile The problem with this approach is that Perl tries to create a temporary backup copy in the same directory and if permissions don't allow, this is not an option.\\ The specific issue I had was trying to edit /etc/oratab. The file is owned by oracle but the directory is obviously owned by root. In this case, resorting to 'old school' ex or ed works a treat.\\ ex (and ed) syntax is pretty much the same as vi. ex /etc/oratab < Remove trailing blanks 1,\$s/ *$// Change all lines in a file to copy what is on the line and repeat it (with quotes and a word in between) 1,\$s/^.*$/'&' TO '&' Replace lines containing if conditions with multiple lines 1,\$s/^.*$/if &; then : else echo "ERROR: Could not copy file" fi/ x Delete all lines from the start of the file upto but not including the one starting with CREATE CONTROLFILE 1,/^CREATE CONTROLFILE/-1d Position the file pointer on the one starting with CREATE CONTROLFILE and change it /^CREATE CONTROLFILE/ s/"${SOURCE_SID}"/"${DEST_SID}"/ s/NORESETLOGS/RESETLOGS/ s/DATABASE/SET DATABASE/ w q Add a line to a file after a specific line ex< ==== Execute a shell command on every database on every server ==== This will list all the xml logs for all databases on all servers ./dosh -v -c '/oracle/scripts/.shb/all_db_dosh "ls -al \$ORACLE_BASE/diag/rdbms/\$ORACLE_SID/\$ORACLE_SID/alert"' ==== Spool csv formatted output directly from SQL*Plus ==== At last, Oracle have provided a way of extracting queries in comma-separated (CSV) format directly. echo "select * from user_tables;"|sqlplus -m "csv on" -fast -s / as sysdba produces something like this "TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","STATUS","PCT_FREE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTENTS","MAX_EXTENTS","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED_UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","AVG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TABLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","TEMPORARY","SECONDARY","NESTED","BUFFER_POOL","FLASH_CACHE","CELL_FLASH_CACHE","ROW_MOVEMENT","GLOBAL_STATS","USER_STATS","DURATION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMPRESSION","COMPRESS_FOR","DROPPED","READ_ONLY","SEGMENT_CREATED","RESULT_CACHE","CLUSTERING","ACTIVITY_TRACKING","DML_TIMESTAMP","HAS_IDENTITY","CONTAINER_DATA","INMEMORY","INMEMORY_PRIORITY","INMEMORY_DISTRIBUTE","INMEMORY_COMPRESSION","INMEMORY_DUPLICATE","DEFAULT_COLLATION","DUPLICATED","SHARDED","EXTERNAL","HYBRID","CELLMEMORY","CONTAINERS_DEFAULT","CONTAINER_MAP","EXTENDED_DATA_LINK","EXTENDED_DATA_LINK_MAP","INMEMORY_SERVICE","INMEMORY_SERVICE_NAME","CONTAINER_MAP_OBJECT","MEMOPTIMIZE_READ","MEMOPTIMIZE_WRITE","HAS_SENSITIVE_COLUMN","ADMIT_NULL","DATA_LINK_DML_ENABLED","LOGICAL_REPLICATION" "UET$","SYSTEM","C_FILE#_BLOCK#",,"VALID",0,0,0,0,24576,1048576,1,2147483645,,1,1,"YES","N",0,79,0,0,0,0,0,0," 1"," 1"," N","ENABLED",0,"20-MAR-20 20:27:34","NO",,"N","N","NO","DEFAULT","DEFAULT","DEFAULT","DISABLED","YES","NO",,"DISABLED","YES","SYS","DISABLED","DISABLED",,"NO","NO","YES","DEFAULT","NO",,,"NO","NO","DISABLED",,,,,"USING_NLS_COMP","N","N","NO","NO",,"NO","NO","NO","NO",,,"NO","DISABLED","DISABLED","NO","NO","NO","ENABLED" "CCOL$","SYSTEM","C_COBJ#",,"VALID",0,0,0,0,57344,1048576,1,2147483645,,1,1,"YES","N",10570,142,0,0,0,20,0,0," 1"," 1"," N","ENABLED",10570,"01-NOV-20 10:04:45","NO",,"N","N","NO","DEFAULT","DEFAULT","DEFAULT","DISABLED","YES","NO",,"DISABLED","YES","SYS","DISABLED","DISABLED",,"NO","NO","YES","DEFAULT","NO",,,"NO","NO","DISABLED",,,,,"USING_NLS_COMP","N","N","NO","NO",,"NO","NO","NO","NO",,,"NO","DISABLED","DISABLED","NO","NO","NO","ENABLED" "CDEF$","SYSTEM","C_COBJ#",,"VALID",0,0,0,0,57344,1048576,1,2147483645,,1,1,"YES","N",9037,142,0,0,0,49,0,0," 1"," 1"," N","ENABLED",9037,"15-NOV-20 22:03:02","NO",,"N","N","NO","DEFAULT","DEFAULT","DEFAULT","DISABLED","YES","NO",,"DISABLED","YES","SYS","DISABLED","DISABLED",,"NO","NO","YES","DEFAULT","NO",,,"NO","NO","DISABLED",,,,,"USING_NLS_COMP","N","N","NO","NO",,"NO","NO","NO","NO",,,"NO","DISABLED","DISABLED","NO","NO","NO","ENABLED" "PROXY_DATA$","SYSTEM",,,"VALID",10,40,1,255,65536,1048576,1,2147483645,,1,1,"YES","N",0,0,0,0,0,0,0,0," 1"," 1"," N","ENABLED",0,"20-MAR-20 20:27:28","NO",,"N","N","NO","DEFAULT","DEFAULT","DEFAULT","DISABLED","YES","NO",,"DISABLED","YES",,"DISABLED","DISABLED",,"NO","NO","YES","DEFAULT","NO",,,"NO","NO","DISABLED",,,,,"USING_NLS_COMP","N","N","NO","NO",,"NO","NO","NO","NO",,,"NO","DISABLED","DISABLED","NO","NO","NO","ENABLED" ==== Rebuild the Data Dictionary ==== A bit od a drastic measure but sometimes worth a try to fix odd issues SQL> shutdown immediate; SQL> startup upgrade; SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql SQL> shutdown immediate; SQL> startup ==== Controlfile Autobackup written to ORACLE_HOME/dbs ==== The trick is that the word "#default" has to appear at the end of the autobackup line, otherwise it just gets written to the dbs directory configure controlfile autobackup format for device type disk clear; ==== Protecting an Apache Web Server directory with htaccess ==== * [[https://www.askapache.com/htaccess/ | 100's of examples of htaccess and htpasswd]] * [[https://davidwalsh.name/password-protect-directory-using-htaccess|https://davidwalsh.name/password-protect-directory-using-htaccess]] * [[http://www.htaccesstools.com/articles/password-protection/|http://www.htaccesstools.com/articles/password-protection/]] Two files are needed.\\ **The .htaccess Code** AuthType Basic AuthName "restricted area" AuthUserFile /home/davidwalsh/html/protect-me-dir/.htpasswd require valid-user **The .htpasswd Code** davidwalsh:daWHfZrDLB88. rodstewart:roFulYxC2.8ws cssexpert:csmnmq.M8T5ho ==== Protecting a lighttpd directory with htaccess ==== Generate an MD5 hash with the tools above and paste into this file vi /etc/lighttpd/.htpasswd stuart:3#$567890#$56789056789 Check modules.conf file to ensure mod_auth and mod_rewrite are enabled vi /etc/lighttpd/modules.conf Alter the lighttpd.conf file to allow the authentication #auth directives auth.backend = "htpasswd" auth.backend.htpasswd.userfile = "/etc/lighttpd/.htpasswd" auth.debug = 1 $HTTP["url"] =~ "^/cgi-bin" { auth.require = ( "" => ( "method" => "basic", "realm" => "DbaHawk access", "require" => "valid-user" ) ) } Restart the lighttpd server systemctl stop lighttpd systemctl start lighttpd I tried it with htdigest but could not get it to protect the directory! #auth.backend = "htdigest" #auth.backend.htdigest.userfile = "/etc/lighttpd/lighttpd.user.htdigest" #auth.require = ( # "/var/www/cgi-bin/" => ( # "method" => "basic", # "realm" => "DbaHawk access", # "require" => "valid-user" # ), #) ==== Rename a datafile if the filename contains junk / unprintable characters ==== alter tablespace ts_thaler_data offline; * Oracle have a document that describes a few ways of doing it in [[https://support.oracle.com/rs?type=doc&id=824473.1|Note 824473.1]] If the filename contains control (or other unprintable) characters and cannot be selected in the normal way, use ls -ali to find the node number and then use find with -inum to rename (or move) the file ls -balti total 47474984 12409 -rw-r----- 1 oracle oinstall 1073750016 Sep 26 13:56 ts_thaler_data_02\1776.dbf 12350 -rw-r----- 1 oracle oinstall 5242888192 Sep 26 13:56 ts_thaler_data_01.dbf find . -inum 12409 -exec mv {} ts_thaler_data_06.dbf \; Work out what the database thinks the name is and rename it there too! AskTom has a discussion on it [[https://asktom.oracle.com/pls/apex/asktom.search?tag=rename-datafile-with-dummy-characters-after-dbf|here]] select substr(name,i,1),ascii(substr(name,i,1)) from ( select name from v$datafile where file# = 9), ( select rownum i from dual connect by level <= 50); SUBS ASCII(SUBSTR(NAME,I,1)) ---- ----------------------- t 116 s 115 _ 95 t 116 h 104 a 97 l 108 e 101 r 114 _ 95 d 100 a 97 t 116 a 97 _ 95 0 48 2 50 127 2 50 . 46 d 100 b 98 f 102 You can see there's a 127 just before the .dbf... this crept in a the backspace key was not working correctly. set serveroutput on declare fname1 varchar2(100); fname2 varchar2(100); begin select name into fname1 from v$datafile where file# = 9; fname2 := replace(fname1,chr(127)); dbms_output.put_line('alter database rename file '''||fname1||''' to '''||fname2||''''); execute immediate 'alter database rename file '''||fname1||''' to '''||fname2||''''; end; / alter tablespace ts_thaler_data_online; or another occasion alter tablespace portfolio offline; ls -balti *dbf2* 207 -rw-r----- 1 oralbk oinslbk 34359730176 Apr 23 17:25 portfolio_data12.dbf2 \010\010 chown oracle:oinstall portfolio_data12* find . -inum 207 -exec mv {} portfolio_data12.dbf \; declare fname1 varchar2(100); fname2 varchar2(100) :='/cln/exp/ora_data1/dwh/data/portfolio_data12.dbf'; begin select name into fname1 from v$datafile where file# = 255; dbms_output.put_line('alter database rename file '''||fname1||''' to '''||fname2||''''); execute immediate 'alter database rename file '''||fname1||''' to '''||fname2||''''; end; / alter tablespace portfolio online; ==== How to generate trace files for an Oracle error ==== Open a SQLPLUS session and execute: SQL> connect / as sysdba SQL> alter system set events '&ORA-error code trace name errorstack level 10'; SQL> alter system set max_dump_file_size = unlimited; Redo the import to reproduce the error and then set of the event with: alter system set events '&ORA-error code trace name errorstack off'; Note: * When the ALTER SYSTEM set events command is used only new sessions see the events set by this command. * Replace with the error number (without the string 'ORA-'). Example: If you are encountering ORA-904, the command to set the trace is: alter system set events '904 trace name errorstack level 10'; ==== Generate creation of directory names from dba_directories ==== Before deleting or dropping directories in the database, use this script to generate the create statements select 'create or replace directory '||directory_name||' as '''||directory_path||''';' from dba_directories; ==== Check if current (dot) directory is in PATH variable ==== Using bareword comparison to check PATH variable f [[ :$PATH: == *:".":* ]] ; then echo "in path" else echo "not in path" fi ==== tail a logfile from within the script you are writing it to ==== # # display logfile in real time # ( tail -0f ${LOGFILE} ) & tailPID=$! trap "kill $tailPID" 0 1 2 3 5 9 15 ==== Dump package, procedure etc. from dba_source in a way that it can be used to recreate it ==== From [[https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:454220160386|Ask Tom]] set lines 1000 pages 1000 feedb off verif off select decode( type||'-'||to_char(line,'fm99999'), 'PACKAGE BODY-1', '/'||chr(10), null) || decode(line,1,'create or replace ', '' ) || text text from dba_source where owner = upper('&owner') and name = upper('&object_to_show') order by type , line / ==== How to detect and count user's failed logon attempts ==== * [https://hoopercharles.wordpress.com/2012/04/04/failed-logon-attempts/|Failed Logon Attempts - Charlse Hooper]] SELECT TO_CHAR(TIMESTAMP,'MM/DD HH24:MI') TIMESTAMP, SUBSTR(OS_USERNAME,1,20) OS_USERNAME, SUBSTR(USERNAME,1,20) USERNAME, SUBSTR(TERMINAL,1,20) TERMINAL, ACTION_NAME, RETURNCODE FROM SYS.DBA_AUDIT_SESSION WHERE USERNAME LIKE 'MYUSER%' AND TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE ORDER BY TIMESTAMP DESC; RETURNCODE=0 indicates success\\ RETURNCODE=1017 indicates bad password\\ RETURNCODE=28000 indicates account is locked out ==== A clever trick to exit out of SQL*Plus (using divide by zero) depending on answer to a question ==== This example is taken from ReviewLite. -- Settings for customized functionality define SCRIPT_OO=_OPTIONS_ONLY -- collect only options information define SCRIPT_OO='' -- collect all information [default behavior] define SCRIPT_TS=_TIME_STAMP -- include timestamp in names of the output directory and output files: YYYY.MM.DD.HH24.MI.SS; script does not prompt for license agreement define SCRIPT_TS='\home\oracle\options' -- standard names for output directory and output files; script prompts for license agreement [default behavior] -- PROMT FOR LICENSE AGREEMENT ACCEPTANCE DEFINE LANSWER=N SET TERMOUT ON ACCEPT &SCRIPT_TS LANSWER FORMAT A1 PROMPT 'Accept License Agreement? (y\n): ' -- FORCE "divisor is equal to zero" AND SQLERROR EXIT IF NOT ACCEPTED -- WILL ALSO CONTINUE IF SCRIPT_TS SUBSTITUTION VARIABLE IS NOT NULL SET TERMOUT OFF WHENEVER SQLERROR EXIT select 1/decode(' &LANSWER', 'Y', null, 'y', null, decode(' &SCRIPT_TS', null, 0, null)) as " " from dual; WHENEVER SQLERROR CONTINUE SET TERMOUT ON ==== How to uninstall optional components such as OWB, APEX, EM, OLAP, OWM from an Oracle database ==== * [[http://fast-dba.blogspot.be/2014/04/how-to-remove-unwanted-components-from.html|fast-dba.blogspot.be]] ==== Find unique indexes / primary index / key columns in a table ==== col owner for a12 col table_name for a32 col column_name for a32 col position for 99 col status for a10 set lines 1000 pages 100 SELECT cons.owner , cols.table_name , cols.column_name , cols.position , cons.status FROM all_constraints cons , all_cons_columns cols WHERE 1=1 and cols.owner = '&TABLE_OWNER' and cols.table_name = '&TABLE_NAME' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name , cols.position / ==== Display the oracle instances running on the local server ==== sed could be shorter but this one works cross-platform alias oenv='ps -ef|grep pmon|grep -v grep|awk -F"ora_pmon\_" "{print \$NF}"|grep -v "\$NF"|sort|uniq|sed -e ":a" -e "N" -e "\$!ba" -e "s/\n/ /g"' ==== Display which databases are scheduled for backup in cron ==== for i in `ps -ef | grep [o]ra_pmon | awk -F_ '{print $NF}'`; do crontab -l | grep $i | grep backup_export | awk '{print $2":"$1" - "$17}' done ==== Trace SQL statements using autotrace and explain plan ==== If the plustrace (plustrc.sql) role has been granted, explaining sql statements (without running the statement) is as easy as alter session set sql_trace=true; set autotrace traceonly explain ==== Interact with (run commands on) the O/S from inside SQL and PL/SQL ==== Again, this code found in $ORACLE_HOME/rdbms/admin/preupgrd.sql on an EBS 12.1 installation VARIABLE osCreateDirCmd VARCHAR2(4000) VARIABLE osCreateDirCmd2 VARCHAR2(4000) ... declare homeDir VARCHAR2(4000); useDir VARCHAR2(4000); rdbmsLogDir VARCHAR2(4000); logDir VARCHAR2(4000); ... begin ... -- set a default command :osCreateDirCmd := 'exit'; ... dbms_system.get_env('ORACLE_HOME', homeDir); ... useDir := RTRIM(useDir, '/'); -- Remove any trailing slashes logDir := dbms_assert.enquote_literal( useDir || '/cfgtoollogs/' || uniqueName || '/preupgrade/'); rdbmsLogDir := homeDir || '/rdbms/log/'; ... :osCreateDirCmd := 'mkdir -p ' || logDir; :osCreateDirCmd2 := 'mkdir -p ' || rdbmsLogDir; ... end; ... COLUMN create_cmd NEW_VALUE create_cmd NOPRINT SELECT :osCreateDirCmd AS create_cmd FROM dual; HOST &create_cmd SELECT :osCreateDirCmd2 AS create_cmd FROM dual; HOST &create_cmd ==== Write to a trace log from PL/SQL ==== dbms_system.ksdwrt(1,'message sent to a trace file') ==== Invisible / hidden / virtual columns ==== Article showing how to dump blocks also.\\ [[https://www.oracle.com/technetwork/articles/database/invisible-columns-odb12c-2331522.html|Tips and Tricks: Invisible Columns in Oracle Database 12c by Alex Zaballa, Oracle Ace and Daniel Da Meda (OCM)]] ==== Write to the alert log from PL/SQL ==== * [[https://www.databasejournal.com/features/oracle/article.php/3386731/Inserting-Custom-Messages-in-Oracle-AlertTrace-files.htm|Inserting Custom Messages in Oracle Alert/Trace files]] Writes to trace file dbms_system.ksdwrt(1,'message sent to trace log') Writes to the alertlog dbms_system.ksdwrt(2,'message sent to the alertlog') Writes to the alertlog and trace file dbms_system.ksdwrt(3,'message sent to trace file and the alert log') Also available, KSDIND, KSDDDT and KSDFLS ==== Setup optional parameters and default values in an SQL*Plus script ==== Some forgotten SQL*Plus tricks from the good old days and some new ones... * [[http://www.orafaq.com/wiki/SQL*Plus_FAQ#What_is_the_difference_between_.21_and_HOST.3F|orafaq.com]] set termout off col p1 new_value 1 col p2 new_value 2 col p3 new_value 3 select null p1, null p2, null p3 from dual where 1=2; select nvl('&1','def1') p1, nvl('&2','def2') p2, nvl('&3','def3') p3 from dual; set termout on prompt 1="&1" prompt 2="&2" prompt 3="&3" undef 1 undef 2 undef 3 ==== Display / show users with sysdba & sysoper privileges ==== select * from v$pwfile_users; ==== Show path names of data files using instr ==== Use to find the pathnames of all the filesystems/directories in which a database is housed. select distinct substr(name,1,(instr(name,'/',-1,1)-1)) path_name from v$datafile union select distinct substr(name,1,(instr(name,'/',-1,1)-1)) path_name from v$tempfile / ==== Does a dataguard standby exist for this database? ==== SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1) FROM v$parameter WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; SUBSTR(VALUE,INSTR(VALUE,'=',INSTR(UPPER(VALUE),'SERVICE'))+1) -------------------------------------------------------------------------------- "jdbeop1_standby", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable m ax_failure=0 max_connections=1 reopen=300 db_unique_name="jdbeop1_standby" ne t_timeout=60, valid_for=(all_logfiles,primary_role) ==== Querying initialisation parameters ==== The traditional way of looking at parameters in the spfile and comparing with the same values in the "memory" version is to use v$spparameter and v$parameter respectively.\\ * Reference: [[https://titanwolf.org/Network/Articles/Article?AID=2fd7022d-afe5-4829-8825-ab0edc4ee9b8#gsc.tab=0|Querying initialisation parameters - titanwolf.org]] This article describes the difference between v$parameter, v$parameter2, v$system_parameter, v$system_parameter2, v$spparameter. * V$PARAMETER view provides the initialization parameter settings visible in the current session. If you want to query the settings of all instances of the RAC database, you can query the GV$PARAMETER view. * V$PARAMETER2 view is similar to V$PARAMETER. The only difference is that for initialization parameters that include values, multiple records are returned from this view, each record corresponds to a value. Similarly, for the RAC environment, you can query the GV$PARAMETER2 view. * V$SYSTEM_PARAMETER view records the initialization parameter settings in effect for the current instance. Note that here is the instance takes effect rather than the session takes effect. Similarly, GV$SYSTEM_PARAMETER contains the initialization parameter information for all instances. * V$SYSTEM_PARAMETER2 view is used for parameters that contain multiple values the initialization parameter settings in effect for the current instance that include values, multiple records are returned from this view, each record corresponds to a value. * V$SPPARAMETER records the initialization parameters from the SPFILE file. If the parameter is not set in the SPFILE file, the value corresponding to the field ISSPECIFIED is FALSE. You can also query the GVSPPARAMETER parameter to display the settings of all instances of the RAC environment. ==== List values for all init parameters in v$parameter (including default values for undocumented (hidden) parameters) ==== select i.ksppinm||';'||sv.ksppstvl from x$ksppi i , x$ksppsv sv where i.indx = sv.indx order by i.ksppinm; ==== Log file reports pipe errors ==== Found these in the log file solax005:root[[/opt/IBM/ITM/logs]]# tail solax005_or_RMANV11_col.out CIR1880E (024955) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30024832_5346_pipe CIR1880E (030938) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30030830_5406_pipe CIR1880E (031923) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30031832_5434_pipe CIR1880E (033934) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30033831_5495_pipe CIR1880E (034918) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30034833_5521_pipe CIR1880E (041927) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30041835_5610_pipe CIR1880E (042931) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30042832_5642_pipe CIR1880E (045928) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30045835_5730_pipe CIR1880E (052927) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30052838_5819_pipe CIR1880E (090929) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30090833_6487_pipe This could be due to a timeout on the pipe.\\ Increase parameters and restart agent. Add following lines to or.config: export COLL_WAIT_TIMEOUT='200' export WAIT_TIMEOUT='200' ==== Find the most recent archived SCN number ==== col next_change# for 999999999999999 set numwidth 15 select max(next_change#) from v$archived_log where (thread#, next_change#) in ( select thread#, max(next_change#) from v$archived_log where archived = 'YES' and status = 'A' and resetlogs_id = ( select resetlogs_id from v$database_incarnation where status = 'CURRENT' ) group by thread# ) and status = 'A' and resetlogs_id = ( select resetlogs_id from v$database_incarnation where status = 'CURRENT' ) order by next_change# asc; ==== Monitoring says database inactive ==== * Check the candle user password has not expired in the database. * There may be a clue in the log file. Check /opt/IBM/ITM/logs/`hostname`_or_${SID}_col.out * Check limits for itmora account in /etc/security/limits. Should be same as oracle user. * Check the config file. We had this issue when a new ORACLE_HOME was installed but the cfg file still had the old one in it. export ITM_HOME=/opt/IBM/ITM cd $ITM_HOME/config vi solax005_or_SRV2R.cfg # IBM Tivoli Monitoring for Databases: Oracle Agent # Configuration file: solax005_or_SRV2R.cfg # Written by CandleDBconfig version: 1.4 on 31Jul13 13:39:41 # Note: this is a Korn-shell script that is "sourced" to create the # environment for a Monitoring Agent for Oracle. It can be used to create # the environment to run the IBM-supplied database grant scripts. # IBM does not recommend that you modify this file, but you can change the # data values or add new exported variables as long as the file is a valid ksh # script that executes with zero return code, and values remain quoted with "". # Following environment variables are set for convenience in running grants. export ORACLE_SID="SRV2R" export ORACLE_HOME="/oracle/product/11.2.0.1" # IY92195 COLL_DISABLE_CURSORS setting export TNS_ADMIN="/oracle/product/11.2.0.1/network/admin" # Following variables are set for the setup scripts db_sid="SRV2R" db_home="/oracle/product/11.2.0.1" db_initfilename="" db_login="tivoli" db_password="795D8822BC49477323815CD21C1E66E966E48EEE19C6A98056224D649B0FE316E6A11DC3F4E9BB5E226B65A8" db_ver="11.x" db_type="kor" db_tns="/oracle/product/11.2.0.1/network/admin" db_extparms="" db_hostname="solax005" db_reason="AIX_ps_ceww" db_pipedir="" db_installstatus="CONFIGSUCCESS" # User-defined environment variables Solution:\\ * Change all occurrences of 11.2.0.1 to 11.2.0.3 * Restart agent ==== Alerts due to password expiring (or being changed in the database but not in ITM!) ==== cd $ITM_HOME/logs ls -altr | grep WM820T tail -20 solax005_or_WM820T_col.out CGN1521E (161614) Interval collection failed for cursor KORHART2 RCD0110S (161614) Invalid Oracle logon id (tivoli) or password given Please contact DBA to correct userid or password CGN1521E (161614) Interval collection failed for cursor KORHART4 CGN1525E (161614) One or more interval cursors failed RCD0110S (161714) Invalid Oracle logon id (tivoli) or password given Please contact DBA to correct userid or password CGN1521E (161714) Interval collection failed for cursor KORHART2 RCD0110S (161714) Invalid Oracle logon id (tivoli) or password given Please contact DBA to correct userid or password CGN1521E (161714) Interval collection failed for cursor KORHART4 CGN1525E (161714) One or more interval cursors failed Solution:\\ * Run agent reconfigure to setup the new password for the candle user (tivoli) export ITM_HOME=/opt/IBM/ITM $ITM_HOME/bin/itmcmd config -A or * Restart agent export ITM_HOME=/opt/IBM/ITM $ITM_HOME/smitools/scripts/StartAgent.sh restart or -o WM820T ==== Many agents started with root user instead of itmora ==== This can happen if the server is rebooted before the agents have been added to the itmora list using kdyedit / kciedit export CANDLEHOME=/opt/IBM/ITM export ITM_HOME=/opt/IBM/ITM $ITM_HOME/smitools/scripts/kdyedit list Type Runas Inst or root AGPDEV or root BO1R or root DEV3 or root EMREP or root ICR or itmora MAPDEV or itmora MAPER or itmora PED or itmora PER or itmora RMANV11 or itmora SRV2R or itmora WM820Q or itmora WM820T $ITM_HOME/smitools/scripts/kciedit list Type Runas Inst or root AGPDEV or root BO1R or root DEV3 or root EMREP or root ICR or itmora MAPDEV or itmora MAPER or itmora PED or itmora PER or itmora RMANV11 or itmora SRV2R or itmora WM820Q or itmora WM820T Solution: Add these instances to the itmora list ./kdyedit -t or -i AGPDEV -r itmora add ./kciedit -t or -i AGPDEV -r itmora add ... ./kdyedit -t or -i ICR -r itmora add ./kciedit -t or -i ICR -r itmora add $ITM_HOME/smitools/scripts/kciedit list Type Runas Inst or itmora AGPDEV or itmora BO1R or itmora DEV3 or itmora EMREP or itmora ICR or itmora MAPDEV or itmora MAPER or itmora PED or itmora PER or itmora RMANV11 or itmora SRV2R or itmora WM820Q or itmora WM820T Kill any agents still running as root ps -ef |grep kor | grep root kill -9 Because these agents were owned by root, there will be permissions issues when trying to restart the agents with itmora so these need to be fixed before restarting the agents.\\ The simplest way of doing this is to run lockdown. export CANDLEHOME=/opt/IBM/ITM $CANDLEHOME/smitools/scripts/lockdown.sh ==== See which processes are causing paging ==== svmon -Pt20 | perl -e 'while(<>){print if($.==2||$&&&!$s++);$.=0 if(/^-+$/)}' ==== How to return several variables from SQL*Plus back to Korn shell script ==== Return multiple columns/elements from Oracle to shell script at the same time sqlplus -s sys/${SYS_PASSWORD}@${SID} as sysdba< ==== How to get a shell environment variable from the operating system into the SQL or PL/SQL environment ==== This has been bugging me for years. Something you would think simple... what is ORACLE_HOME? I know it's a question mark at the SQL prompt but how to get it's O/S value? declare var1 varchar2(200); begin dbms_system.get_env ('ORACLE_HOME', var1); dbms_output.put_line ('ORACLE_HOME: '||var1); end; / or in SQL*Plus variable var1 varchar2(200); exec dbms_system.get_env ('ORACLE_HOME', :var1); select :var1 from dual; That got me thinking... if this just gets stuff from the environment, it can fetch any exported variable?!?!\\ From the shell: export running_sids=`ps -ef | grep [p]mon | awk -F_ '{print $NF}'` and now in SQL*Plus: variable sids varchar2(240); exec dbms_system.get_env ('running_sids', :sids); select :sids from dual; Wow!\\ \\ and this is an example taken from postupgrade_fixups.sql (upgrade to 18c) ALTER SESSION SET "_oracle_script" = TRUE; VARIABLE admin_preupgrade_dir VARCHAR2(512); REM REM point PREUPGRADE_DIR to OH/rdbms/admin REM DECLARE oh VARCHAR2(4000); BEGIN dbms_system.get_env('ORACLE_HOME', oh); :admin_preupgrade_dir := dbms_assert.enquote_literal(oh || '/rdbms/admin'); END; / DECLARE command varchar2(4000); BEGIN command := 'CREATE OR REPLACE DIRECTORY PREUPGRADE_DIR AS ' || :admin_preupgrade_dir; EXECUTE IMMEDIATE command; END; / ==== How long/wide can a database name/sid be? ==== Depends on where you look but taking the minimum here as "safe", it should still be limited to 8 characters... col table_name for a30 col data_type for a12 select table_name, data_type, data_length from dba_tab_columns where column_name = 'DB_NAME'; This is 11gR2 TABLE_NAME DATA_TYPE DATA_LENGTH ------------------------------ ------------ ----------- LOGMNRG_DICTIONARY$ VARCHAR2 9 SYS_FBA_CONTEXT_AUD VARCHAR2 256 V_$LOGMNR_DICTIONARY VARCHAR2 9 V_$LOGMNR_LOGS VARCHAR2 8 GV_$LOGMNR_DICTIONARY VARCHAR2 9 GV_$LOGMNR_LOGS VARCHAR2 8 V_$LOGMNR_LOGFILE VARCHAR2 8 V_$LOGMNR_SESSION VARCHAR2 128 GV_$LOGMNR_LOGFILE VARCHAR2 8 GV_$LOGMNR_SESSION VARCHAR2 128 GV_$ASM_CLIENT VARCHAR2 8 V_$ASM_CLIENT VARCHAR2 8 GV_$IOS_CLIENT VARCHAR2 64 V_$IOS_CLIENT VARCHAR2 64 DBA_PDB_HISTORY VARCHAR2 128 CDB_PDB_HISTORY VARCHAR2 128 SSCR_CAP$ VARCHAR2 4000 SSCR_RES$ VARCHAR2 4000 DBA_SSCR_CAPTURE VARCHAR2 4000 CDB_SSCR_CAPTURE VARCHAR2 4000 DBA_SSCR_RESTORE VARCHAR2 4000 CDB_SSCR_RESTORE VARCHAR2 4000 WRM$_DATABASE_INSTANCE VARCHAR2 9 INT$DBA_HIST_DATABASE_INSTANCE VARCHAR2 9 DBA_HIST_DATABASE_INSTANCE VARCHAR2 9 CDB_HIST_DATABASE_INSTANCE VARCHAR2 9 LOGMNR_DICTIONARY$ VARCHAR2 9 27 rows selected. ==== What character set (characterset) was the database built with? ==== col name for a40 col value for a60 select * from nls_database_parameters; or col name for a40 col value$ for a60 select name,value$ from props$ where name = 'NLS_CHARACTERSET'; ==== How to Manually Install XML DB on 11gR2 ==== Depending on how the database was created, XMLDB may not have been installed. And installing it after creation (on 11.2.0.4) causes errors due to a bug in the script.\\ Using the tips in this post means it will be properly usable! * [[http://www.oracle-wiki.net/startdocshowtoinstallxmldb|How to Manually Install XML DB on 11gR2]] * Confirm XML DB is not already installed. select username,default_tablespace,temporary_tablespace from dba_users where username = 'XDB'; returns 0 rows. desc resource_view fails. * As SYS, perform the XML DB installation - Note: this will not be a complete build due to Oracle Bug 9818995: @?/rdbms/admin/catqm.sql For example @?/rdbms/admin/catqm.sql xdb sysaux temp NO * As SYS, grant the relevent permissions to the XDB user: grant execute on utl_file to xdb; grant execute on dbms_lob to xdb; * As sys, ensure their are no invalid objects: @?/rdbms/admin/utlrp.sql * Assuming an spfile is in use, as sys configure the xdb dispatcher: alter system set dispatchers="(protocol=tcp)(service=db)" scope=both; If init.ora is in use, edit the init.ora file instead. * As sys, install the missing contents caused by Oracle Bug 9818995: shutdown immediate; startup upgrade @?/rdbms/admin/xsdbmig.sql shutdown immediate; startup * As sys, ensure their are no invalid objects: @?/rdbms/admin/utlrp.sql * As sys, confirm the XML DB is now fully installed by running the following query and returning the following output: SELECT object_value FROM xdb.xs$securityclass WHERE 1=1 and EXTRACTVALUE(object_value, '/securityClass/@targetNamespace')='http://xmlns.oracle.com/plsql' AND EXTRACTVALUE(object_value, '/securityClass/@name')='network' / OBJECT_VALUE -------------------------------------------------------------------------------- ==== Escape the underscore(_) or percent(%) character in an SQL statement ==== The underscore matches any single character so to return data containing an underscore means having to use an escape character.\\ Here I use the backslash(\\) so it is easy for Unix people to understand the mechanism. select username from dba_users where username like 'ENDUR\_DEV%' escape '\'; ==== Validate, analyse and rebuild indexes ==== set feedback off set linesize 132 set serveroutput on set trimspool on declare LOCKED exception; pragma exception_init (LOCKED, -54); dbase char(8); dbuser varchar2(30); index_name varchar(30); free_text varchar2(50); pctused number; height number; index_mb number; analyze_sql varchar2(100); pct_used_sql varchar2(150); rebuild_sql varchar2(150); cursor c1 is select segment_name, bytes from user_segments where segment_type = 'INDEX' and segment_name like 'DW%' and segment_name not like 'BIN%' and bytes/1024 >= 1024 order by 2; -- validate the index procedure validate_index (v_ind in varchar2) is begin analyze_sql := 'analyze index '||v_ind||' validate structure'; begin execute immediate analyze_sql; exception when LOCKED then dbms_output.put_line (v_ind||' locked - skipping'); pctused := 100; return; when others then dbms_output.put_line (analyze_sql); raise; end; pct_used_sql := 'select pct_used, round(blocks*8192/(1024*1024)), height from index_stats where name = '''||v_ind||''''; execute immediate pct_used_sql into pctused, index_mb, height; if pctused is null then pctused := 0; end if; dbms_output.put_line (rpad(v_ind,35)||' (pct used '||pctused||'% size '||index_mb||'Mb height ' || height ||')'); end validate_index; -- execute SQL and trace if errors procedure run_sql (v_sql in varchar2) is begin execute immediate v_sql; exception when others then dbms_output.put_line (v_sql); raise; end run_sql; -- -- Script starts here -- begin dbms_output.enable(1000000); -- Set up database name and schema -- select name into dbase from v$database; select user into dbuser from dual; dbms_output.put_line('============================================================================= *'); -- dbms_output.put_line('* Indexes rebuild report for '||dbuser||' on '||dbase); dbms_output.put_line('============================================================================= *'); -- Loop around Indexes for x in c1 loop index_name := x.segment_name; validate_index (index_name ); if pctused < 81 then if x.bytes/1024/1024 < 100 then rebuild_sql := 'alter index '||x.segment_name||' rebuild online'; else rebuild_sql := 'alter index '||x.segment_name||' coalesce'; end if; dbms_output.put_line (rebuild_sql); run_sql (rebuild_sql); validate_index (index_name); end if; end loop; end; / set feed on ==== Rebuild unusable indexes on a table ==== select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where 1=1 and status != 'VALID' and owner not in ('SYS','SYSTEM') --and owner = 'WM822PP' --and table_name = 'WF_PLANTS'; ==== Pull the latest cfengine changes now instead of waiting for scheduled time ==== For one host /opt/cfengine/bin/cfagent -ID dbaTime On all hosts at the same time /home/ibmtools/scripts/oracle/dosh -c "/opt/cfengine/bin/cfagent -ID dbaTime" ==== Check status of datafiles without dba_data_files or v$datafile ==== If the database is in mount mode, most tables are unavailable. This query uses the x$ (c based) tables and are always available set linesize 200; set pagesize 100; col inst_id for 9999999 heading 'Instance #' col file_nr for 9999999 heading 'File #' col file_name for A50 heading 'File name' col checkpoint_change_nr for 99999999999999 heading 'Checkpoint #' col checkpoint_change_time for A20 heading 'Checkpoint time' col last_change_nr for 99999999999999 heading 'Last change #' SELECT fe.inst_id, fe.fenum file_nr, fn.fnnam file_name, TO_NUMBER (fe.fecps) checkpoint_change_nr, fe.fecpt checkpoint_change_time, fe.fests last_change_nr, DECODE ( fe.fetsn, 0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'), DECODE (BITAND (fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER') ) status FROM x$kccfe fe, x$kccfn fn WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 ) OR (fe.fepax = 65535 OR fe.fepax = 0) ) AND fn.fnfno = fe.fenum AND fe.fefnh = fn.fnnum AND fe.fedup != 0 AND fn.fntyp = 4 AND fn.fnnam IS NOT NULL AND BITAND (fn.fnflg, 4) != 4 ORDER BY fe.fenum ; ==== Check highest allocated extent in datafile (likely slow when having many extents) ==== * Script to Detect Tablespace Fragmentation ( Doc ID 1020182.6 ) * How to shrink or reduce the datafile size by finding the high water mark (HWM) ( Doc ID 1600774.1 ) * How to find Objects Fragmented below High Water Mark ( Doc ID 337651.1 ) column file_name format a50; column tablespace_name format a15; column highwater format 9999999999; set pagesize 9999 select a.tablespace_name ,a.file_name ,(b.maximum+c.blocks-1)*d.db_block_size highwater from dba_data_files a ,(select file_id,max(block_id) maximum from dba_extents group by file_id) b ,dba_extents c ,(select value db_block_size from v$parameter where name='db_block_size') d where a.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum order by a.tablespace_name,a.file_name / Runup to this was... set markup html on spool on spool db_info.html set echo on select * from dba_tablespaces where tablespace_name = '&&tablespace_name'; select * from dba_data_files where tablespace_name = '&tablespace_name'; select * from (select * from dba_extents where tablespace_name = '&tablespace_name' order by block_id desc) where rownum <301; spool off set markup html off select owner, segment_type, count(*) from dba_segments where tablesapce_name='&tablespace_name' group by owner, segment_type; begin for ii in (select owner, segment_name, segment_type from dba_segments where tablespace_name = '&tablespace_name' and segment_type like 'TABLE%' and segment_name not like '%DATAPUMP%') loop if ii.segment_type='TABLE' then begin execute immediate('alter table '||ii.owner||'.'||ii.segment_name||' enable row movement'); execute immediate('alter table '||ii.owner||'.'||ii.segment_name||' shrink space cascade'); exception when others then dbms_output.put_line('FAILED 1: alter table '||ii.owner||'.'||ii.segment_name||' shrink space cascade : '||SQLERRM); end; end if; end loop; end; / ==== Shrink datafiles to high water mark (HWM) ==== Using dba_extents can be very slow, so this method uses the view's base tables and just uses what is necessary to get the job done.\\ Reference: [[https://blog.dbi-services.com/resize-your-oracle-datafiles-down-to-the-minimum-without-ora-03297/ - Franck Pachot]] set linesize 1000 pagesize 0 feedback off trimspool on with hwm as ( -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents ) select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn ), hwmts as ( -- join ts# with tablespace_name select name tablespace_name,relative_fno,hwm_blocks from hwm join v$tablespace using(ts#) ), hwmdf as ( -- join with datafiles, put 5M minimum for datafiles with no extents select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes from hwmts right join dba_data_files using(tablespace_name,relative_fno) ) select case when autoextensible='YES' and maxbytes>=bytes then -- we generate resize statements only if autoextensible can grow back to current size '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ ' ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;' else -- generate only a comment when autoextensible is off '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999) ||'M after setting autoextensible maxsize higher than current size for file ' || file_name||' */' end SQL from hwmdf where bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed order by bytes-hwm_bytes desc / ==== Procedure to shrink space in tables and indexes ==== * [[http://www.oracle-wiki.net/startdocsreallifeshrinkexample|Using the Oracle Shrink Command]] - lots of good stuff at this site Written by [[https://asktom.oracle.com/pls/apex/f%3Fp%3D100:11:0::::P11_QUESTION_ID:5645095700346948864|Tom Kyte]]\\ Depending on the Oracle version, this procedure may or may not work! It does not include the compact clause (neither does it re-analyse to prove it worked).\\ [[http://www.oracle-wiki.net/startdocsreallifeshrinkexample|Here]] is a worked example that shows the complete process - oracle-wiki.net create or replace procedure shrink_all as l_sql varchar2(4000); l_sql2 varchar2(4000); row_movement exception; pragma exception_init( row_movement, -10636 ); begin for x in (select table_name , owner from t where sgm_space_management = 'AUTO') loop l_sql := 'alter table "' || x.owner || '"."' || x.table_name || '" shrink space'; dbms_output.put_line( l_sql ); begin execute immediate l_sql; exception when row_movement then dbms_output.put_line( 'failed due to row movement...' ); l_sql2 := 'alter table "' || x.owner || '"."' || x.table_name || '" enable row movement'; dbms_output.put_line( l_sql2 ); execute immediate l_sql2; dbms_output.put_line( l_sql ); execute immediate l_sql; end; for y in (select owner , index_name from dba_indexes where table_owner = x.owner and table_name = x.table_name ) loop l_sql := 'alter index "' || y.owner || '"."' || y.index_name || '" shrink space'; dbms_output.put_line( l_sql ); execute immediate l_sql; end loop; end loop; end; / ==== How to delete/kill a distributed transaction ==== Reference: [[http://www.pouwiel.com/?p=2204|pouwiel.com]], [[https://sumanruet.wordpress.com/2017/04/03/ora-02062-distributed-recovery-received/]] col global_tran_id for a40 col fail_time for a21 col host for a10 select local_tran_id , global_tran_id , to_char(fail_time,'DD-MON-YYYY HH24:MI:SS') fail_time , state , host , mixed from dba_2pc_pending / LOCAL_TRAN_ID GLOBAL_TRAN_ID FAIL_TIME STATE HOST MIXED ---------------------- ---------------------------------------- --------------------- ---------------- ---------- ------------ 149.3.2239899 DWH.WORLD.c1482e66.149.3.2239899 14-JAN-2019 16:48:20 collecting CN7057 no 188.17.2309561 DWH.WORLD.c1482e66.188.17.2309561 14-DEC-2018 15:57:06 collecting CN7054 no col database for a30 select local_tran_id , in_out , database , interface from dba_2pc_neighbors / LOCAL_TRAN_ID IN_OUT DATABASE I ---------------------- ------------ ------------------------------ - 149.3.2239899 in N 188.17.2309561 in N 149.3.2239899 out THALER.WORLD N 188.17.2309561 out CHANNELS.WORLD N 188.17.2309561 out LBK_LINK.WORLD N 149.3.2239899 out THALER_CONSULT.WORLD N 6 rows selected. SQL> rollback force '149.3.2239899'; Rollback complete. SQL> execute dbms_transaction.purge_lost_db_entry('149.3.2239899'); PL/SQL procedure successfully completed. SQL> commit; Commit complete. Be aware that the support document 159377.1 does not speak of the rollback force. If you don’t execute that particular command it will not work. ==== ORACLE_BASE is getting set to the same value as ORACLE_HOME when using . oraenv ==== Permissions problem. Make sure the user has **write** access to oraclehomeproperties.xml file! ls -al $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml Change permissions as appropriate. A different approach which should give the same end result would be to grant the orabase executable the setuid bit. chmod u+s $ORACLE_HOME/bin/orabase This allows any user to set the environment as the orabase executable will be run with the permissions of its owner. ==== Move table partitions to a different tablespace ==== Are you getting errors due to partitions belonging to a different tablespace and you want to drop the current tablepace?\\ ORA-14404: partitioned table contains partitions in a different tablespace\\ === Find tables with partitions in more than one tablespace === set lines 2000 pages 50 col table_owner for a30 col table_name for a30 select table_owner , table_name , count(*) from dba_tab_partitions where table_owner != 'SYS' group by table_owner , table_name having count(*) > 1 / See which tables have partitions across multiple tablespaces... set lines 1000 pages 100 col table_name for a50 select distinct b.table_owner||'.'||b.table_name table_name , a.tablespace_name ts1 , b.tablespace_name ts2 from ( select distinct tablespace_name , table_name , partition_name from dba_tab_partitions ) a , dba_tab_partitions b where a.table_name = b.table_name and a.tablespace_name != b.tablespace_name / Generate statements to move the partitions from one tablespace to another... select 'alter table '||b.table_owner||'.'||b.table_name||' move partition '||b.partition_name||' tablespace &NEW_TABLESPACE;' from ( select distinct table_name , partition_name from dba_tab_partitions where tablespace_name = '&&OLD_TABLESPACE' ) a , dba_tab_partitions b where a.table_name = b.table_name and b.tablespace_name != '&OLD_TABLESPACE' / undef OLD_TABLESPACE === What's left hanging around? === col SEGMENT_TYPE for a20 col OWNER for a20 col SEGMENT_NAME for a40 col PARTITION_NAME for a15 col TABLESPACE_NAME for a30 select segment_type , owner , segment_name , partition_name , tablespace_name from dba_segments where tablespace_name in ('TS_THALER_IOT_OLD','TS_THALER_CU','TS_THALER_BACKUP','TS_THALER_PART_OLD') / select table_owner , table_name from dba_indexes where index_name = '&index_name' / ==== What edition is my database (Standard, Enterprise, XE...)? ==== Only works ob 12c and above select edition from sys.registry$ where cid='CATPROC' ==== Is database a CDB with PDBs? ==== Needs a bit of work. Won't work pre-12c obviously and also not on Standby databases if they are in MOUNT mode for db in $(ps -ef | grep [p]mon|awk -F_ '{print $NF}') do export ORACLE_SID=${db} ORAENV_ASK=NO . oraenv >/dev/null 2>&1 sqlplus -s / as sysdba<<'EOSQL' set head off newpa none select name , (select count(*) from v$pdbs) npdbs from v$database / EOSQL done version 2 function is_db_cdb return boolean is b_is_cdb boolean := false; l_is_cdb varchar2(3) := 'NO'; e_col_not_found exception; pragma exception_init(e_col_not_found, -904); begin begin execute immediate 'select cdb from v$database' into l_is_cdb; exception when e_col_not_found then l_is_cdb := 'NO'; -- ORA-00904 end; if (l_is_cdb = 'YES') then return TRUE; else return FALSE; -- either not a cdb or pre-12.1 database end if; end is_db_cdb; ==== List triggers in the database ==== This query specifically lists after login triggers select obj.con_id , pdb.name , obj.owner , obj.object_name , to_char(obj.created, 'DD/MM/YYYY HH24:MI:SS') created_str , to_char(obj.last_ddl_time, 'DD/MM/YYYY HH24:MI:SS') last_ddl_str from cdb_objects obj join cdb_triggers trgs on ( obj.con_id = trgs.con_id and obj.owner = trgs.owner and obj.object_name = trgs.trigger_name ) join v$pdbs pdb on ( obj.con_id = pdb.con_id ) where trgs.trigger_type = 'AFTER EVENT' and trgs.triggering_event like 'LOGON%' and trgs.status = 'ENABLED' order by object_name , obj.last_ddl_time / ==== List the pluggable databases (PDB) in a container database (CDB) ==== The network_name column shows what you should find in tnsnames.ora for this db connection select name,network_name,pdb from v$services; or select * from v$pdbs; ==== Start and stop a pluggable database ==== alter pluggable database pdb1 open; alter pluggable database pdb1 close immediate; ==== Swith to a pluggable database ==== alter session set container=pdb1; ==== Reopen pluggable databases at container startup ==== By default pluggable databases in a container remain in a MOUNT state when the container starts up. select name, open_mode from v$pdbs where name = 'PDB1'; To modify this, open it and save its state alter pluggable database pdb1 open; alter pluggable database pdb1 save state; To see a log of issues with pluggable databases select * from pdb_plug_in_violations where type = 'ERROR' and status != 'RESOLVED' and name = 'PDB1'; ==== What is the current container id in a cdb? ==== CDB$ROOT is container id 1. User containers start from 2. select sys_context('USERENV','CON_ID') from dual ==== What is the current container name in a cdb? ==== select sys_context('USERENV','CON_NAME') from dual or just show con_name ==== Switching Between Containers ==== SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter session set container=tstklok; Session altered. SQL> show con_name CON_NAME ------------------------------ TSTKLOK ==== Direct connection to pluggable database ==== * Check what is possible with EZCONNECT Plus [[https://medium.com/@FranckPachot/19c-easy-connect-e0c3b77968d7]]\\ These must be made using a service (defined in tnsnames.ora). Each pluggable database automatically registers a service with the listener (v$services). sqlplus appuser/apppwd@tstklok or using ezconnect SQL> conn appuser/apppwd@//localhost:1521/tstklok ==== What platform is the database running on? ==== Could be useful for handling line endings... select platform_name from v$database if instr(db_platform, 'WINDOWS') != 0 then crlf := CHR(13) || CHR(10); -- Windows gets the \r and \n else crlf := CHR (10); -- Just \n for the rest of the world end if; ==== Is the database 32 or 64 bit? ==== If the answer is 1,7,10,15,16 or 17, then it is 32bit, everything else should be 64bit. select platform_id from v$database ==== What features is my database using? ==== Before converting to Standard Edition, check the features here - some may be Enterprise specific. col name for a45 col description for a85 set lines 2000 select name , detected_usages , description from dba_feature_usage_statistics where 1=1 and currently_used = 'TRUE' / ==== How many redo log switches per hour? ==== set lines 100 col "YYYYMMDD HH24" for a14 select to_char(first_time,'yyyymmdd hh24') "YYYYMMDD HH24" , count(1) num_switches from v$log_history group by to_char(first_time,'yyyymmdd hh24') order by 1 or a bit fancier version... set lines 200 col day for a9 hea "Date\Hour" col "00" for a5 col "01" for a5 col "02" for a5 col "03" for a5 col "04" for a5 col "05" for a5 col "06" for a5 col "07" for a5 col "08" for a5 col "09" for a5 col "10" for a5 col "11" for a5 col "12" for a5 col "13" for a5 col "14" for a5 col "15" for a5 col "16" for a5 col "17" for a5 col "18" for a5 col "19" for a5 col "20" for a5 col "21" for a5 col "22" for a5 col "23" for a5 col "all" for a9 heading "All day" alter session set nls_date_format='DD-MON-YY' / select trunc(first_time) day, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06", to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18", to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21", to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23", to_char(count(to_char(first_time,'DD')),'9999') "all" from v$log_history where first_time > (sysdate-&days_back) group by trunc(first_time) order by trunc(first_time) / set lines 80 ==== Show the rate at which redo logs switch ==== rem *********************************************************** rem rem File: log_history.sql rem Description: Log switch rates from v$log_history rem rem From 'Oracle Performance Survival Guide' by Guy Harrison rem Chapter 21 Page 637 rem ISBN: 978-0137011957 rem See www.guyharrison.net for further information rem rem This work is in the public domain NSA rem rem rem ********************************************************* col min_minutes format 9999.99 col max_minutes format 9999.99 col avg_minutes format 9999.99 set pagesize 1000 set lines 70 set echo on WITH log_history AS (SELECT thread#, first_time, LAG(first_time) OVER (ORDER BY thread#, sequence#) last_first_time, (first_time - LAG(first_time) OVER (ORDER BY thread#, sequence#)) * 24* 60 last_log_time_minutes, LAG(thread#) OVER (ORDER BY thread#, sequence#) last_thread# FROM v$log_history) SELECT ROUND(MIN(last_log_time_minutes), 2) min_minutes, ROUND(MAX(last_log_time_minutes), 2) max_minutes, ROUND(AVG(last_log_time_minutes), 2) avg_minutes FROM log_history WHERE last_first_time IS NOT NULL AND last_thread# = thread# AND first_time > SYSDATE - 1; ==== Show redo log groups/members ==== set lines 1000 col instance for a8 col thread for 999 col groupno for 999 col member for a50 col redo_file_type for a15 col log_status for a10 col logsize_m for 99999 col archived for a12 SELECT i.instance_name instance , i.thread# thread , f.group# groupno , f.member member , f.type redo_file_type , l.status log_status , l.bytes/1024/1024 logsize_m , l.archived archived FROM gv$logfile f , gv$log l , gv$instance i WHERE 1=1 and f.group# = l.group# AND l.thread# = i.thread# AND i.inst_id = f.inst_id AND f.inst_id = l.inst_id ORDER BY i.instance_name , f.group# , f.member; ==== Korn shell timestamp function ==== Example usage: echo "`ts`: Checking backup status" function ts { date +'%d-%b-%Y %H:%M:%S' } ==== Drop a database ==== Dropping a database including backups should be considered criminal in a production environment! sqlplus / as sysdba startup force mount exclusive restrict exit rman target / drop database including backups noprompt; exit or RMAN> connect target sys/pass@test connected to target database: test (dbid=123456789) RMAN> startup force mount RMAN> sql 'alter system enable restricted session'; RMAN> drop database including backups noprompt; ==== Use sub-select or in-line views to update base tables ==== Inline views can be used to update base table data update ( select p.list_price from products p , product_categories pc where 1=1 and p.category_id = pc.category_id and pc.category_name = 'CPU' ) set list_price = list_price * 1.15; ==== Use sub-select or in-line views to delete base table data ==== A different way to delete rows in base tables, using Inline views or subselect delete ( select p.list_price from products p , product_categories pc where 1=1 and p.category_id = pc.category_id and pc.category_name = 'CPU' ) where list_price < 1000; ==== Mass update of files using perl inline script ==== for server in `cat /home/tools/etc/oracle/oracle_servers`; do ssh $server “perl -p -i -e 's/T01/D01/' /home/tools/scripts/rman/inclexcl.lst” done ==== How to use vi-style editing in SQL*Plus ==== These instructions are for Redhat but other versions will be very similar * Download rlwrap from [[http://dl.fedoraproject.org/pub/epel/5/x86_64/repoview/rlwrap.html]] or [[http://dl.fedoraproject.org/pub/epel/6/x86_64/repoview/rlwrap.html]] * sudo yum install rlwrap * build a dictionary file for autocomplete on pressing Tab vi $HOME/sql.dict select from dba_users dba_data_files dba_tablespaces alias sysdba='rlwrap -f $HOME/sql.dict sqlplus / as sysdba' or for general command use... alias sqlplus='rlwrap -D2 -ic sqlplus' alias dgmgrl='rlwrap -D2 -ic dgmgrl' alias rman='rlwrap -D2 -ic rman' alias asmcmd='rlwrap -D2 -ic asmcmd' alias lsnrctl='rlwrap -D2 -ic lsnrctl' alias adrci='rlwrap -D2 -ic adrci' alias impdp='rlwrap -D2 -ic impdp' alias expdp='rlwrap -D2 -ic expdp ==== Execute an SQL statement on all databases on all servers for a customer ==== Run from customer's management server ./dosh -c "su - oracle -c '/home/tools/scripts/oracle/all_db_do -v \"select username, account_status, profile from dba_users order by 3;\"'" >user_status.lst or SCRIPTS_DIR=/home/tools/scripts/oracle $SCRIPTS_DIR/dosh -c "su - oraibm -c \"$SCRIPTS_DIR/all_db_do 'alter user ops\\\$oraibm profile oracle_system_user;'\"" $SCRIPTS_DIR/dosh2 -c "su - oraibm -c \"$SCRIPTS_DIR/all_db_do 'alter user ops$oraibm profile oracle_system_user;'\"" $SCRIPTS_DIR/dosh2 -c "su - oraibm -c \"$SCRIPTS_DIR/all_db_do 'alter system set control_file_record_keep_time=31;'\"" ==== Where is the alert log/alertlog? ==== Older systems select value from v$parameter where name='background_dump_dest'; Newer systems select value from v$diag_info where name='Diag Trace'; ==== Grant normal users access to the alertlog table (sys.x$dbgalertext) ==== create view x$dbgalertext_v as select * from x$dbgalertext; grant select on x$dbgalertext_v to &&grantee; create or replace synonym &grantee..x$dbgalertext for sys.x$dbgalertext_v; undef grantee ==== Search alert log for ORA-00600 and ORA-07445 errors ==== Scan the database table version of the alertlog for errors without reporting what was already found. select indx, message_text from x$dbgalertext where message_text like '%ORA-00600%' or message_text like '%ORA-07445%' and indx > &last_queried_indx order by indx desc; ==== Simulating errors in alert log ==== Sometimes you need to inject error messages into the alertlog to test your monitoring system. This generates the entries in the alertlog and in x$dbgalertext table. exec sys.dbms_system.ksdwrt(2, 'ORA-07445: Testing this error code for Zenoss, do not investigate.'); This additionally creates a trace file. exec sys.dbms_system.ksdwrt(3, 'ORA-00600: Testing this error code for Zenoss, do not investigate.'); ==== tail the database alert log ==== If an ADR error is displayed, then ORACLE_SID is probably not set alias tailalertlog='adrci exec="set home diag/rdbms/$(echo $ORACLE_SID | tr A-Z a-z)/$ORACLE_SID; show alert -tail -f"' ==== What is my WAN ip address? ==== curl -A "Mozilla/4.0" http://checkip.dyndns.org/ 2>/dev/null | perl -ne 'print "$1\n" if /Current IP Address: (\d+\.\d+\.\d+\.\d+)/' or curl -A "Mozilla/4.0" http://myip.dnsdynamic.org/ 2>/dev/null ==== Wait for child pid to finish in background and report its status ==== # submit a process to the background export_schema1.ksh & bgpid=$! while (ps -ef | grep $bgpid | grep -v grep); do # still running... sleep 600 done # should be finished wait $bgpid bgstatus=$? echo background process ended with status $bgstatus or #!/usr/bin/ksh # submit a few processes to the background # wait for them all to finish # concat their logfiles (./sleeper.sh;echo "sleeper 1 finished")>sleeper1.log & PIDLIST="$PIDLIST $!" echo "PIDLIST is $PIDLIST ..." sleep 5 (./sleeper.sh;echo "sleeper 2 finished")>sleeper2.log & PIDLIST="$PIDLIST $!" echo "PIDLIST is $PIDLIST ..." sleep 5 (./sleeper.sh;echo "sleeper 3 finished")>sleeper3.log & PIDLIST="$PIDLIST $!" echo "PIDLIST is $PIDLIST ..." sleep 5 echo "Now 15 seconds later, we have 3 pids: $PIDLIST" for PID in $PIDLIST; do wait $PID echo "$PID exited with status $?" done # all jobs should have ended, concat their logs cat sleeper*log > sleepers.log cat sleepers.log Real world example #!/usr/bin/ksh # ============================================================================== # Name : export_JDBEOP1.ksh # Description : Run export in pieces for performance reasons # # Parameters : # # Notes : Also decide whether to exclude PDARC schema or not # depending on what day it is # # # Modification History # ==================== # When Who What # ========= ================= ================================================== # 15-OCT-13 Stuart Barkley Created # ============================================================================== STARTTIME=`date '+%Y%m%d%H%M%S'` DAYNO=`date +%u` PROGNAME=`basename $0` SCRIPTS_DIR=/home/tools/scripts/oracle DATA_DIR=/oracle/JDBEOP1/admin/change EXPORT_DIR=/oracle/export/JDBEOP1 # marker so we can find our files later MARKERFILE=/tmp/start_$PROGNAME_$$ touch $MARKERFILE $SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u PRODDTA -p 4 -k 2 -f $DATA_DIR/JDBEOP1_PRODDTA.parfile & PIDLIST="$PIDLIST $!" echo "PIDLIST is $PIDLIST ..." $SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u PRODCTL -p 2 -k 2 -f $DATA_DIR/JDBEOP1_PRODCTL.parfile & PIDLIST="$PIDLIST $!" echo "PIDLIST is $PIDLIST ..." $SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u NOPRODNOPDARC -p 4 -k 2 -f $DATA_DIR/JDBEOP1_NOPROD_NOPDARC.parfile & PIDLIST="$PIDLIST $!" echo "PIDLIST is $PIDLIST ..." if [[ $DAYNO -eq 1 ]]; then $SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u NOPROD -p 4 -k 2 -f $DATA_DIR/JDBEOP1_NOPROD.parfile & PIDLIST="$PIDLIST $!" echo "PIDLIST is $PIDLIST ..." fi # now sit and wait for the exports to finish for PID in $PIDLIST; do echo "waiting for pid $PID to finish" wait $PID echo "$PID exited with status $?" done # collect all log files up into one cd $EXPORT_DIR for i in `find . -name "expdp_JDBEOP1*log" -newer $MARKERFILE`; do cat $i > expdp_JDBEOP1_D_FULL_${STARTTIME}.log done rm $MARKLERFILE ==== Create private DB link for a user without knowing his password ==== * Inspired by [[http://oradbatips.blogspot.co.uk/2010/01/tip-84-create-private-db-link-for-user.html|oradbatips.blogspot.co.uk]] It uses a clever package, dbms_sys_sql.\\ Also useful for executing anything on behalf of another user.\\ Here is an example on how to create private database link for a user. create or replace procedure link_creator ( p_owner varchar2, p_dblink_name varchar2, p_username varchar2, p_password varchar2, p_address varchar2 ) as uid number; sqltext varchar2(1000) := 'create [public] database link [link_name] connect to [username] identified by [password] using ''[address]'''; myint integer; begin if upper(p_owner) = 'PUBLIC' then sqltext := replace(sqltext, '[public]', 'public'); else sqltext := replace(sqltext, '[public]', ''); end if; sqltext := replace(sqltext, '[linkname]', p_dblink_name); sqltext := replace(sqltext, '[username]', p_username); sqltext := replace(sqltext, '[password]', p_password); sqltext := replace(sqltext, '[address]', p_address); select user_id into uid from dba_users where username = decode(upper(p_owner), 'PUBLIC', 'SYS', p_owner) ; myint:=sys.dbms_sys_sql.open_cursor(); sys.dbms_sys_sql.parse_as_user ( myint, sqltext, dbms_sql.native, uid ); sys.dbms_sys_sql.close_cursor(myint); end; / Here we can generate SQL to rebuild the links for future use. Note the password is no longer stored in this column. set pages 1000 select 'begin link_creator ( '''|| u.name ||''','''|| l.name ||''','''|| l.userid ||''','''|| l.password ||''','''|| l.host ||'''); end;'|| chr(10) ||'/' link_text from sys.link$ l , sys.user$ u where l.owner# = u.user# ; === Another method to create a db_link from sys on behalf of a user is to create a procedure under that users name that does the db link creation === grant create database link to &&user; create or replace procedure &user..create_db_link as begin execute immediate 'create database link &link_name connect to &user identified by &users_password using ''&tns_connect_identifier'''; end create_db_link; exec &user..create_db_link; revoke create database link from &user; drop procedure &user..create_db_link; undef user ==== Create a db_link by creating a procedure in the schema ==== Although it is not possible to create a database link on behalf of another user from sys (unless dbms_sql is used), we can create a procedure for another schema! TMPFILE="/tmp/results.createdb_appli_${SID}.sql" cat<"${TMPFILE}" whenever sqlerror exit failure; grant create database link to ${CREATING_SCHEMA}; grant select on sys.dba_db_links to ${CREATING_SCHEMA}; set pages 0 newpa none lines 1000 feed off serveroutput on create or replace procedure ${CREATING_SCHEMA}.create_db_link as l_db_link varchar2(4000); l_stmt varchar2(4000); begin dbms_output.enable(null); select db_link into l_db_link from sys.dba_db_links where owner = '${CREATING_SCHEMA}' and db_link like '%${SID_UPPER}%'; dbms_output.put_line('Database link '||l_db_link||' already exists.'); exception when no_data_found then select 'create database link "DBL_${SID}.WORLD" connect to dbsnmp identified by ${SYS_PASSWORD} using ''${SID}.world''' into l_stmt from dual; dbms_output.put_line(l_stmt); execute immediate l_stmt; end create_db_link; / execute ${CREATING_SCHEMA}.create_db_link; revoke create database link from ${CREATING_SCHEMA}; drop procedure ${CREATING_SCHEMA}.create_db_link; EOSQL sqlplus -s sys/${SYS_PASSWORD}@${APPLI_NET_CONNECTOR} as sysdba</tmp/results.$$ whenever sqlerror exit failure; @${TMPFILE} EOSQL ==== Create a db_link on behalf of another user from sys using dbms_sql from sys ==== This version has the same effect as the one above (using a procedure) but this one cannot be used on old versions of Oracle create or replace procedure create_db_link ( p_schema_name in varchar2 , p_db_link in varchar2 , p_connect_usr in varchar2 default 'dbsnmp' , p_connect_pwd in varchar2 default 'password' ) is l_plsql varchar2(4000); l_cur number; l_uid number; l_rc number; begin select du.user_id into l_uid from dba_users du where 1=1 and upper(du.username) = upper(p_schema_name); l_plsql := 'create database link "'||p_db_link||'" connect to '||p_connect_usr||' identified by '||p_connect_pwd||' using '''||p_schema_name||'.world'''; dbms_output.put_line(l_plsql); l_cur := sys.dbms_sys_sql.open_cursor; sys.dbms_sys_sql.parse_as_user ( c => l_cur , statement => l_plsql , language_flag => dbms_sql.native , userid => l_uid ); l_rc := sys.dbms_sys_sql.execute(l_cur); sys.dbms_sys_sql.close_cursor(l_cur); end create_db_link; / exec create_db_link ('db_sizing', 'dbl_atlas.world'); ==== Find Materialized views in a specified tablespace ==== select s.sowner mv_owner, s.tname mv_name, p.name tablespace from sys.obj$ o, sys.tab$ t, sys.user$ u, sys.snap$ s , sys.ts$ p where t.ts#=p.ts# and p.name='&TABLESPACE' and t.obj#=o.obj# and o.owner#=u.user# and u.name=s.sowner and o.name=s.tname order by 1,2 / ==== Change database global name ==== This affects databases that connect to this one via database links (where parameter global_names = true)\\ Assuming db_name is 'appli3a' and db_domain is 'world' alter database rename global_name to appli3a.world; ==== Find Materialized view indexes in a specified tablespace ==== select o.owner, o.object_name index_name, o.object_type, sq.mv_owner,sq.mv_name,sq.tablespace from dba_objects o, (select i.obj#,s.sowner mv_owner, s.tname mv_name, p.name tablespace from sys.obj$ o, sys.tab$ t, sys.ind$ i, sys.user$ u, sys.snap$ s,sys.ts$ p where i.ts#=p.ts# and p.name='&TABLESPACE' and i.bo#=t.obj# and t.obj#=o.obj# and o.owner#=u.user# and u.name=s.sowner and o.name=s.tname ) sq where sq.obj#=o.object_id order by 1,2,3 / ==== Create a comma separated list of columns from a select statement ==== * [[http://stackoverflow.com/questions/5324996/comma-separated-list-as-a-result-of-select-statement-in-oracle|comma separated list of columns from a select statement - stackoverflow.com]] * [[http://www.salvis.com/blog/?p=207|A whole page showing how to do it in all versions back to 7!]] Method 1: SELECT parent_id, RTRIM(XMLAGG(XMLELEMENT(e,child_id || ',')).EXTRACT('//text()'),',') AS "Children" FROM parentChildTable WHERE parent_id = 0 GROUP BY parent_id / or SELECT parent_id, LISTAGG(child_id, ',') WITHIN GROUP (ORDER BY child_id) AS "Children" FROM parentChildTable WHERE parent_id = 0 GROUP BY parent_id / Method 2 (undocumented and cannot therefore be relied on to continue working in the same manner): SELECT wmsys.wm_concat() FROM / ==== Start an instant one-line webserver in current directory ==== Need to quickly share/copy a file or read an html file on Linux? Start a web server! python -m SimpleHTTPServer 8000 and point your browser to http://localhost:8000\\ or while true; do nc -l -p 80 -q 1 < index.html; done ==== Get SQL*Plus to generate HTML ==== * [[http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_seven.htm#CHDCECJG|official guide]] One way to do it... use -m(arkup) option to specify:\\ html on/off - specifies whether to output html output or not head - specify your own customised head contents\\ spool off - as a part of the -m tag means do not print the default <html>, <head> and <body> tags\\ sqlplus -m "html on head '' spool off" / as sysdba or sqlplus / as sysdba set markup html on head '' body "" table "" spool off besides html and head, you can also specify:\\ body - specify your own customised body attributes\\ table - override the default table options\\ entmap - turn on or off the html replacement characters (<, >, etc...)\\ preformat - uses the <pre> tag to format output exactly as required\\ * another little gem... ---------------------------------------- -- get the last SQL*Plus output in HTML -- after Tanel Poder ---------------------------------------- set termout off set markup HTML ON HEAD " - - " - BODY "" - TABLE "border='1' align='center' summary='Script output'" - SPOOL ON ENTMAP ON PREFORMAT OFF spool myoutput.html l / spool off set markup html off spool off host firefox myoutput.html set termout on ==== HTML SELECT - Trigger JavaScript ONCHANGE event even when the option is not changed ==== ==== Display horizontal bar graph in HTML table data cell ==== Using Perl print ('' . '
' . '
' . $tspace . '' ); Depending on which way around the divs are, you get different interpretations print ("" . "
99)?(100):($automaxpercused),"%;\" />" . "
" . $tspace . "" );