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