Needed a list of all users on the instance as per their domain name:
use master;
SELECT name FROM sys.database_principals
WHERE type_desc=’DATABASE_ROLE’ and name like ‘domainname%’
UNION ALL
SELECT name FROM sys.server_principals
WHERE type_desc=’SERVER_ROLE’ and name like ‘domainname%’
UNION ALL
SELECT name FROM sys.syslogins where name like ‘domainname%’
sql> ALTER USER [username] IDENTIFIED BY [password]
(all actions from a local command line)
Prior to starting sqlplus, first set the oracle sid (shown here as xxxx) – where the oracle_sid is the name of the database you wish to amend.
TIP : If on linux, first execute “. oraenv ” which will set this sid and other necessary environment variables.
[oracle@DBNODE3 ~]$ . oraenv ORACLE_SID = [oracle] ? PRESTIGE The Oracle base for ORACLE_HOME=/opt/mnt1/app/oracle/product/11.2.0/dbhome_1 is /opt/mnt1 [oracle@DBNODE3 ~]$ sqlplus / as sysdba sql> alter user [username] identified by [password]
start > run > cmd
SET ORACLE_SID=xxxx sqlplus / AS sysdba sql> ALTER USER sys IDENTIFIED BY [password]
Processor licensing
You pay per Processor you run the Oracle software on; however Oracle has a special definition of “processor” which may or may not match that of your hardware vendor.
Intel Hyperthreading technology that makes one core look like two counts as 1 processor for this purpose.
Other soft partitioning technologies are treated differently; for example, the Solaris OS has a concept of Containers, this is similar to hard partitioning on an HP machine; however Oracle does not recognise software partitioning with Solaris Containers prior to Solaris 10; and even then there are stipulations.
Hard partitioning methods such as Sun’s Domains, IBM’s Logical partitioning are recognised as legitimate methods to limit the amount of resources that can run the Oracle software. If you are a MS or VMWare virtual machine user, the following document is a must read; Oracle Partitioning Policy document
Oracle Standard Edition uses a per-socket licensing scheme v Enterprise Edition uses a per-core licensing scheme.
Oracle Standard Edition
If you use Standard Edition or Standard Edition One on a 2 processor system you simply need 2 licenses. However, if you use Enterprise Edition you need to take the number of cores into account as well.
Enterprise Edition Per-core licensing
Multi-core processors are priced as (number of cores)*(multi-core factor) processors, where the multi-core factor is:
This link describes this best: http://www.oracle.com/us/corporate/contracts/processor-core-factor-table-070634.pdf
Sql Tools
Sql Developer is a free cross-platform cross-database SQL tool (Oracle, Mysql & MS-SQL) that is recommended. there are many others!
ELECT DISTINCT
s1.username ||'@'|| s1.machine ||'(INST=' || s1.inst_id ||' SID_SERIAL#= '|| s1.sid ||'_'||s1.serial#||')' AS Blocker
,s2.username||'@'||s2.machine||' (INST='||s1.inst_id||' SID_SERIAL# = '||s2.sid ||'_'||s2.serial#||' Event='|| s2.event ||')' AS Blockee
, s2.program ||'@'|| s1.machine AS Program
,SUBSTR(ob.object_name,1,40) ||' SQL -> ' || s3.sql_text AS Blocked_OBJ_SQL, s4.sql_text AS Blocker_SQL
FROM gv$lock l1, gv$session s1, gv$lock l2
, gv$session s2, v$sql s3
, gv$active_session_history ash, v$sql s4, dba_objects ob, gv$locked_object lo
WHERE s1.sid=l1.sid
AND s2.sid=l2.sid
AND l1.block=1
AND l2.request >0
AND l1.id1=l2.id1
AND l2.id2=l2.id2
AND s2.sql_id = s3.sql_id
AND s1.prev_sql_id = s4.sql_id
AND lo.object_id = ob.object_id
AND lo.SESSION_ID = s2.sid
AND s2.seconds_in_wait > 10;
Note
1. this is using global view of GV$SESSION_WAIT – and reports back on any session which has waited > 10 seconds. So if your case scenario is looking for sessions which are blocked for less period of time then change the final line in the above script as required.
AND s2.seconds_in_wait > 10;
Be cautious when amending this time period, as some applications may have by design, plenty of transactions which do cause row lock transactions eg scheduling applications. Normally, the purpose of this script is to look for anomalies.
2. seconds_in_wait has been deprecated from oracle 11g+. Currently is still works in 11gR2. Future releases may not include this column
if this bothers you, then change script line :
AND s2.seconds_in_wait > 10;
to read
AND s2.WAIT_TIME_MICRO/1000000 > 10;
We can easily simulate a a blocking lock situation using a test table. We will open two sqlplus sessions to the database and then issue the following commands to demonstrate this
1. using sqlplus connect to the database, eg
C:\sqlplus [username]/[password]@database_name SQL*Plus: RELEASE 11.2.0.1.0 Production ON Wed Oct 5 13:33:26 2011 Copyright (c) 1982, 2010, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition RELEASE 11.2.0.1.0 - 64bit Production WITH the Partitioning, OLAP, Data Mining AND REAL Application Testing options SQL>
Session A.
here, we create a dummy table and insert some test data into it. Check the data via a select, and then lock all rows in the table :
SQL> CREATE TABLE test(colA VARCHAR2(1), colB VARCHAR2(1)); TABLE created. SQL> INSERT INTO test VALUES (1,'a'); 1 ROW created. SQL> INSERT INTO test VALUES (1,'b'); 1 ROW created. SQL> SELECT * FROM test ; C C - - 1 a 1 b SQL> COMMIT; COMMIT complete. sql> SELECT * FROM test FOR UPDATE;
now we have locked all rows in the table.
so switching to another sqlplus session, using the same connection string as before
eg
C:\sqlplus [username]/[password]@database_name SQL>
lets first try and see if we can select any row data :
SQL> SELECT * FROM test ; C C - - 1 a 1 b
good – expected behaviour as a row or table lock does not normally prevent us from seeing the before image. Now lets try updating a row :
SQL> UPDATE test SET colA='Z' WHERE colA='1';
now you should see the session hangs. And you can prove this from running the script as above giving us the example result of :
so back to Session A – lets close the lock
SQL> COMMIT; COMMIT complete.
and then checking session B, you should see that the update has now completed :
SQL> UPDATE test SET colA='Z' WHERE colA='1'; 1 ROW updated.
When reviewing objects for possible tuning issues, it is handy to have statistics such as the number of internal transaction list (ITL) waits, buffer busy waits and row lock waits that the object has experienced. Combined with the number of logical and physical reads the object has experienced, the above statistics give a complete picture of the usage of the object in question.
To see a list of these per object recorded since the database/instance was last restarted, run the following sql
SELECT * FROM
(
SELECT
DECODE
(GROUPING(a.object_name), 1, 'All Objects', a.object_name)
AS "Object",
SUM(CASE WHEN
a.statistic_name = 'ITL waits'
THEN
a.VALUE ELSE NULL END) "ITL Waits",
SUM(CASE WHEN
a.statistic_name = 'buffer busy waits'
THEN
a.VALUE ELSE NULL END) "Buffer Busy Waits",
SUM(CASE WHEN
a.statistic_name = 'row lock waits'
THEN
a.VALUE ELSE NULL END) "Row Lock Waits",
SUM(CASE WHEN
a.statistic_name = 'physical reads'
THEN
a.VALUE ELSE NULL END) "Physical Reads",
SUM(CASE WHEN
a.statistic_name = 'logical reads'
THEN
a.VALUE ELSE NULL END) "Logical Reads"
FROM
gv$segment_statistics a
WHERE
a.owner LIKE UPPER('&owner')
GROUP BY
rollup(a.object_name)) b
WHERE (b."ITL Waits">0 OR b."Buffer Busy Waits">0)
DECLARE @ShowResultsInKB1_MB0 BIT
SET @ShowResultsInKB1_MB0 = 0;
WITH TableSizes AS (
SELECT
a3.name AS [schemaname],
a2.name AS [tablename],
a1.ROWS AS ROW_COUNT,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.DATA * 8 AS DATA,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.DATA THEN (a1.used + ISNULL(a4.used,0)) - a1.DATA ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN ROW_COUNT
ELSE 0
END
) AS [ROWS],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS DATA,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.TYPE <> N'S' AND a2.TYPE <> N'IT'
)
SELECT SchemaName, TableName, ROW_COUNT
, CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN reserved ELSE reserved / 1024.0 END, 2) AS DECIMAL(18,2)) AS reserved
, CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN DATA ELSE DATA / 1024.0 END, 2) AS DECIMAL(18,2)) AS DATA
, CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN index_size ELSE index_size / 1024.0 END, 2) AS DECIMAL(18,2)) AS index_size
, CAST(ROUND(CASE @ShowResultsInKB1_MB0 WHEN 1 THEN unused ELSE unused / 1024.0 END, 2) AS DECIMAL(18,2)) AS unused
FROM TableSizes
ORDER BY ROW_COUNT DESC,SchemaName, TableName
example output
run the following to see the database locks – works for 2005+
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction AS IsUserTransaction,
AT.name AS TransactionName,
CN.auth_scheme AS AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
By default, MySQL does not allow remote clients to connect to the MySQL database.
If you try to connect to a remote MySQL database from your client system, you will be shown message
ERROR 1130: Host is not allowed to connect to this MySQL server
To allow a specific client ip-address to access the mysql database running on a server, execute the following command on the server that is running the mysql database.
$ mysql -u root -p
Enter password:
mysql> use mysql
mysql> GRANT ALL ON *.* to root@'192.168.1.10' IDENTIFIED BY 'your-root-password';
where 192.168.1.10 is the address of the client machine
Recently needed to query the zabbix database running under mysql to obtain the last session logins per user. Zabbix stores all times since epoch (Jan 1, 1970 00:00 GMT), so it will be necessary to convert these. This page has useful formatting when using FROM_UNIXTIME example
select users.alias,users.name, FROM_UNIXTIME(lastaccess ,"%Y-%m-%d %T") AS `lastaccess` from sessions,users where users.userid=sessions.userid and lastaccess > (UNIX_TIMESTAMP(NOW()) -282800) order by lastaccess desc
output: 
When there is an issue with an instance or session hang run this sql
sqlplus ‘/ as sysdba’
oradebug setmypid
oradebug unlimit
oradebug setinst all
oradebug -g all hanganalyze 4 <=== Issue command 3 times one minute apart
oradebug -g all dump systemstate 267 <=== Issue command 3 times one minute apart
quit
help is available :
sql> oradebug help
Trace files will be produced in the user_dump_dest directory. They are named according to the SPID of the process where the oradebug command is executed.
SQL> sho parameter user_dump_dest NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ user_dump_dest string C:\oracle\diag\rdbms\xxx\xxx1\trace C:\Temp>set ORACLE_SID=xxxa1 C:\Temp>sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Thu Aug 26 08:43:27 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 11.1.0.7.0 - 64bit Production With the Real Application Clusters option SQL> set timing on SQL> set time on 08:43:38 SQL> spool oradebug_xxxx1 08:43:40 SQL> oradebug setmypid
Big Nose Kates 