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.
Tuesday, January 13, 2015
Friday, January 9, 2015
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.
Wednesday, January 7, 2015
How to get Sql query for order by in respecive of Case Insensitive in Oracle
create table test (case varchar2(10));
Table created.
insert intotestvalues('a');
insert intotestvalues('B');
insert intotestvalues('c'); insert intotestvalues('D'); insert intotestvalues('E'); insert intotestvalues('D'); insert intotestvalues('D');
select * fromtest
CASE
D
f
a
B
c
D
E
D
f
select * fromtest order bycase
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
Thursday, January 1, 2015
Friday, December 26, 2014
Wednesday, December 24, 2014
how to check last analyzed table in oracle
SELECT table_name,num_rows,last_analyzed FROM user_TABLES
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------
MTHPASSKBS 611 15-SEP-14
SH_NO 6319 19-SEP-14
D_R_D_SEP 280 11-OCT-14
REQ_TRAINEE_T 3152 20-DEC-14
REQUISITION_T 1251 20-DEC-14
EVALUATION_T 1349 20-DEC-14
EVALUATION_WEEK_T 296 20-DEC-14
REGISTRATION_T 1634 20-DEC-14
OFFICEORDER_T 2069 20-DEC-14
MASTER 710 20-DEC-14
10 rows selected
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------
MTHPASSKBS 611 15-SEP-14
SH_NO 6319 19-SEP-14
D_R_D_SEP 280 11-OCT-14
REQ_TRAINEE_T 3152 20-DEC-14
REQUISITION_T 1251 20-DEC-14
EVALUATION_T 1349 20-DEC-14
EVALUATION_WEEK_T 296 20-DEC-14
REGISTRATION_T 1634 20-DEC-14
OFFICEORDER_T 2069 20-DEC-14
MASTER 710 20-DEC-14
10 rows selected
ANALYZE TABLE command
ANALYZE TABLE <TABLE_NAME> COMPUTE STATISTICS;
Tuesday, December 23, 2014
How to check whether Oracle instance is started using pfile or spfile?
SQL> show parameter spfile;
NAME TYPE VALUE
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
spfile string F:\APP\PRABHAKAR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA
It gives path of spfile file which is getting used by database for starting up.
It means my database is using spfile
No value is displayed because database is not using spfile it is using pfile.
It means database is using pfile for startup.
Subscribe to:
Posts (Atom)
