snippets
Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
| snippets [2018/12/06 21:05] – created 91.177.234.129 | snippets [2024/06/18 11:54] (current) – external edit 127.0.0.1 | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Snippets | + | ==== Drop system generated hidden columns (statistics) |
| + | < | ||
| + | BEGIN | ||
| + | DBMS_STATS.DROP_EXTENDED_STATS('< | ||
| + | END; | ||
| + | / | ||
| + | </ | ||
| + | After procedure executed please verify extended statistics have been successfully removed | ||
| + | < | ||
| + | SELECT column_name, | ||
| + | FROM all_tab_cols | ||
| + | WHERE table_name | ||
| + | AND owner = ' | ||
| + | AND column_name LIKE ' | ||
| + | </ | ||
| + | After dropping the extended statistics, the system-generated virtual columns should be removed. | ||
| - | =====Protecting an Apache Web Server directory with htaccess===== | + | NOTE: To drop the individual column |
| - | * [[https:// | + | < |
| - | * [[http:// | + | BEGIN |
| - | Two files are needed.<br /> | + | DBMS_STATS.DROP_EXTENDED_STATS(' |
| + | 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 | ||
| + | r_version varchar2(17); | ||
| + | BEGIN | ||
| + | LOOP | ||
| + | -- find the digit | ||
| + | l_digit := regexp_substr(l_version,' | ||
| + | -- strip the digit and the . (if it exists) | ||
| + | IF l_version is null THEN | ||
| + | r_version := r_version || ' | ||
| + | ELSE | ||
| + | l_version := regexp_replace(l_version,' | ||
| + | r_version := r_version || lpad(to_char(l_digit,' | ||
| + | END IF; | ||
| + | exit when length(r_version) >= 10; | ||
| + | END LOOP; | ||
| + | RETURN r_version; | ||
| + | END COMP_DB_VERSION; | ||
| + | |||
| + | </ | ||
| + | |||
| + | < | ||
| + | select username | ||
| + | , profile | ||
| + | from | ||
| + | where ( | ||
| + | | ||
| + | | ||
| + | | ||
| + | not regexp_like (username, ' | ||
| + | ) | ||
| + | </ | ||
| + | |||
| + | ==== 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 ' | ||
| + | |||
| + | select 'alter database tempfile ''' | ||
| + | |||
| + | select 'alter database tempfile ''' | ||
| + | |||
| + | 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 " | ||
| + | < | ||
| + | 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 | ||
| + | col mount_point | ||
| + | col target_value for a70 | ||
| + | col filesystem | ||
| + | |||
| + | select target_name host_name | ||
| + | , key_value mount_point | ||
| + | , MAX(DECODE(column_label,' | ||
| + | , SUM(DECODE(column_label,' | ||
| + | , SUM(DECODE(column_label,' | ||
| + | , SUM(DECODE(column_label,' | ||
| + | from | ||
| + | where target_name = '& | ||
| + | and metric_name = ' | ||
| + | group BY target_name | ||
| + | , key_value | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== ddclient.conf file when using Cloudflare ==== | ||
| + | * [[https:// | ||
| + | ddclient nows keeps its configuration file in / | ||
| + | < | ||
| + | daemon=600 # | ||
| + | syslog=yes # | ||
| + | # | ||
| + | mail-failure=root # | ||
| + | pid=/ | ||
| + | ssl=yes # | ||
| + | use=web, web=checkip.dyndns.org/, | ||
| + | protocol=cloudflare, | ||
| + | zone=stuartbarkley.com, | ||
| + | server=www.cloudflare.com, | ||
| + | login=< | ||
| + | password=< | ||
| + | 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 ' | ||
| + | < | ||
| + | ll / | ||
| + | sudo chown ddclient / | ||
| + | sudo chgrp ddclient / | ||
| + | </ | ||
| + | Another reason could be due to the fact that there are duplicate lines in / | ||
| + | |||
| + | Does it fail to update? Check the log. On fedora, check / | ||
| + | < | ||
| + | WARNING: | ||
| + | </ | ||
| + | Stop the service, delete the cache file and restart the service | ||
| + | < | ||
| + | sudo systemctl stop ddclient | ||
| + | sudo rm / | ||
| + | sudo systemctl start ddclient | ||
| + | sudo systemctl status ddclient | ||
| + | </ | ||
| + | < | ||
| + | ● ddclient.service - A Perl Client Used To Update Dynamic DNS | ||
| + | | ||
| + | | ||
| + | Process: 4036 ExecStart=/ | ||
| + | Process: 4033 ExecStartPre=/ | ||
| + | Main PID: 4047 (ddclient - read) | ||
| + | Tasks: 1 (limit: 4915) | ||
| + | | ||
| + | | ||
| + | | ||
| + | |||
| + | 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]: | ||
| + | Jan 16 17:05:43 fedora ddclient[4049]: | ||
| + | </ | ||
| + | ==== 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 ' | ||
| + | </ | ||
| + | |||
| + | ==== Default variables in SQL*Plus script ==== | ||
| + | * [[https:// | ||
| + | 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=' | ||
| + | def DEFAULT_CRIT_LEVEL=' | ||
| + | |||
| + | 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 | ||
| + | where 1=2 | ||
| + | / | ||
| + | |||
| + | rem if values were passed in, use them, if not, use default | ||
| + | select nvl('& | ||
| + | , nvl('& | ||
| + | from | ||
| + | / | ||
| + | |||
| + | rem because of the way new_value works, we now have the values in &1 and &2 | ||
| + | def warn_level='& | ||
| + | def crit_level='& | ||
| + | </ | ||
| + | |||
| + | Another version with one variable | ||
| + | < | ||
| + | var l_default_password varchar2(4000) | ||
| + | exec : | ||
| + | |||
| + | set lines 1000 pages 100 verif off | ||
| + | col cgkpwd new_value 1 nopri | ||
| + | select null cgkpwd from dual where 1=2; | ||
| + | select nvl('& | ||
| + | def cgkpwd = "& | ||
| + | </ | ||
| + | This can now be used in PL/SQL like this | ||
| + | < | ||
| + | l_stmt := ' | ||
| + | </ | ||
| + | |||
| + | Here is an example found in $ORACLE_HOME/ | ||
| + | < | ||
| + | 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 | ||
| + | SELECT NULL " | ||
| + | SELECT NVL('&& | ||
| + | |||
| + | COLUMN 2 NEW_VALUE | ||
| + | SELECT NULL " | ||
| + | SELECT NVL('&& | ||
| + | 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 | ||
| + | ------------------------------ -------------------- | ||
| + | AVG_BUSY_TIME | ||
| + | AVG_IDLE_TIME | ||
| + | AVG_IOWAIT_TIME | ||
| + | AVG_SYS_TIME | ||
| + | AVG_USER_TIME | ||
| + | BUSY_TIME | ||
| + | GLOBAL_RECEIVE_SIZE_MAX | ||
| + | GLOBAL_SEND_SIZE_MAX | ||
| + | IDLE_TIME | ||
| + | IOWAIT_TIME | ||
| + | LOAD 187.662109375 | ||
| + | NUM_CPUS | ||
| + | NUM_CPU_CORES | ||
| + | NUM_LCPUS | ||
| + | NUM_VCPUS | ||
| + | OS_CPU_WAIT_TIME | ||
| + | PHYSICAL_MEMORY_BYTES | ||
| + | RSRC_MGR_CPU_WAIT_TIME | ||
| + | SYS_TIME | ||
| + | TCP_RECEIVE_SIZE_DEFAULT | ||
| + | TCP_RECEIVE_SIZE_MAX | ||
| + | TCP_RECEIVE_SIZE_MIN | ||
| + | TCP_SEND_SIZE_DEFAULT | ||
| + | TCP_SEND_SIZE_MAX | ||
| + | TCP_SEND_SIZE_MIN | ||
| + | USER_TIME | ||
| + | VM_IN_BYTES | ||
| + | VM_OUT_BYTES | ||
| + | |||
| + | 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 ' | ||
| + | </ | ||
| + | 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 / | ||
| + | In this case, resorting to 'old school' | ||
| + | ex (and ed) syntax is pretty much the same as vi. | ||
| + | < | ||
| + | ex /etc/oratab << | ||
| + | 1, | ||
| + | g/ | ||
| + | %s/ | ||
| + | x | ||
| + | EOEX | ||
| + | </ | ||
| + | Remove trailing blanks | ||
| + | < | ||
| + | 1, | ||
| + | </ | ||
| + | Change all lines in a file to copy what is on the line and repeat it (with quotes and a word in between) | ||
| + | < | ||
| + | 1, | ||
| + | </ | ||
| + | Replace lines containing if conditions with multiple lines | ||
| + | < | ||
| + | 1, | ||
| + | : | ||
| + | else | ||
| + | echo " | ||
| + | fi/ | ||
| + | x | ||
| + | </ | ||
| + | Delete all lines from the start of the file upto but not including the one starting with CREATE CONTROLFILE | ||
| + | < | ||
| + | 1,/^CREATE CONTROLFILE/ | ||
| + | </ | ||
| + | Position the file pointer on the one starting with CREATE CONTROLFILE and change it | ||
| + | < | ||
| + | /^CREATE CONTROLFILE/ | ||
| + | s/" | ||
| + | s/ | ||
| + | s/ | ||
| + | w | ||
| + | q | ||
| + | </ | ||
| + | Add a line to a file after a specific line | ||
| + | < | ||
| + | ex<< | ||
| + | /Executing agentDeploy.sh/ | ||
| + | a | ||
| + | perl -p -i -e ' | ||
| + | . | ||
| + | w | ||
| + | q | ||
| + | EOEX | ||
| + | </ | ||
| + | |||
| + | ==== 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 '/ | ||
| + | </ | ||
| + | |||
| + | ==== 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 " | ||
| + | </ | ||
| + | produces something like this | ||
| + | < | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | </ | ||
| + | |||
| + | ==== 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/ | ||
| + | SQL> @$ORACLE_HOME/ | ||
| + | SQL> @$ORACLE_HOME/ | ||
| + | SQL> @$ORACLE_HOME/ | ||
| + | SQL> @$ORACLE_HOME/ | ||
| + | SQL> shutdown immediate; | ||
| + | SQL> startup | ||
| + | |||
| + | </ | ||
| + | ==== Controlfile Autobackup written to ORACLE_HOME/ | ||
| + | The trick is that the word "# | ||
| + | < | ||
| + | configure controlfile autobackup format for device type disk clear; | ||
| + | </ | ||
| + | |||
| + | ==== Protecting an Apache Web Server directory with htaccess ==== | ||
| + | * | ||
| + | * | ||
| + | * [[http:// | ||
| + | Two files are needed.\\ | ||
| **The .htaccess Code** | **The .htaccess Code** | ||
| - | < | + | < |
| + | AuthType Basic | ||
| + | AuthName " | ||
| + | AuthUserFile / | ||
| + | require valid-user | ||
| + | </ | ||
| **The .htpasswd Code** | **The .htpasswd Code** | ||
| - | < | + | < |
| - | =====Protecting a lighttpd directory with htaccess===== | + | davidwalsh: |
| + | rodstewart: | ||
| + | cssexpert: | ||
| + | </ | ||
| + | ==== Protecting a lighttpd directory with htaccess ==== | ||
| Generate an MD5 hash with the tools above and paste into this file | Generate an MD5 hash with the tools above and paste into this file | ||
| - | < | + | < |
| + | vi / | ||
| + | stuart: | ||
| + | </ | ||
| Check modules.conf file to ensure mod_auth and mod_rewrite are enabled | Check modules.conf file to ensure mod_auth and mod_rewrite are enabled | ||
| - | < | + | < |
| + | vi / | ||
| + | </ | ||
| Alter the lighttpd.conf file to allow the authentication | Alter the lighttpd.conf file to allow the authentication | ||
| - | < | + | < |
| + | #auth directives | ||
| + | auth.backend = " | ||
| + | auth.backend.htpasswd.userfile = "/ | ||
| + | auth.debug = 1 | ||
| + | $HTTP[" | ||
| + | auth.require = ( "" | ||
| + | ( | ||
| + | " | ||
| + | " | ||
| + | " | ||
| + | ) ) | ||
| + | } | ||
| + | </ | ||
| Restart the lighttpd server | Restart the lighttpd server | ||
| - | < | + | < |
| - | I tried it with htdigest but could not get it to protest | + | systemctl stop lighttpd |
| - | < | + | systemctl start lighttpd |
| + | </ | ||
| + | I tried it with htdigest but could not get it to protect | ||
| + | < | ||
| + | # | ||
| + | # | ||
| + | # | ||
| + | # "/ | ||
| + | # " | ||
| + | # " | ||
| + | # " | ||
| + | # ), | ||
| + | #) | ||
| + | </ | ||
| - | =====Rename a datafile if the filename contains junk / unprintable characters===== | + | ==== Rename a datafile if the filename contains junk / unprintable characters ==== |
| - | < | + | < |
| - | * Oracle have a document that describes a few ways of doing it in [[https:// | + | alter tablespace ts_thaler_data offline; |
| - | If the filename contains control 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 | + | </ |
| - | < | + | * Oracle have a document that describes a few ways of doing it in [[https:// |
| + | If the filename contains control | ||
| + | < | ||
| + | ls -balti | ||
| + | total 47474984 | ||
| + | 12409 -rw-r----- | ||
| + | 12350 -rw-r----- | ||
| + | |||
| + | 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:// | Work out what the database thinks the name is and rename it there too! AskTom has a discussion on it [[https:// | ||
| - | < | + | < |
| + | select substr(name, | ||
| + | SUBS ASCII(SUBSTR(NAME, | ||
| + | ---- ----------------------- | ||
| + | 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' | You can see there' | ||
| - | < | + | < |
| - | =====Generate creation of directory names from dba_directories===== | + | set serveroutput on |
| + | declare | ||
| + | | ||
| + | | ||
| + | begin | ||
| + | select name into fname1 from v$datafile where file# = 9; | ||
| + | fname2 := replace(fname1, | ||
| + | |||
| + | dbms_output.put_line(' | ||
| + | execute immediate 'alter database rename file ''' | ||
| + | end; | ||
| + | / | ||
| + | |||
| + | alter tablespace ts_thaler_data_online; | ||
| + | </ | ||
| + | or another occasion | ||
| + | < | ||
| + | alter tablespace portfolio offline; | ||
| + | |||
| + | ls -balti *dbf2* | ||
| + | 207 -rw-r----- | ||
| + | |||
| + | chown oracle: | ||
| + | |||
| + | find . -inum 207 -exec mv {} portfolio_data12.dbf \; | ||
| + | |||
| + | |||
| + | declare | ||
| + | | ||
| + | | ||
| + | begin | ||
| + | select name into fname1 from v$datafile where file# = 255; | ||
| + | dbms_output.put_line(' | ||
| + | execute immediate 'alter database rename file ''' | ||
| + | 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 '& | ||
| + | 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 '& | ||
| + | </ | ||
| + | |||
| + | Note: | ||
| + | * When the ALTER SYSTEM set events command is used only new sessions see the events set by this command. | ||
| + | * | ||
| + | |||
| + | 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 | Before deleting or dropping directories in the database, use this script to generate the create statements | ||
| - | < | + | < |
| + | select ' | ||
| + | </ | ||
| - | =====Check if current (dot) directory is in PATH variable===== | + | ==== Check if current (dot) directory is in PATH variable ==== |
| - | Using bareword comparison to check PATH variable< | + | Using bareword comparison to check PATH variable< |
| + | f [[ :$PATH: == *:" | ||
| + | echo "in path" | ||
| + | else | ||
| + | echo "not in path" | ||
| + | fi | ||
| + | </ | ||
| - | =====tail a logfile from within the script you are writing it to===== | + | ==== tail a logfile from within the script you are writing it to ==== |
| - | < | + | < |
| - | =====Dump package, procedure etc. from dba_source in a way that it can be used to recreate it===== | + | # |
| + | # display logfile in real time | ||
| + | # | ||
| + | ( tail -0f ${LOGFILE} ) & | ||
| + | tailPID=$! | ||
| + | |||
| + | trap "kill $tailPID" | ||
| + | </ | ||
| + | ==== Dump package, procedure etc. from dba_source in a way that it can be used to recreate it ==== | ||
| From [[https:// | From [[https:// | ||
| - | < | + | < |
| + | set lines 1000 pages 1000 feedb off verif off | ||
| + | select decode( type||' | ||
| + | | ||
| + | text text | ||
| + | from | ||
| + | where owner = upper('& | ||
| + | and name = upper('& | ||
| + | order by type | ||
| + | , line | ||
| + | / | ||
| + | </ | ||
| - | =====A trick to exit out of SQL*Plus (using divide by zero) depending on answer to a question===== | + | ==== How to detect and count user's failed logon attempts ==== |
| + | * [https:// | ||
| + | < | ||
| + | SELECT | ||
| + | TO_CHAR(TIMESTAMP,' | ||
| + | SUBSTR(OS_USERNAME, | ||
| + | SUBSTR(USERNAME, | ||
| + | SUBSTR(TERMINAL, | ||
| + | ACTION_NAME, | ||
| + | RETURNCODE | ||
| + | FROM | ||
| + | SYS.DBA_AUDIT_SESSION | ||
| + | WHERE | ||
| + | USERNAME LIKE ' | ||
| + | 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 | ||
| This example is taken from ReviewLite. | This example is taken from ReviewLite. | ||
| - | < | + | < |
| + | -- Settings for customized functionality | ||
| + | define SCRIPT_OO=_OPTIONS_ONLY -- collect only options information | ||
| + | define SCRIPT_OO='' | ||
| + | |||
| + | define SCRIPT_TS=_TIME_STAMP | ||
| + | define SCRIPT_TS=' | ||
| + | |||
| + | -- PROMT FOR LICENSE AGREEMENT ACCEPTANCE | ||
| + | DEFINE LANSWER=N | ||
| + | SET TERMOUT ON | ||
| + | ACCEPT & | ||
| + | |||
| + | -- FORCE " | ||
| + | -- WILL ALSO CONTINUE IF SCRIPT_TS SUBSTITUTION VARIABLE IS NOT NULL | ||
| + | SET TERMOUT OFF | ||
| + | WHENEVER SQLERROR EXIT | ||
| + | select 1/ | ||
| + | WHENEVER SQLERROR CONTINUE | ||
| + | SET TERMOUT ON | ||
| + | </ | ||
| + | |||
| + | ==== How to uninstall optional components such as OWB, APEX, EM, OLAP, OWM from an Oracle database ==== | ||
| + | * [[http:// | ||
| - | =====How to uninstall optional components such as OWB, APEX, EM, OLAP, OWM from an Oracle database===== | + | ==== Find unique indexes / primary index / key columns in a table ==== |
| - | | + | < |
| + | col owner for a12 | ||
| + | col table_name | ||
| + | col column_name for a32 | ||
| + | col position | ||
| + | col status | ||
| + | set lines 1000 pages 100 | ||
| - | =====Find unique indexes / primary index / key columns in a table===== | + | SELECT cons.owner |
| - | < | + | , cols.table_name |
| + | , cols.column_name | ||
| + | , cols.position | ||
| + | , cons.status | ||
| + | FROM | ||
| + | , all_cons_columns cols | ||
| + | WHERE 1=1 | ||
| + | and cols.owner | ||
| + | and cols.table_name | ||
| + | AND cons.constraint_type | ||
| + | AND cons.constraint_name | ||
| + | AND cons.owner | ||
| + | ORDER BY cols.table_name | ||
| + | , cols.position | ||
| + | / | ||
| + | </ | ||
| - | =====Display the oracle instances running on the local server===== | + | ==== Display the oracle instances running on the local server ==== |
| sed could be shorter but this one works cross-platform | sed could be shorter but this one works cross-platform | ||
| - | < | + | < |
| + | alias oenv=' | ||
| + | </ | ||
| - | =====Display which databases are scheduled for backup in cron===== | + | ==== Display which databases are scheduled for backup in cron ==== |
| - | < | + | < |
| + | for i in `ps -ef | grep [o]ra_pmon | awk -F_ ' | ||
| + | crontab -l | grep $i | grep backup_export | awk ' | ||
| + | done | ||
| + | </ | ||
| - | =====Trace SQL statements using autotrace and explain plan===== | + | ==== 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 | If the plustrace (plustrc.sql) role has been granted, explaining sql statements (without running the statement) is as easy as | ||
| - | < | + | < |
| - | =====Write to a trace log from PL/SQL===== | + | alter session set sql_trace=true; |
| - | < | + | set autotrace traceonly explain |
| - | =====Invisible / hidden / virtual columns===== | + | </ |
| - | Article showing how to dump blocks also.<br /> | + | ==== Interact with (run commands on) the O/S from inside SQL and PL/SQL ==== |
| + | Again, this code found in $ORACLE_HOME/ | ||
| + | < | ||
| + | VARIABLE osCreateDirCmd | ||
| + | VARIABLE osCreateDirCmd2 VARCHAR2(4000) | ||
| + | ... | ||
| + | declare | ||
| + | homeDir | ||
| + | useDir | ||
| + | rdbmsLogDir | ||
| + | logDir | ||
| + | ... | ||
| + | begin | ||
| + | ... | ||
| + | -- set a default command | ||
| + | : | ||
| + | |||
| + | ... | ||
| + | dbms_system.get_env(' | ||
| + | ... | ||
| + | useDir := RTRIM(useDir, | ||
| + | logDir := dbms_assert.enquote_literal( | ||
| + | useDir | ||
| + | || '/ | ||
| + | || uniqueName | ||
| + | || '/ | ||
| + | rdbmsLogDir := homeDir || '/ | ||
| + | ... | ||
| + | : | ||
| + | : | ||
| + | ... | ||
| + | end; | ||
| + | ... | ||
| + | COLUMN create_cmd NEW_VALUE create_cmd NOPRINT | ||
| + | SELECT : | ||
| + | HOST & | ||
| + | SELECT : | ||
| + | HOST & | ||
| + | |||
| + | </ | ||
| + | |||
| + | ==== Write to a trace log from PL/SQL ==== | ||
| + | < | ||
| + | dbms_system.ksdwrt(1,' | ||
| + | </ | ||
| + | ==== Invisible / hidden / virtual columns ==== | ||
| + | Article showing how to dump blocks also.\\ | ||
| [[https:// | [[https:// | ||
| - | =====Write to the alert log from PL/SQL===== | + | ==== Write to the alert log from PL/SQL ==== |
| - | * [[https:// | + | * [[https:// |
| Writes to trace file | Writes to trace file | ||
| - | < | + | < |
| + | dbms_system.ksdwrt(1,' | ||
| + | </ | ||
| Writes to the alertlog | Writes to the alertlog | ||
| - | < | + | < |
| + | dbms_system.ksdwrt(2,' | ||
| + | </ | ||
| Writes to the alertlog and trace file | Writes to the alertlog and trace file | ||
| - | < | + | < |
| + | dbms_system.ksdwrt(3,' | ||
| + | </ | ||
| Also available, KSDIND, KSDDDT and KSDFLS | Also available, KSDIND, KSDDDT and KSDFLS | ||
| - | =====Do distributed SQL transactions exist? | + | ==== Setup optional parameters and default values in an SQL*Plus script ==== |
| - | < | + | |
| - | if yes, and to commit them... | + | |
| - | < | + | |
| - | =====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... | Some forgotten SQL*Plus tricks from the good old days and some new ones... | ||
| - | * [[http:// | + | * [[http:// |
| - | < | + | < |
| + | 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('& | ||
| + | set termout on | ||
| + | prompt 1="& | ||
| + | prompt 2="& | ||
| + | prompt 3="& | ||
| + | undef 1 | ||
| + | undef 2 | ||
| + | undef 3 | ||
| + | </ | ||
| - | =====Display / show users with sysdba & sysoper privileges===== | + | ==== Display / show users with sysdba & sysoper privileges ==== |
| - | < | + | < |
| + | select * from v$pwfile_users; | ||
| + | </ | ||
| - | =====Show path names of data files using instr===== | + | ==== Show path names of data files using instr ==== |
| Use to find the pathnames of all the filesystems/ | Use to find the pathnames of all the filesystems/ | ||
| - | < | + | < |
| + | select distinct substr(name, | ||
| + | union | ||
| + | select distinct substr(name, | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Does a dataguard standby exist for this database? ==== | ||
| + | < | ||
| + | SELECT SUBSTR(value, | ||
| + | FROM | ||
| + | WHERE name LIKE ' | ||
| + | < | ||
| + | SUBSTR(VALUE, | ||
| + | -------------------------------------------------------------------------------- | ||
| + | " | ||
| + | ax_failure=0 max_connections=1 reopen=300 db_unique_name=" | ||
| + | t_timeout=60, | ||
| + | </ | ||
| + | |||
| + | ==== Querying initialisation parameters ==== | ||
| + | The traditional way of looking at parameters in the spfile and comparing with the same values in the " | ||
| + | * Reference: [[https:// | ||
| + | This article describes the difference between v$parameter, | ||
| + | |||
| + | * 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, | ||
| + | |||
| + | * 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. | ||
| - | =====Does a dataguard standby exist for this database? | + | * 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. |
| - | < | + | |
| - | gives something like this if found... | + | |
| - | < | + | |
| - | =====List values for all init parameters in v$parameter (including default values for undocumented (hidden) parameters===== | + | ==== List values for all init parameters in v$parameter (including default values for undocumented (hidden) parameters) ==== |
| - | < | + | < |
| + | select i.ksppinm||';' | ||
| + | from | ||
| + | , x$ksppsv sv | ||
| + | where i.indx = sv.indx | ||
| + | order by i.ksppinm; | ||
| + | </ | ||
| - | =====Log file reports pipe errors===== | + | ==== Log file reports pipe errors ==== |
| Found these in the log file | Found these in the log file | ||
| - | < | + | < |
| - | This could be due to a timeout on the pipe.<br /> | + | solax005: |
| + | CIR1880E (024955) Open Probe pipe error 2. Pipe=/ | ||
| + | CIR1880E (030938) Open Probe pipe error 2. Pipe=/ | ||
| + | CIR1880E (031923) Open Probe pipe error 2. Pipe=/ | ||
| + | CIR1880E (033934) Open Probe pipe error 2. Pipe=/ | ||
| + | CIR1880E (034918) Open Probe pipe error 2. Pipe=/ | ||
| + | CIR1880E (041927) Open Probe pipe error 2. Pipe=/ | ||
| + | CIR1880E (042931) Open Probe pipe error 2. Pipe=/ | ||
| + | CIR1880E (045928) Open Probe pipe error 2. Pipe=/ | ||
| + | CIR1880E (052927) Open Probe pipe error 2. Pipe=/ | ||
| + | CIR1880E (090929) Open Probe pipe error 2. Pipe=/ | ||
| + | </ | ||
| + | This could be due to a timeout on the pipe.\\ | ||
| Increase parameters and restart agent. Add following lines to or.config: | Increase parameters and restart agent. Add following lines to or.config: | ||
| - | < | + | < |
| + | export COLL_WAIT_TIMEOUT=' | ||
| + | export WAIT_TIMEOUT=' | ||
| + | </ | ||
| - | =====Find the most recent archived SCN number===== | + | ==== Find the most recent archived SCN number ==== |
| - | < | + | < |
| + | col next_change# | ||
| + | set numwidth 15 | ||
| - | =====Monitoring says database inactive===== | + | select max(next_change# |
| - | * Check the candle user password has not expired in the database. | + | from |
| - | * There may be a clue in the log file. Check / | + | where (thread#, next_change# |
| - | | + | from |
| - | | + | where archived |
| - | < | + | and status |
| - | < | + | and resetlogs_id |
| - | Solution:< | + | from |
| - | * Change all occurrences of 11.2.0.1 to 11.2.0.3 | + | where status |
| - | * Restart agent | + | ) |
| + | group by thread# | ||
| + | ) | ||
| + | and status | ||
| + | and resetlogs_id | ||
| + | | ||
| + | | ||
| + | ) | ||
| + | order by next_change# | ||
| + | </ | ||
| - | =====Alerts due to password | + | ==== Monitoring says database inactive |
| - | < | + | * Check the candle user password |
| - | < | + | * There may be a clue in the log file. Check /opt/IBM/ITM/logs/`hostname`_or_${SID}_col.out |
| - | Solution:< | + | |
| - | * Run agent reconfigure to setup the new password for the candle user (tivoli) | + | * |
| - | < | + | < |
| - | * Restart agent | + | export ITM_HOME=/opt/IBM/ITM |
| - | < | + | cd $ITM_HOME/ |
| + | vi solax005_or_SRV2R.cfg | ||
| + | </code> | ||
| + | < | ||
| + | # IBM Tivoli Monitoring for Databases: Oracle Agent | ||
| + | # Configuration file: solax005_or_SRV2R.cfg | ||
| + | # Written by CandleDBconfig version: 1.4 on 31Jul13 13:39:41 | ||
| - | =====Many agents started with root user instead of itmora===== | + | # Note: this is a Korn-shell script that is " |
| + | # environment for a Monitoring Agent for Oracle. | ||
| + | # 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. | ||
| + | | ||
| + | | ||
| + | # IY92195 | ||
| + | | ||
| + | |||
| + | # Following variables are set for the setup scripts | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | |||
| + | # 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/ | ||
| + | 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 | ||
| + | | ||
| + | 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 | ||
| + | | ||
| + | CGN1521E (161714) Interval collection failed for cursor KORHART2 | ||
| + | RCD0110S (161714) Invalid Oracle logon id (tivoli) or password given | ||
| + | | ||
| + | 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=/ | ||
| + | $ITM_HOME/ | ||
| + | </ | ||
| + | * Restart agent | ||
| + | < | ||
| + | export ITM_HOME=/ | ||
| + | $ITM_HOME/ | ||
| + | </ | ||
| + | |||
| + | ==== 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 | This can happen if the server is rebooted before the agents have been added to the itmora list using kdyedit / kciedit | ||
| - | < | + | < |
| - | < | + | export CANDLEHOME=/ |
| + | export ITM_HOME=/ | ||
| + | $ITM_HOME/ | ||
| + | Type Runas Inst | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | </ | ||
| + | < | ||
| + | $ITM_HOME/ | ||
| + | Type Runas Inst | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | </ | ||
| Solution: Add these instances to the itmora list | 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/ | ||
| + | Type Runas Inst | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | or | ||
| + | </ | ||
| Kill any agents still running as root | Kill any agents still running as root | ||
| - | < | + | < |
| - | 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.<br /> | + | ps -ef |grep kor | grep root |
| + | kill -9 <process id> | ||
| + | </ | ||
| + | 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. | The simplest way of doing this is to run lockdown. | ||
| - | < | + | < |
| + | export CANDLEHOME=/ | ||
| + | $CANDLEHOME/ | ||
| + | </ | ||
| - | =====See which processes are causing paging===== | + | ==== See which processes are causing paging ==== |
| - | < | + | < |
| - | =====How to return several variables from SQL*Plus back to Korn shell script===== | + | svmon -Pt20 | perl -e ' |
| + | </ | ||
| + | ==== How to return several variables from SQL*Plus back to Korn shell script ==== | ||
| Return multiple columns/ | Return multiple columns/ | ||
| - | < | + | < |
| - | =====How to get a shell environment variable from the operating system into the SQL or PL/SQL environment===== | + | sqlplus -s sys/ |
| + | set numwid 15 headi off newpa none feedb off | ||
| + | select sign(${SPACE_USED} + ${SPACE_AVAILABLE} - ${DATABASE_SIZE}), | ||
| + | from dual | ||
| + | / | ||
| + | EOSQL | ||
| + | </ | ||
| + | ==== 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? | This has been bugging me for years. Something you would think simple... what is ORACLE_HOME? | ||
| - | < | + | < |
| + | declare | ||
| + | var1 varchar2(200); | ||
| + | begin | ||
| + | dbms_system.get_env (' | ||
| + | dbms_output.put_line (' | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| or in SQL*Plus | or in SQL*Plus | ||
| - | < | + | < |
| - | That got me thinking... if this just gets stuff from the environment, | + | variable var1 varchar2(200); |
| + | exec dbms_system.get_env (' | ||
| + | select :var1 from dual; | ||
| + | </ | ||
| + | That got me thinking... if this just gets stuff from the environment, | ||
| From the shell: | From the shell: | ||
| - | < | + | < |
| + | export running_sids=`ps -ef | grep [p]mon | awk -F_ ' | ||
| + | </ | ||
| and now in SQL*Plus: | and now in SQL*Plus: | ||
| - | < | + | < |
| - | Wow! | + | variable sids varchar2(240); |
| + | exec dbms_system.get_env (' | ||
| + | select :sids from dual; | ||
| + | </ | ||
| + | Wow!\\ | ||
| + | \\ | ||
| + | and this is an example taken from postupgrade_fixups.sql (upgrade to 18c) | ||
| + | < | ||
| + | ALTER SESSION SET " | ||
| + | VARIABLE admin_preupgrade_dir VARCHAR2(512); | ||
| + | |||
| + | REM | ||
| + | REM point PREUPGRADE_DIR to OH/ | ||
| + | REM | ||
| + | DECLARE | ||
| + | oh VARCHAR2(4000); | ||
| + | BEGIN | ||
| + | dbms_system.get_env(' | ||
| + | : | ||
| + | END; | ||
| + | / | ||
| + | |||
| + | DECLARE | ||
| + | command varchar2(4000); | ||
| + | BEGIN | ||
| + | command := ' | ||
| + | EXECUTE IMMEDIATE command; | ||
| + | END; | ||
| + | / | ||
| + | </ | ||
| - | =====How long/wide can a database name/sid be?===== | + | ==== How long/wide can a database name/sid be? ==== |
| Depends on where you look but taking the minimum here as " | Depends on where you look but taking the minimum here as " | ||
| - | < | + | < |
| + | col table_name for a30 | ||
| + | col data_type | ||
| + | select table_name, data_type, data_length from dba_tab_columns where column_name = ' | ||
| + | </ | ||
| This is 11gR2 | This is 11gR2 | ||
| - | < | + | < |
| + | TABLE_NAME | ||
| + | ------------------------------ ------------ ----------- | ||
| + | LOGMNRG_DICTIONARY$ | ||
| + | SYS_FBA_CONTEXT_AUD | ||
| + | V_$LOGMNR_DICTIONARY | ||
| + | V_$LOGMNR_LOGS | ||
| + | GV_$LOGMNR_DICTIONARY | ||
| + | GV_$LOGMNR_LOGS | ||
| + | V_$LOGMNR_LOGFILE | ||
| + | V_$LOGMNR_SESSION | ||
| + | GV_$LOGMNR_LOGFILE | ||
| + | GV_$LOGMNR_SESSION | ||
| + | GV_$ASM_CLIENT | ||
| + | V_$ASM_CLIENT | ||
| + | GV_$IOS_CLIENT | ||
| + | V_$IOS_CLIENT | ||
| + | DBA_PDB_HISTORY | ||
| + | CDB_PDB_HISTORY | ||
| + | SSCR_CAP$ | ||
| + | SSCR_RES$ | ||
| + | DBA_SSCR_CAPTURE | ||
| + | CDB_SSCR_CAPTURE | ||
| + | DBA_SSCR_RESTORE | ||
| + | CDB_SSCR_RESTORE | ||
| + | WRM$_DATABASE_INSTANCE | ||
| + | INT$DBA_HIST_DATABASE_INSTANCE VARCHAR2 | ||
| + | DBA_HIST_DATABASE_INSTANCE | ||
| + | CDB_HIST_DATABASE_INSTANCE | ||
| + | LOGMNR_DICTIONARY$ | ||
| - | =====What character set (characterset) was the database built with?===== | + | 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 | or | ||
| - | < | + | < |
| + | col name for a40 | ||
| + | col value$ for a60 | ||
| + | select name,value$ from props$ where name = ' | ||
| + | </ | ||
| - | =====How to get value of ORACLE_HOME from shell environment into SQL*Plus or PL/SQL===== | + | ==== 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:// | ||
| - | =====How to find ORACLE_HOME from pmon process for a SID if there is no entry in oratab===== | + | * Confirm XML DB is not already installed. |
| - | If database is created without dbca (datafile and controlfile copy), no entry will be automatically made in / | + | < |
| + | select username, | ||
| + | </ | ||
| + | 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: | ||
| + | < | ||
| + | @?/ | ||
| + | </ | ||
| + | For example | ||
| + | < | ||
| + | @?/ | ||
| + | </ | ||
| + | * 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: | ||
| + | < | ||
| + | @?/ | ||
| + | </ | ||
| + | * Assuming an spfile is in use, as sys configure the xdb dispatcher: | ||
| + | < | ||
| + | alter system set dispatchers=" | ||
| + | </ | ||
| + | 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 | ||
| + | @?/ | ||
| + | shutdown immediate; | ||
| + | startup | ||
| + | </ | ||
| + | * As sys, ensure their are no invalid objects: | ||
| + | < | ||
| + | @?/ | ||
| + | </ | ||
| + | * As sys, confirm the XML DB is now fully installed by running the following query and returning the following output: | ||
| + | < | ||
| + | SELECT object_value | ||
| + | FROM | ||
| + | WHERE 1=1 | ||
| + | and EXTRACTVALUE(object_value, | ||
| + | AND EXTRACTVALUE(object_value, | ||
| + | / | ||
| + | |||
| + | OBJECT_VALUE | ||
| + | -------------------------------------------------------------------------------- | ||
| + | < | ||
| + | |||
| + | </ | ||
| + | |||
| + | |||
| + | ==== How to find ORACLE_HOME from pmon process for a SID if there is no entry in oratab ==== | ||
| + | If database is created without dbca (datafile and controlfile copy), no entry will be automatically made in / | ||
| So the problem is that you need to find out which of the ORACLE_HOMEs is being used by the running instances. | So the problem is that you need to find out which of the ORACLE_HOMEs is being used by the running instances. | ||
| - | * Solaris, Linux | + | * Solaris, Linux |
| - | < | + | < |
| - | * AIX | + | $ ps -ef | grep [p]mon |
| - | < | + | oracle 10848 |
| - | or<br /> | + | $ pwdx 10848 |
| + | 10848: | ||
| + | </ | ||
| + | * AIX | ||
| + | < | ||
| + | $ ps -ef | grep [p]mon | ||
| + | oracle 13893878 | ||
| + | $ ls -al /proc/ | ||
| + | lr-x------ | ||
| + | </code> | ||
| + | or\\ | ||
| This command (ps eww) gives lots of information about the given process (pmon in this case). Tells you where the instance was started, whether it was started from SQL*Plus or RMAN, the http_proxy and loads more useful bits! | This command (ps eww) gives lots of information about the given process (pmon in this case). Tells you where the instance was started, whether it was started from SQL*Plus or RMAN, the http_proxy and loads more useful bits! | ||
| - | < | + | < |
| + | ps -ef | grep [p]mon | awk ' | ||
| + | </ | ||
| - | =====Escape the underscore(_) or percent(%) character in an SQL statement===== | + | ==== 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.<br /> | + | 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. | + | Here I use the backslash(\\) so it is easy for Unix people to understand the mechanism. |
| - | < | + | < |
| + | select username from dba_users where username like ' | ||
| + | </ | ||
| - | =====Validate, | + | ==== Validate, analyse and rebuild indexes ==== |
| - | <code> | + | < |
| - | =====Rebuild unusable indexes | + | set feedback off |
| - | < | + | set linesize 132 |
| + | set serveroutput | ||
| + | set trimspool on | ||
| - | =====Pull the latest cfengine changes now instead of waiting for scheduled time===== | + | declare |
| + | LOCKED exception; | ||
| + | pragma exception_init (LOCKED, -54); | ||
| + | |||
| + | dbase | ||
| + | dbuser | ||
| + | index_name | ||
| + | free_text | ||
| + | pctused | ||
| + | height | ||
| + | index_mb | ||
| + | analyze_sql | ||
| + | pct_used_sql | ||
| + | rebuild_sql | ||
| + | |||
| + | cursor c1 is | ||
| + | select segment_name, | ||
| + | from | ||
| + | where segment_type | ||
| + | and segment_name like ' | ||
| + | and segment_name not like ' | ||
| + | and bytes/ | ||
| + | order by 2; | ||
| + | |||
| + | -- validate the index | ||
| + | procedure validate_index (v_ind in varchar2) is | ||
| + | begin | ||
| + | analyze_sql := ' | ||
| + | begin | ||
| + | execute immediate analyze_sql; | ||
| + | exception | ||
| + | when LOCKED then | ||
| + | dbms_output.put_line (v_ind||' | ||
| + | pctused := 100; | ||
| + | return; | ||
| + | when others then | ||
| + | dbms_output.put_line (analyze_sql); | ||
| + | raise; | ||
| + | end; | ||
| + | |||
| + | pct_used_sql := ' | ||
| + | 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, | ||
| + | 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(' | ||
| + | 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/ | ||
| + | rebuild_sql := 'alter index ' | ||
| + | else | ||
| + | rebuild_sql := 'alter index ' | ||
| + | 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 ' | ||
| + | from | ||
| + | where 1=1 | ||
| + | and status | ||
| + | and owner not in (' | ||
| + | --and owner = ' | ||
| + | --and table_name = ' | ||
| + | </ | ||
| + | |||
| + | ==== Pull the latest cfengine changes now instead of waiting for scheduled time ==== | ||
| For one host | For one host | ||
| - | < | + | < |
| + | / | ||
| + | </ | ||
| On all hosts at the same time | On all hosts at the same time | ||
| - | < | + | < |
| - | =====Check status of datafiles without dba_data_files or v$datafile===== | + | / |
| + | </ | ||
| + | ==== 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 | 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 ' | ||
| + | col file_nr for 9999999 heading 'File #' | ||
| + | col file_name for A50 heading 'File name' | ||
| + | col checkpoint_change_nr for 99999999999999 heading ' | ||
| + | col checkpoint_change_time for A20 heading ' | ||
| + | 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, ' | ||
| + | DECODE (BITAND (fe.festa, 18), | ||
| + | 0, ' | ||
| + | 2, ' | ||
| + | ' | ||
| + | ) status | ||
| + | FROM x$kccfe fe, | ||
| + | | ||
| + | WHERE ( | ||
| + | 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)===== | + | ==== Check highest allocated extent in datafile (likely slow when having many extents) ==== |
| - | * Script to Detect Tablespace Fragmentation ( Doc ID 1020182.6 ) | + | * 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 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 ) | + | * 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 | ||
| + | , | ||
| + | , | ||
| + | from dba_data_files a | ||
| + | ,(select file_id, | ||
| + | from dba_extents | ||
| + | group by file_id) b | ||
| + | , | ||
| + | ,(select value db_block_size | ||
| + | from v$parameter | ||
| + | where name=' | ||
| + | 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, | ||
| + | / | ||
| + | </ | ||
| Runup to this was... | Runup to this was... | ||
| - | < | + | < |
| - | < | + | set markup html on spool on |
| - | < | + | spool db_info.html |
| + | set echo on | ||
| + | select * from dba_tablespaces where tablespace_name = '&& | ||
| + | select * from dba_data_files where tablespace_name = '& | ||
| + | select * from (select * from dba_extents where tablespace_name = '& | ||
| + | spool off | ||
| + | set markup html off | ||
| + | </ | ||
| + | < | ||
| + | select owner, segment_type, | ||
| + | </ | ||
| + | < | ||
| + | begin | ||
| + | for ii in (select owner, segment_name, | ||
| + | loop | ||
| + | if ii.segment_type=' | ||
| + | begin | ||
| + | execute immediate(' | ||
| + | execute immediate(' | ||
| + | exception when others then | ||
| + | dbms_output.put_line(' | ||
| + | end; | ||
| + | end if; | ||
| + | end loop; | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| - | =====Procedure to shrink space in tables and indexes===== | + | ==== Shrink datafiles to high water mark (HWM) ==== |
| - | * [[http:// | + | 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.\\ |
| - | Written by [[https:// | + | Reference: [[https:// |
| - | 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).<br /> | + | < |
| + | 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#, | ||
| + | from sys.x$ktfbue group by ktfbuefno, | ||
| + | ), | ||
| + | hwmts as ( | ||
| + | -- join ts# with tablespace_name | ||
| + | select name tablespace_name, | ||
| + | from hwm join v$tablespace using(ts# | ||
| + | ), | ||
| + | hwmdf as ( | ||
| + | -- join with datafiles, put 5M minimum for datafiles with no extents | ||
| + | select file_name, | ||
| + | from hwmts right join dba_data_files using(tablespace_name, | ||
| + | ) | ||
| + | select | ||
| + | case when autoextensible=' | ||
| + | then -- we generate resize statements only if autoextensible can grow back to current size | ||
| + | '/* reclaim ' | ||
| + | ||'M from ' | ||
| + | ||' | ||
| + | else -- generate only a comment when autoextensible is off | ||
| + | '/* reclaim ' | ||
| + | ||'M from ' | ||
| + | ||'M after setting autoextensible maxsize higher than current size for file ' | ||
| + | || file_name||' | ||
| + | end SQL | ||
| + | from hwmdf | ||
| + | where | ||
| + | bytes-hwm_bytes> | ||
| + | order by bytes-hwm_bytes desc | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Procedure to shrink space in tables and indexes ==== | ||
| + | * [[http:// | ||
| + | Written by [[https:// | ||
| + | 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:// | [[http:// | ||
| - | < | + | < |
| + | create or replace procedure shrink_all | ||
| + | as | ||
| + | l_sql varchar2(4000); | ||
| + | l_sql2 | ||
| + | row_movement exception; | ||
| + | pragma | ||
| + | begin | ||
| + | for x in (select table_name | ||
| + | , owner | ||
| + | from t | ||
| + | where sgm_space_management = ' | ||
| + | loop | ||
| + | l_sql := 'alter table "' | ||
| + | dbms_output.put_line( l_sql ); | ||
| + | |||
| + | begin | ||
| + | execute immediate l_sql; | ||
| + | exception | ||
| + | when row_movement | ||
| + | then | ||
| + | dbms_output.put_line( ' | ||
| + | l_sql2 := 'alter table "' | ||
| + | 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 | ||
| + | where table_owner = x.owner | ||
| + | and table_name | ||
| + | loop | ||
| + | l_sql := 'alter index "' | ||
| + | dbms_output.put_line( l_sql ); | ||
| + | execute immediate l_sql; | ||
| + | end loop; | ||
| + | |||
| + | end loop; | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== How to delete/kill a distributed transaction ==== | ||
| + | Reference: [[http:// | ||
| + | < | ||
| + | col global_tran_id for a40 | ||
| + | col fail_time | ||
| + | col host for a10 | ||
| + | select local_tran_id | ||
| + | , global_tran_id | ||
| + | , to_char(fail_time,' | ||
| + | , state | ||
| + | , host | ||
| + | , mixed | ||
| + | from | ||
| + | / | ||
| + | |||
| + | LOCAL_TRAN_ID | ||
| + | ---------------------- ---------------------------------------- --------------------- ---------------- ---------- ------------ | ||
| + | 149.3.2239899 | ||
| + | 188.17.2309561 | ||
| + | |||
| + | </ | ||
| + | < | ||
| + | col database for a30 | ||
| + | select local_tran_id | ||
| + | , in_out | ||
| + | , database | ||
| + | , interface | ||
| + | from | ||
| + | / | ||
| + | |||
| + | LOCAL_TRAN_ID | ||
| + | ---------------------- ------------ ------------------------------ - | ||
| + | 149.3.2239899 | ||
| + | 188.17.2309561 | ||
| + | 149.3.2239899 | ||
| + | 188.17.2309561 | ||
| + | 188.17.2309561 | ||
| + | 149.3.2239899 | ||
| + | |||
| + | 6 rows selected. | ||
| + | |||
| + | </ | ||
| + | < | ||
| + | |||
| + | SQL> rollback force ' | ||
| + | |||
| + | Rollback complete. | ||
| + | |||
| + | </ | ||
| + | < | ||
| + | SQL> execute dbms_transaction.purge_lost_db_entry(' | ||
| + | |||
| + | PL/SQL procedure successfully completed. | ||
| + | |||
| + | </ | ||
| + | < | ||
| + | SQL> commit; | ||
| + | |||
| + | Commit complete. | ||
| - | =====How to delete/kill a distributed transaction===== | + | </ |
| - | From [[http:// | + | |
| - | < | + | |
| 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. | 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. | ||
| - | <br /> | ||
| - | =====ORACLE_BASE is getting set to the same value as ORACLE_HOME when using . oraenv===== | + | |
| + | ==== 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! | Permissions problem. Make sure the user has **write** access to oraclehomeproperties.xml file! | ||
| - | < | + | < |
| + | ls -al $ORACLE_HOME/ | ||
| + | </ | ||
| Change permissions as appropriate. | Change permissions as appropriate. | ||
| A different approach which should give the same end result would be to grant the orabase executable the setuid bit. | 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/ | ||
| + | </ | ||
| This allows any user to set the environment as the orabase executable will be run with the permissions of its owner. | 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===== | + | ==== 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?<br /> | + | 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<br /> | + | ORA-14404: partitioned table contains partitions in a different tablespace\\ |
| - | ====Find tables with partitions in more than one 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 | ||
| + | , | ||
| + | , count(*) | ||
| + | from | ||
| + | where table_owner != ' | ||
| + | group by table_owner | ||
| + | , table_name | ||
| + | having count(*) > 1 | ||
| + | / | ||
| + | </ | ||
| See which tables have partitions across multiple tablespaces... | See which tables have partitions across multiple tablespaces... | ||
| - | < | + | < |
| + | set lines 1000 pages 100 | ||
| + | col table_name for a50 | ||
| + | select distinct b.table_owner||' | ||
| + | , a.tablespace_name ts1 | ||
| + | , b.tablespace_name ts2 | ||
| + | from ( | ||
| + | | ||
| + | , | ||
| + | , | ||
| + | | ||
| + | | ||
| + | , dba_tab_partitions b | ||
| + | where a.table_name | ||
| + | and a.tablespace_name != b.tablespace_name | ||
| + | / | ||
| + | </ | ||
| Generate statements to move the partitions from one tablespace to another... | Generate statements to move the partitions from one tablespace to another... | ||
| - | < | + | < |
| - | ====What' | + | select 'alter table ' |
| - | < | + | from ( |
| + | | ||
| + | , | ||
| + | | ||
| + | | ||
| + | | ||
| + | , dba_tab_partitions b | ||
| + | where a.table_name | ||
| + | and b.tablespace_name != '& | ||
| + | / | ||
| + | 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 | ||
| + | where tablespace_name in (' | ||
| + | / | ||
| - | =====What edition is my database (Standard, Enterprise, XE...)?===== | + | select table_owner |
| - | Only works from 12c :-( | + | , table_name |
| - | < | + | from |
| - | =====Is database a CDB with PDBs?===== | + | where index_name |
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== What edition is my database (Standard, Enterprise, XE...)? ==== | ||
| + | Only works ob 12c and above | ||
| + | < | ||
| + | select edition from sys.registry$ where cid=' | ||
| + | </ | ||
| + | ==== 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 | 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_ ' | ||
| + | do | ||
| + | export ORACLE_SID=${db} | ||
| + | ORAENV_ASK=NO | ||
| + | . oraenv >/ | ||
| + | sqlplus -s / as sysdba<<' | ||
| + | set head off newpa none | ||
| + | select name | ||
| + | , (select count(*) from v$pdbs) npdbs | ||
| + | from | ||
| + | / | ||
| + | EOSQL | ||
| + | done | ||
| + | </ | ||
| version 2 | version 2 | ||
| - | < | + | < |
| - | =====List triggers in the database===== | + | function is_db_cdb return boolean |
| - | This query specifically lists after login triggers | + | is |
| - | < | + | b_is_cdb |
| - | =====List the pluggable databases (PDB) in a container database (CDB)===== | + | l_is_cdb |
| + | e_col_not_found exception; | ||
| + | pragma | ||
| + | begin | ||
| + | begin | ||
| + | execute immediate ' | ||
| + | into l_is_cdb; | ||
| + | exception | ||
| + | when e_col_not_found then l_is_cdb := ' | ||
| + | end; | ||
| + | |||
| + | if (l_is_cdb = ' | ||
| + | return TRUE; | ||
| + | else | ||
| + | return FALSE; | ||
| + | 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, | ||
| + | , to_char(obj.last_ddl_time, | ||
| + | from | ||
| + | join | ||
| + | on ( | ||
| + | and obj.owner | ||
| + | and obj.object_name = trgs.trigger_name | ||
| + | ) | ||
| + | join | ||
| + | on ( obj.con_id | ||
| + | where trgs.trigger_type | ||
| + | and trgs.triggering_event like ' | ||
| + | and trgs.status | ||
| + | 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 | The network_name column shows what you should find in tnsnames.ora for this db connection | ||
| - | < | + | < |
| + | select name, | ||
| + | </ | ||
| or | or | ||
| - | < | + | < |
| + | select * from v$pdbs; | ||
| + | </ | ||
| - | =====Start and stop a pluggable database===== | + | ==== Start and stop a pluggable database ==== |
| - | < | + | < |
| - | < | + | alter pluggable database pdb1 open; |
| - | =====Swith to a pluggable database===== | + | </ |
| - | < | + | < |
| - | =====Reopen pluggable databases at container startup===== | + | 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. | 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 = ' | ||
| + | </ | ||
| To modify this, open it and save its state | 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 | To see a log of issues with pluggable databases | ||
| - | < | + | < |
| + | select * from pdb_plug_in_violations where type = ' | ||
| + | </ | ||
| - | =====What is the current container id in a cdb?===== | + | ==== What is the current container id in a cdb? ==== |
| CDB$ROOT is container id 1. User containers start from 2. | CDB$ROOT is container id 1. User containers start from 2. | ||
| - | < | + | < |
| - | =====What is the current container name in a cdb?===== | + | select sys_context(' |
| - | < | + | </ |
| + | ==== What is the current container name in a cdb? ==== | ||
| + | < | ||
| + | select sys_context(' | ||
| + | </ | ||
| or just | or just | ||
| - | < | + | < |
| - | =====Switching Between Containers===== | + | show con_name |
| - | < | + | </ |
| - | =====Direct connection to pluggable database===== | + | ==== Switching Between Containers ==== |
| + | < | ||
| + | SQL> show con_name | ||
| + | |||
| + | CON_NAME | ||
| + | ------------------------------ | ||
| + | CDB$ROOT | ||
| + | |||
| + | SQL> alter session set container=tstklok; | ||
| + | |||
| + | Session altered. | ||
| + | |||
| + | SQL> | ||
| + | |||
| + | CON_NAME | ||
| + | ------------------------------ | ||
| + | TSTKLOK | ||
| + | </ | ||
| + | ==== Direct connection to pluggable database ==== | ||
| + | * Check what is possible with EZCONNECT Plus [[https:// | ||
| These must be made using a service (defined in tnsnames.ora). Each pluggable database automatically registers a service with the listener (v$services). | 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/ | ||
| + | </ | ||
| or using ezconnect | or using ezconnect | ||
| - | < | + | < |
| + | SQL> conn appuser/ | ||
| + | </ | ||
| - | =====What platform is the database running on?===== | + | ==== What platform is the database running on? ==== |
| Could be useful for handling line endings... | Could be useful for handling line endings... | ||
| - | < | + | < |
| - | < | + | select platform_name from v$database |
| + | </ | ||
| + | < | ||
| + | if instr(db_platform, | ||
| + | crlf := CHR(13) || CHR(10); | ||
| + | else | ||
| + | crlf := CHR (10); -- Just \n for the rest of the world | ||
| + | end if; | ||
| + | </ | ||
| - | =====Is the database 32 or 64 bit?===== | + | ==== Is the database 32 or 64 bit? ==== |
| If the answer is 1, | If the answer is 1, | ||
| - | < | + | < |
| + | select platform_id from v$database | ||
| + | </ | ||
| - | =====What features is my database using?===== | + | ==== What features is my database using? ==== |
| Before converting to Standard Edition, check the features here - some may be Enterprise specific. | 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 | ||
| + | where 1=1 | ||
| + | and currently_used = ' | ||
| + | / | ||
| + | </ | ||
| - | =====How many redo log switches per hour?===== | + | ==== How many redo log switches per hour? ==== |
| - | < | + | < |
| + | set lines 100 | ||
| + | col " | ||
| + | select to_char(first_time,' | ||
| + | , count(1) | ||
| + | from | ||
| + | group by to_char(first_time,' | ||
| + | order by 1 | ||
| + | </ | ||
| or a bit fancier version... | or a bit fancier version... | ||
| - | < | + | < |
| + | set lines 200 | ||
| + | col day for a9 hea " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | alter session set nls_date_format=' | ||
| + | / | ||
| + | select trunc(first_time) day, | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | from | ||
| + | where first_time > (sysdate-& | ||
| + | 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: | ||
| + | rem | ||
| + | rem From ' | ||
| + | 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 ********************************************************* | ||
| - | =====Show redo log groups/ | ||
| - | < | ||
| - | =====Korn shell timestamp function===== | + | 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 | ||
| + | | ||
| + | | ||
| + | last_first_time, | ||
| + | | ||
| + | - LAG(first_time) OVER (ORDER BY thread#, sequence# | ||
| + | * 24* 60 | ||
| + | | ||
| + | | ||
| + | FROM v$log_history) | ||
| + | SELECT ROUND(MIN(last_log_time_minutes), | ||
| + | | ||
| + | | ||
| + | FROM log_history | ||
| + | WHERE | ||
| + | AND last_thread# | ||
| + | AND first_time > SYSDATE - 1; | ||
| + | </ | ||
| + | |||
| + | ==== Show redo log groups/ | ||
| + | < | ||
| + | set lines 1000 | ||
| + | col instance | ||
| + | col thread | ||
| + | col groupno | ||
| + | col member | ||
| + | col redo_file_type for a15 | ||
| + | col log_status | ||
| + | col logsize_m | ||
| + | col archived | ||
| + | |||
| + | SELECT i.instance_name | ||
| + | , i.thread# | ||
| + | , f.group# | ||
| + | , f.member | ||
| + | , f.type | ||
| + | , l.status | ||
| + | , l.bytes/ | ||
| + | , l.archived | ||
| + | FROM | ||
| + | , gv$log | ||
| + | , gv$instance i | ||
| + | WHERE 1=1 | ||
| + | and f.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" | Example usage: echo "`ts`: Checking backup status" | ||
| - | < | + | < |
| - | =====Drop a database===== | + | function ts { |
| + | date +' | ||
| + | } | ||
| + | </ | ||
| + | ==== Drop a database ==== | ||
| Dropping a database including backups should be considered criminal in a production environment! | 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 | 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===== | + | ==== Use sub-select or in-line views to update base tables ==== |
| Inline views can be used to update base table data | Inline views can be used to update base table data | ||
| - | < | + | < |
| - | =====Use sub-select or in-line views to delete base table data===== | + | update |
| + | ( | ||
| + | select p.list_price | ||
| + | from | ||
| + | , product_categories pc | ||
| + | where 1=1 | ||
| + | and p.category_id | ||
| + | and pc.category_name = ' | ||
| + | ) | ||
| + | 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 | A different way to delete rows in base tables, using Inline views or subselect | ||
| - | < | + | < |
| - | =====Mass update of files using perl inline script===== | + | delete |
| - | < | + | ( |
| + | select p.list_price | ||
| + | from | ||
| + | , product_categories pc | ||
| + | where 1=1 | ||
| + | and p.category_id | ||
| + | and pc.category_name = ' | ||
| + | ) | ||
| + | where list_price < 1000; | ||
| + | </ | ||
| + | ==== Mass update of files using perl inline script ==== | ||
| + | < | ||
| + | for server in `cat / | ||
| + | ssh $server “perl -p -i -e ' | ||
| + | done | ||
| + | </ | ||
| - | =====How to use vi-style editing in SQL*Plus===== | + | ==== How to use vi-style editing in SQL*Plus ==== |
| These instructions are for Redhat but other versions will be very similar | These instructions are for Redhat but other versions will be very similar | ||
| - | * Download rlwrap from [[http:// | + | * Download rlwrap from [[http:// |
| - | * sudo yum install rlwrap | + | * sudo yum install rlwrap |
| - | * build a dictionary file for autocomplete on pressing Tab | + | * build a dictionary file for autocomplete on pressing Tab |
| - | < | + | < |
| - | < | + | vi $HOME/ |
| + | select | ||
| + | from | ||
| + | dba_users | ||
| + | dba_data_files | ||
| + | dba_tablespaces | ||
| + | </ | ||
| + | < | ||
| + | alias sysdba=' | ||
| + | </ | ||
| or for general command use... | or for general command use... | ||
| - | < | + | < |
| + | alias sqlplus=' | ||
| + | alias dgmgrl=' | ||
| + | alias rman=' | ||
| + | alias asmcmd=' | ||
| + | alias lsnrctl=' | ||
| + | alias adrci=' | ||
| + | alias impdp=' | ||
| + | alias expdp=' | ||
| + | </ | ||
| - | =====Execute an SQL statement on all databases on all servers for a customer===== | + | ==== Execute an SQL statement on all databases on all servers for a customer ==== |
| Run from customer' | Run from customer' | ||
| - | < | + | < |
| + | ./dosh -c "su - oracle -c '/ | ||
| + | </ | ||
| or | or | ||
| - | < | + | < |
| + | SCRIPTS_DIR=/ | ||
| + | $SCRIPTS_DIR/ | ||
| + | $SCRIPTS_DIR/ | ||
| + | $SCRIPTS_DIR/ | ||
| + | </ | ||
| - | =====Where is the alert log/ | + | ==== Where is the alert log/ |
| Older systems | Older systems | ||
| - | < | + | < |
| + | select value from v$parameter where name=' | ||
| + | </ | ||
| Newer systems | Newer systems | ||
| - | < | + | < |
| - | =====Grant normal users access to the alertlog table (sys.x$dbgalertext)===== | + | select value from v$diag_info where name=' |
| - | < | + | </ |
| + | ==== 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 && | ||
| + | create or replace synonym & | ||
| + | undef grantee | ||
| + | </ | ||
| - | =====Search alert log for ORA-00600 and ORA-07445 errors===== | + | ==== 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. | Scan the database table version of the alertlog for errors without reporting what was already found. | ||
| - | < | + | < |
| - | =====Simulating errors in alert log===== | + | select indx, message_text from x$dbgalertext where message_text like ' |
| + | </ | ||
| + | ==== 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. | 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, | ||
| + | </ | ||
| This additionally creates a trace file. | This additionally creates a trace file. | ||
| - | < | + | < |
| + | exec sys.dbms_system.ksdwrt(3, | ||
| + | </ | ||
| - | =====tail the database alert log===== | + | ==== tail the database alert log ==== |
| If an ADR error is displayed, then ORACLE_SID is probably not set | If an ADR error is displayed, then ORACLE_SID is probably not set | ||
| - | < | + | < |
| + | alias tailalertlog=' | ||
| + | </ | ||
| - | =====What is my WAN ip address?===== | + | ==== What is my WAN ip address? ==== |
| - | < | + | < |
| + | curl -A " | ||
| + | </ | ||
| or | or | ||
| - | < | + | < |
| + | curl -A " | ||
| + | </ | ||
| - | =====Wait for child pid to finish in background and report its status===== | + | ==== 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 | or | ||
| - | < | + | < |
| + | # | ||
| + | # submit a few processes to the background | ||
| + | # wait for them all to finish | ||
| + | # concat their logfiles | ||
| + | |||
| + | (./ | ||
| + | PIDLIST=" | ||
| + | echo " | ||
| + | sleep 5 | ||
| + | |||
| + | (./ | ||
| + | PIDLIST=" | ||
| + | echo " | ||
| + | sleep 5 | ||
| + | |||
| + | (./ | ||
| + | PIDLIST=" | ||
| + | echo " | ||
| + | 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 | Real world example | ||
| - | < | + | < |
| + | # | ||
| + | # ============================================================================== | ||
| + | # Name : export_JDBEOP1.ksh | ||
| + | # Description | ||
| + | # | ||
| + | # Parameters | ||
| + | # | ||
| + | # Notes : Also decide whether to exclude PDARC schema or not | ||
| + | # depending on what day it is | ||
| + | # | ||
| + | # | ||
| + | # Modification History | ||
| + | # ==================== | ||
| + | # When Who | ||
| + | # ========= ================= ================================================== | ||
| + | # 15-OCT-13 Stuart Barkley | ||
| + | # ============================================================================== | ||
| + | |||
| + | STARTTIME=`date ' | ||
| + | DAYNO=`date +%u` | ||
| + | PROGNAME=`basename $0` | ||
| + | SCRIPTS_DIR=/ | ||
| + | DATA_DIR=/ | ||
| + | EXPORT_DIR=/ | ||
| + | |||
| + | # marker so we can find our files later | ||
| + | MARKERFILE=/ | ||
| + | touch $MARKERFILE | ||
| + | |||
| + | $SCRIPTS_DIR/ | ||
| + | PIDLIST=" | ||
| + | echo " | ||
| + | |||
| + | $SCRIPTS_DIR/ | ||
| + | PIDLIST=" | ||
| + | echo " | ||
| + | |||
| + | $SCRIPTS_DIR/ | ||
| + | PIDLIST=" | ||
| + | echo " | ||
| + | |||
| + | if [[ $DAYNO -eq 1 ]]; then | ||
| + | $SCRIPTS_DIR/ | ||
| + | PIDLIST=" | ||
| + | echo " | ||
| + | fi | ||
| + | |||
| + | # now sit and wait for the exports to finish | ||
| + | for PID in $PIDLIST; do | ||
| + | echo " | ||
| + | wait $PID | ||
| + | echo "$PID exited with status $?" | ||
| + | done | ||
| + | |||
| + | # collect all log files up into one | ||
| + | cd $EXPORT_DIR | ||
| + | for i in `find . -name " | ||
| + | cat $i > expdp_JDBEOP1_D_FULL_${STARTTIME}.log | ||
| + | done | ||
| + | |||
| + | rm $MARKLERFILE | ||
| + | </ | ||
| - | =====Create private DB link for a user without knowing his password===== | + | ==== Create private DB link for a user without knowing his password ==== |
| - | * Inspired by [[http:// | + | * Inspired by [[http:// |
| - | It uses a clever package, dbms_sys_sql.<br /> | + | It uses a clever package, dbms_sys_sql.\\ |
| - | Also useful for executing anything on behalf of another user.<br /> | + | Also useful for executing anything on behalf of another user.\\ |
| Here is an example on how to create private database link for a 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) := ' | ||
| + | myint integer; | ||
| + | begin | ||
| + | if upper(p_owner) = ' | ||
| + | sqltext := replace(sqltext, | ||
| + | else | ||
| + | sqltext := replace(sqltext, | ||
| + | end if; | ||
| + | sqltext := replace(sqltext, | ||
| + | sqltext := replace(sqltext, | ||
| + | sqltext := replace(sqltext, | ||
| + | sqltext := replace(sqltext, | ||
| + | |||
| + | select user_id | ||
| + | into uid | ||
| + | from | ||
| + | where username = decode(upper(p_owner), | ||
| + | ; | ||
| + | myint: | ||
| + | sys.dbms_sys_sql.parse_as_user ( myint, sqltext, dbms_sql.native, | ||
| + | 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. | Here we can generate SQL to rebuild the links for future use. Note the password is no longer stored in this column. | ||
| - | < | + | < |
| - | ====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==== | + | set pages 1000 |
| - | < | + | select 'begin link_creator ( ''' |
| + | from | ||
| + | , 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 && | ||
| - | =====Create a comma separated list of columns from a select statement===== | + | create or replace procedure & |
| + | begin | ||
| + | execute immediate ' | ||
| + | end create_db_link; | ||
| + | |||
| + | exec & | ||
| + | |||
| + | revoke create database link from & | ||
| + | |||
| + | drop procedure & | ||
| + | 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="/ | ||
| + | cat<< | ||
| + | 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 | ||
| + | begin | ||
| + | dbms_output.enable(null); | ||
| + | select db_link | ||
| + | into | ||
| + | from | ||
| + | where owner = ' | ||
| + | and db_link like ' | ||
| + | dbms_output.put_line(' | ||
| + | exception | ||
| + | when no_data_found then | ||
| + | select ' | ||
| + | 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/ | ||
| + | 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 | ||
| + | , p_connect_usr in varchar2 default ' | ||
| + | , p_connect_pwd in varchar2 default ' | ||
| + | ) is | ||
| + | l_plsql | ||
| + | l_cur | ||
| + | l_uid | ||
| + | l_rc number; | ||
| + | begin | ||
| + | |||
| + | select | ||
| + | into l_uid | ||
| + | from dba_users du | ||
| + | where 1=1 | ||
| + | and | ||
| + | |||
| + | l_plsql := ' | ||
| + | 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 | ||
| + | , language_flag => dbms_sql.native | ||
| + | , userid | ||
| + | ); | ||
| + | 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 (' | ||
| + | </ | ||
| + | |||
| + | ==== 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='& | ||
| + | and t.obj# | ||
| + | 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 ' | ||
| + | < | ||
| + | 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, | ||
| + | from dba_objects o, | ||
| + | (select i.obj#, | ||
| + | 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='& | ||
| + | and i.bo# | ||
| + | where sq.obj# | ||
| + | order by 1,2,3 | ||
| + | / | ||
| + | </ | ||
| + | ==== Create a comma separated list of columns from a select statement ==== | ||
| + | * [[http:// | ||
| + | * [[http:// | ||
| Method 1: | Method 1: | ||
| - | < | + | < |
| + | SELECT parent_id, | ||
| + | | ||
| + | FROM | ||
| + | WHERE parent_id = 0 | ||
| + | GROUP BY parent_id | ||
| + | / | ||
| + | </ | ||
| or | or | ||
| - | < | + | < |
| + | SELECT parent_id, | ||
| + | | ||
| + | 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): | 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===== | + | ==== 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! | Need to quickly share/copy a file or read an html file on Linux? Start a web server! | ||
| - | < | + | < |
| - | and point your browser to http:// | + | python -m SimpleHTTPServer 8000 |
| + | </ | ||
| + | and point your browser to http:// | ||
| or | or | ||
| - | < | + | < |
| + | while true; do nc -l -p 80 -q 1 < index.html; done | ||
| + | </ | ||
| - | =====Get SQL*Plus to generate HTML===== | + | ==== Get SQL*Plus to generate HTML ==== |
| - | * [[http:// | + | * [[http:// |
| - | One way to do it... use -m(arkup) option to specify:<br /> | + | One way to do it... use -m(arkup) option to specify:\\ |
| html on/off - specifies whether to output html output or not | html on/off - specifies whether to output html output or not | ||
| - | head - specify your own customised head contents<br /> | + | head - specify your own customised head contents\\ |
| - | spool off - as a part of the -m tag means do not print the default & | + | spool off - as a part of the -m tag means do not print the default & |
| - | <code>135@@</ | + | <code> |
| + | sqlplus -m "html on head '<link rel=" | ||
| + | </ | ||
| or | or | ||
| - | < | + | < |
| - | besides html and head, you can also specify:<br /> | + | sqlplus / as sysdba |
| - | body - specify your own customised body attributes<br /> | + | set markup html on head '< |
| - | table - override the default table options<br /> | + | </ |
| - | entmap - turn on or off the html replacement characters (<, >, etc...)<br /> | + | besides html and head, you can also specify:\\ |
| - | preformat - uses the & | + | body - specify your own customised body attributes\\ |
| - | * another little gem... | + | table - override the default table options\\ |
| - | < | + | entmap - turn on or off the html replacement characters (<, >, etc...)\\ |
| + | preformat - uses the & | ||
| + | * another little gem... | ||
| + | < | ||
| + | ---------------------------------------- | ||
| + | -- get the last SQL*Plus output in HTML | ||
| + | -- after Tanel Poder | ||
| + | ---------------------------------------- | ||
| - | =====HTML SELECT - Trigger JavaScript ONCHANGE event even when the option is not changed===== | + | set termout off |
| - | < | + | |
| - | =====Display horizontal bar graph in HTML table data cell===== | + | set markup HTML ON HEAD " - |
| + | - | ||
| + | " - | ||
| + | BODY "" | ||
| + | TABLE " | ||
| + | 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 ==== | ||
| + | < | ||
| + | <select onchange=" | ||
| + | <option value="" | ||
| + | < | ||
| + | <option value=" | ||
| + | <option value=" | ||
| + | </ | ||
| + | </ | ||
| + | ==== Display horizontal bar graph in HTML table data cell ==== | ||
| Using Perl | Using Perl | ||
| - | < | + | < |
| + | print ('< | ||
| + | '< | ||
| + | '< | ||
| + | $tspace . | ||
| + | '</ | ||
| + | ); | ||
| + | </ | ||
| Depending on which way around the divs are, you get different interpretations | Depending on which way around the divs are, you get different interpretations | ||
| - | < | + | < |
| - | ====References==== | + | print ("<td class=\" |
| - | * [[http:// | + | "< |
| - | * [[http://www.salvis.com/blog/?p=207|A whole page showing how to do it in all versions back to 7!]] | + | "< |
| + | $tspace | ||
| + | "< | ||
| + | ); | ||
| + | </code> | ||
snippets.1544130327.txt.gz · Last modified: 2018/12/06 21:05 by 91.177.234.129