Oracle – SQL – demo deadlock

Posted by

0

create table t (processed_flag varchar(1));

create bitmap index t_idx on t(processed_flag);

From Tom Kyte “Expert Oracle Database Architecture – 3rd Edition”

insert into t values (‘N’);

declare
pragma autonomous_transaction;
begin
insert into t values (‘N’);
commit;
end;
/

declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

Oracle – Using ASH to Monitor Temporary Tablespace Usage

Posted by

0

Using ASH to Monitor Temporary Tablespace Usage

To use ASH (Active Session History) to monitor temporary tablespace usage use SQL similar to the query below. Detect the space used by queries.

SELECT ASH.inst_id,
  ASH.user_id,
  ASH.session_id sid,
  ASH.session_serial# serial#,
  ASH.sql_id,
  ASH.sql_exec_id,
  ASH.sql_opname,
  ASH.module,
  MIN(sample_time) sql_start_time,
  MAX(sample_time) sql_end_time,
  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (3600*24) etime_secs ,
  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (60*24) etime_mins ,
  MAX(temp_space_allocated)/(1024*1024) max_temp_mb
FROM gv$active_session_history ASH
WHERE ASH.session_type = 'FOREGROUND'
AND ASH.sql_id        IS NOT NULL
--AND sample_time BETWEEN to_timestamp('01-04-2019 10:00', 'DD-MM-YYYY HH24:MI') AND to_timestamp('13-04-2019 12:00', 'DD-MM-YYYY HH24:MI')
AND sample_time > systimestamp - 3
GROUP BY ASH.inst_id,
  ASH.user_id,
  ASH.session_id,
  ASH.session_serial#,
  ASH.sql_id,
  ASH.sql_opname,
  ASH.sql_exec_id,
  ASH.module
HAVING MAX(temp_space_allocated) > 10000
order by 9 desc

Oracle – RMAN commands

Posted by

0

delete archivelog all backed up 1 times to sbt_tape;

delete archivelog all completed before 'sysdate -1';

delete noprompt archivelog all;

....until time "to_date('2018-03-13 14:00:07','YYYY-MM-DD HH24:MI:SS')";

Oracle – Multitenant – Create a sysdba Common User

Posted by

0

Oracle – Multitenant – Create a sysdba Common User

😎 To create a sysdba user that can administrate all containers:

sys@ORCL> show con_id

CON_ID
------------------------------
1
sys@ORCL> show con_name

CON_NAME
------------------------------
CDB$ROOT
 
sys@ORCL> create user c##username identified by <password>;

User created.

sys@ORCL> grant create session to c##username;

Grant succeeded.

sys@ORCL> grant sysdba to c##username container = ALL;

Grant succeeded.



If it is necessary to change the common user prefix (or get rid of it!), the value of _common_user_prefix_common_user_prefix can be changed:


sys@ORCL> select rpad(ksppinm,40) ||' ---->  '||ksppstvl "param, value" from x$ksppi a,x$ksppsv b
  2  where a.indx=b.indx and substr(ksppinm,1,1) = '_' and ksppinm like '%common%';

param, value
_ksipc_common_sl                         ---->
_eliminate_common_subexpr                ---->  TRUE
_dump_common_subexpressions              ---->  FALSE
_common_data_view_enabled                ---->  TRUE
_common_user_prefix                      ---->  C##

Mini Keepass – Database File from iPhone to PC

Posted by

0

Move keepass Database File from iPhone to PC

  • tap Share icon (square/arrow) at bottom centre
  • select “copy to (Google) Drive”
  • save it somewhere on the drive
  • In Google Drive, find the file and tap three dots icon at right side of file
  • Select “Send a copy”
  • the file will be exported
  • rename the file with a “txt” extension if the email destination is likely to remove the attachment due to security settings

Linux – Install java and javac on RedHat Linux

Posted by

0

Install java and javac on RedHat Linux

To install java and the java compiler on RedHat Linux, it is required to installed a couple of packages using yum.

As root, or sudo as a privileged user, execute the following commands at the command line prompt:

yum install java
yum install java-1.8.0-openjdk-devel

SQL Server – Exetended Events example : failed logins

Posted by

0

-- create the extended event session and start it
CREATE EVENT SESSION FailedLogins
ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION 
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username
    )
    WHERE severity = 14
      AND state > 1
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL\MSSQL\Log\xEventSessions\FailedLogins.xel',
    METADATAFILE = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL\MSSQL\Log\xEventSessions\FailedLogins.xem'
  );
GO

ALTER EVENT SESSION FailedLogins ON SERVER
  STATE = START;
GO

-- Query the files
WITH event_data AS 
(
  SELECT data = CONVERT(XML, event_data)
    FROM sys.fn_xe_file_target_read_file(
   'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL\MSSQL\Log\xEventSessions\FailedLogins*.xel', 
   'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL\MSSQL\Log\xEventSessions\FailedLogins*.xem', 
   NULL, NULL
 )
),
tabular AS
(
  SELECT 
    [host] = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
    [app] = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
    [error] = data.value('(event/data[@name="error_number"]/value)[1]','int'),
    [state] = data.value('(event/data[@name="state"]/value)[1]','tinyint'),
    [message] = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)')
  FROM event_data
)
SELECT [host],[app],[state],[message],[date/time]
  FROM tabular
  WHERE error = 18456 
  ORDER BY [date/time] DESC;

Oracle VM – Collect diagnostic info

Posted by

0

Collect diagnostic info for Oracle Virtual Machine Manager and Servers

If you are experiencing issues with OVM, and an SR is opened, Oracle support will request diagnostic information. The shell session below shows collection of this information for OVM 3.4.x

[root@ovm-manager support]# ./vmpinfo3.sh --help
Usage:
    vmpinfo3.sh --username=ADMIN_USER [listservers | servers=SERVERS]
    vmpinfo3.sh --wallet=WALLET [listservers | servers=SERVERS]

    Collect info from all owned servers:
        vmpinfo3.sh --username=admin
    List owned servers:
        vmpinfo3.sh --username=admin listservers
    Collect info from specific server(s):
        vmpinfo3.sh --username=admin servers=server3
        vmpinfo3.sh --username=admin servers=server1,server5,server9

On PCA the --wallet option retrieves the credential from the PCA wallet

Data collected is aggregated in your system temporary directory.
If there is not enough free space in this filesystem you can override
the location by setting $TMPDIR -- see mktemp(1).

# cd /u01/app/oracle/ovm-manager-3/ovm_tools/support/
[root@ovm-manager support]# ./vmpinfo3.sh --username=admin
Enter OVM Manager Password:

Starting data collection
Gathering files from all servers. This process may take some time.
Gathering OVM Model Dump files
Gathering sosreports from servers
The following server(s) will get info collected: [ovs1]
Gathering sosreport from ovs1
Data collection complete
Gathering OVM Manager Logs
Clean up metrics
Copying model files
Copying DB backup log files
Running lightweight sosreport
Archiving vmpinfo3-20181211-080334
=======================================================================================
 Please send /tmp/vmpinfo3-3.4.5.1919-20181211-080334.tar.gz to Oracle support
=======================================================================================