In cases when your terminal has lost the cursor or similar you could use the OS command
reset
It will reset the special characters to their default values at least. You will not have to logoff/logon again to reinitialize your terminal session.
P.S. You will also wish to run clear command (the shortcut for clear usually is 'Ctrl+l') before reset command as well if you need it (to clear status strings of tmux or screen sessions etc.)
Good Luck !
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
The input parameter &1 was assigned value 'FILE', and &2 was assigned a value 'TEXT'. Quite useful construction.
Good Luck !
One of the causes of such error I would like to describe below.
It might sound crazy, but there is software of some vendors, and such software requires logon to an Oracle instance as sysdba from root OS account.
I stuck into the error :
ERROR:
ORA-12546: TNS:permission denied
Addind root to group dba didn't help to resolve the ORA-12546, but adding root to oinstall did :). Actually root must be the member of group dba to avoid the
ERROR:
ORA-01017: invalid username/password; logon denied
You haven't to add root to these groups, you can simply use newgrp command (newgrp [-] oinstall , then newgrp [-] dba). After such 'manipulations' you will be able to logon as sysdba.
Good Luck !
Recently I used lag function to find interval of creating new objects of the same type in the database. Particularly I was interested by the time interval between such creations. The original query was
SELECT
file#
,creation_time
, (creation_time - lag (creation_time) over (order by creation_time)) as interval
FROM
v$datafile a
, v$tablespace b
WHERE
a.ts# = b.ts#
AND b.name = 'XXX'
ORDER BY
2 desc
/
It turned out that it is possible to use interval expression during subtraction :
SELECT
file#
,creation_time
, (creation_time - lag (creation_time) over (order by creation_time)) day (3) to second as interval
FROM
v$datafile a
, v$tablespace b
WHERE
a.ts# = b.ts#
AND b.name = 'BARS_739_ADD'
ORDER BY
2 desc
/
The result is more presentable than in previous query.
The Oracle SQL Language documentation says than the NUMBER datatype will be generated as result of "DATE - DATE" operation. With help dump function, I saw some mysterious (internal ?) datatype 14, not 2 (number datatype). You could also use such "feature" when subtracting DATE datatype values and if trunc or round functions are not of your suitable option.
Good Luck !
When you used to access to host console via iLOM, you probably noticed that long command lines (over 80 symbols) do not break to new continuation line, the new characters just go over existed, already typed characters. It looks like the terminal string
[root@xxx-xxxx-xxx ~]# ddddddddddddddddddddddddddddddddddddddddddddddddddddd
transforms into
aaaat@xxx-xxx-xxx ~]# dddddddddddddddddddddddddddddddddddddddddddddddddddddaaa
To resolve the issue, set tty parameters corresponding to your terminal, for example :
[root@xxx-xxxx-xxx ~]# stty rows 45 columns 158
You should also reopen iLOM console for proper terminal functioning.
Good Luck !
1. You can double parameters in row, Oracle will you only last value. For example, when you use
(ADDRESS=
(PROTOCOL=tcp)
(HOST=localhost)
(port=1520)
(PORT=1521)
)
, the port 1521 which is the last in the group of values of the same group (address), and that one will be used in the connection (1520 will not be tried at all). This behavior is expected though, remember several addresses in the address_list group, but in case of address_list the client tries to establish the connection with every address until succeed, and in our case the client simply goes via the last parameter in the list.
2. You can use some abstract (not existed and not considered by oracle) parameters and values for debugging or for other purposes, for example,
(CONNECT_DATA=
(service_name = pdb1.db-01.site)
(instance_name = cdb121)
(instance_name = cdb_manual)
(aaa = bbb)
)
Here abstract parameter aaa is used. The listener entry in this case could be like this :
22-NOV-2020 09:22:31 * (CONNECT_DATA=(service_name=pdb1.db-01.site)(instance_name=cdb121)(instance_name=cdb_manual)(global_name=cdb121aaa)(aaa=bbb)(CID=(PROGRAM=sqlplus@db-01)(HOST=db-01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=21799)) * establish * pdb1.db-01.site * 0
As we can see sqlplus established connection with instance_name=cdb_manual (the last of the group of instance_name parameters inside of tns descriptor, you can confirm it with help of select sys_context from dual statement)
3. Will be continued...
Good Luck !
I've got this error during execution of "validate database verbose db_name spfile ;" on Oracle 12.2.
The error encountered when property DGConnectIdentifier for the database was set to easyconnect string with shared or dedicated connection type (like 'hostname/service_name:dedicated') or expanded tns entry was used (like '(description=...)').
DataGuard Broker build the connection string with additional useless fragments of database dispatchers setting making the connection string invalid :
Unable to connect to database using hostname/service_name:dedicatedf)(connections=100)(sessions=100)
The error had gone when property DGConnectIdentifier was set to easyconnect string without ":dedicated" piece or to the name of the tns entry (from tnsnames.ora or from ldap).
Update : the issue can occur when validated database also has got dispatchers parameter set to non-default value.
Good Luck !
Instead of the following which returns empty value of a :
$> a=0 ; find /dev/shm/ -uid 54321 -ls | awk '{print $7}' | while read line ; do let a+=${line}; done ; echo "a="${a}
try to use bash's process substitution like this :
a=0 ; while read line ; do let a+=$line ; done < <(find /dev/shm/ -uid 54321 -ls | awk '{print $7}') ; echo "a="$a
This will prevent using newly created shells for pipelines, using the current shell for all operands. Good Luck !
The default character of invoking ssh escape sequence is '~' (from new line of course). Twice typing of such character send the escape character itself. In situations where several sequential connections to terminal (or to console, like Oracle Solaris Zone console via zlogin -C) are made, you should know one 'trick' to disconnect only from last connection made.
Let me explain below. Let's start with session A - a session of your local machine. Here B - proxy vpn machine, C - Solaris server, D - newly installed Oracle Solaris Zone, not configured yet.
A -- ssh_via_vpn_to_B --> B -- ssh_to_server_C --> C -- login_to_zone_console --> D
When you type exit ssh sequence '~.' you immediately close your first ssh session from A to B.In order to close only zlogin session and stay in session on server C, you should type '~~~.' Doing it you transmit escape character to the appropriate deeper session in your 'connection stack'. Take it in your mind.
P.S. this doesn't work when ssh jumping or configured ssh proxy connection is used.
