Which user executing what type of command in oracle

SELECT SID,SERIAL#,v.schemaname,
DECODE(COMMAND,0,'None',2,'Insert',3,'Select',6,'Update',7,'Delete',8,'Drop',26,'Lock Table'
,44,'Commit',45,'Rollback',47,'PL/SQL Execute','Other') command FROM V$SESSION v


       SID    SERIAL# SCHEMANAME                     COMMAND     
---------- ---------- ------------------------------ --------------
         1          1 SYS                            None          
         2          1 SYS                            None          
         3          1 SYS                            None          
         4          1 SYS                            None          
         5          7 SYS                            None          
         8          1 SYS                            None          
         9         10 SYS                            None          
        63          5 SYS                            None          
        64          1 SYS                            None          
        65          1 SYS                            None          
        66          1 SYS                            None          
        69         52 SYS                            None          
        70          3 SYS                            None          
        72        485 SYS                            None          
       125          1 SYS                            None          
       126          1 SYS                            None          
       127          1 SYS                            None          
       128          1 SYS                            None          
       129          3 SYS                            None          
       131        631 DRSTR                    Select        
       132        448 SYSTEM                  Select        
       187          1 SYS                            None          
       188          1 SYS                            None          
       189          1 SYS                            None          
       190          1 SYS                            None          
       192          4 SYS                            None          
       194          1 SYS                            None          
       195          1 SYS                            None          
       199         27 SYS                            None          

 29 rows selected

snapshot too old error: ORA:01555

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

how to check listener status in oracle

Oracle LSNRCTL – Listener Shutdown and Startup Procedures

 

1) Oracle listener is not running  .

  

connect user prabhakarh

pass:

1) sudo bash

2) connect oracle user (su - oracle)

C:\>lsnrctl status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   32-bit Windows Error: 2: No such file or directory

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   32-bit Windows Error: 61: Unknown error

 

 

2) Oracle listener is running

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2015 12:17

:41

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ

ction

Start Date                10-JAN-2015 12:17:34

Uptime                    0 days 0 hr. 0 min. 7 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   E:\app\honnikery\product\11.2.0\dbhome_1\network\admin

\listener.ora

Listener Log File         e:\app\honnikery\diag\tnslsnr\honnikery-PC\listener\al

ert\log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Services Summary...

Service "CLRExtProc" has 1 instance(s).

  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

3. Stop Oracle Listener

$ lsnrctl stop



Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully

4. Restart Oracle Listener

lsnrctl reload

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
The command completed successfully

listener full help 

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2015 12:28
:24

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start               stop                status
services            version             reload
save_config         trace               change_password
quit                exit                set*
show*

LSNRCTL>


Oracle Listener Help

1. View Available Listener Commands

lsnrctl help command will display all available listener commands. In Oracle 11g following are the available listener commands.

start – Start the Oracle listener
stop – Stop the Oracle listener
status – Display the current status of the Oracle listener
services – Retrieve the listener services information
version – Display the oracle listener version information
reload – This will reload the oracle listener SID and parameter files. This is equivalent to lsnrctl stop and lsnrctl start.
save_config – This will save the current settings to the listener.ora file and also take a backup of the listener.ora file before overwriting it. If there are no changes, it will display the message “No changes to save for LISTENER”
trace – Enable the tracing at the listener level. The available options are ‘trace OFF’, ‘trace USER’, ‘trace ADMIN’ or ‘trace SUPPORT’
spawn – Spawns a new with the program with the spawn_alias mentioned in the listener.ora file
change_password – Set the new password to the oracle listener (or) change the existing listener password.
show – Display log files and other relevant listener information.









 

 

How to get Sql query for order by in respecive of Case Insensitive in Oracle

create table test (case varchar2(10));

Table created. 

 insert into test  values('a');

 insert into test  values('B');

 insert into test  values('c');
 insert into test  values('D');
 insert into test  values('E');
 insert into test  values('D'); 
 insert into test  values('D');

select * from test  

CASE

D
f
a
B
c
D
E
D
f   

 select * from test order by case 

CASE 

B
D
D
D
E
a
c
f
f 

select * from test order by order by nlssort(case,'NLS_SORT=BINARY_CI');

CASE       
----------
a          
B          
c          
D          
D          
D          
E          
f          
f          
 9 rows selected  

how to check free space in mysql database

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;