Wednesday, November 28, 2012

Error related to /dev/shm while oracle installation

Error
Using Automatic Memory Management requires ...... .The current available space in "" is only MB.If you want to use Automatic Memory Management you should either free up some space in /dev/shm or reduce the memory allocated to oracle.
or
sql>startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/*.ora'

ORA-00845: MEMORY_TARGET not supported on this system

Cause:
lesser amount of available space in /dev/shm .

Solution:
increase the size of /dev/shm (tmp directory in linux)
to increase the size do as below:

temporary increase in size :
# mount -o remount,size=8G /dev/shm
Verify the size
# df -h


permanent changes:
1. vim /etc/fstab (made change w.r.t. tmpfs), add this ",size=8g"
tmpfs     /dev/shm          tmpfs   defaults,size=8g        0 0

2. mount -o remount tmpfs

3. df -h      (check the changes

Tuesday, November 27, 2012

Uninstall Oracle from Linux Operating System

Remove Oracle from Linux O.S.
  • Stop any outstanding processes using the appropriate utilities.
    # oemctl stop oms user/password
    # agentctl stop
    # lsnrctl stop
    Alternatively you can kill them using the kill -9 pid command as the root user.
  • Delete the files and directories below the $ORACLE_HOME.
    # cd $ORACLE_HOME
    # rm -Rf *
  • Delete the /etc/oratab file. If using 9iAS delete the /etc/emtab file also.
    # rm /etc/oratab /etc/emtab

sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied

sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied

or

sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied


problem :

SELinux is enable

Solution:

two ways to overcome this

First  : Disable the SELinux

Second : (i prefer this)

Configure SELinux to allow /u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1 using below command


chcon -t textrel_shlib_t '/u01/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1'

Note: change path as per your present directory structure 

Monday, November 26, 2012

SQLCMD error

Sqlcmd: Error: Internal error at ReadText (Reason: No mapping for the Unicode character exists in the target multi-byte code page).

comment at page:
http://connect.microsoft.com/SQLServer/feedback/details/549363/internal-error-in-sqlcmd-utility

Posted by Microsoft on 10/24/2011 at 6:43 PM
This bug has been fixed and will be available in the next release.

Thanks again for reporting this.

Microsoft SQL Server.

Friday, November 23, 2012

SQLCMD not working


HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

SOLVED:
1.Enable the named pipes

2. change the properties as below
 3.Restart the SQLServer services.
 4. Run> SQLCMD

Tuesday, November 6, 2012

Fulltext Index SqlServer

1. create catalog
2. Fulltext Index
3. Warning: Table or indexed view 'Documents' has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.

1. create catalog
-- Create a fulltext catalog
CREATE FULLTEXT CATALOG ft_catalog_database1
GO

-- Create a table to contain the poems
CREATE TABLE poems
(
 id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
 author VARCHAR(50) NOT NULL,
 title VARCHAR(100) NOT NULL,
 poem TEXT NOT NULL
);



-- Insert some data
INSERT INTO poems
(
 author, title, poem
)
SELECT
 
 'Johann Wolfgang von Goethe',
 'Night Thoughts',
 'Stars, you are unfortunate, I pity you,
Beautiful as you are, shining in your glory,
Who guide seafaring men through stress and peril.'
UNION ALL
SELECT
 'Nikki Giovanni',
 'I Love You',
 'I love you
because the Earth turns round the sun
because the North wind blows north.'
UNION ALL
SELECT
 'Lord Byron',
 'She Walks In Beauty',
 'She walks in beauty, like the night
Of cloudless climes and starry skies;
And all that''s best of dark and bright
Meet in her aspect and her eyes'
UNION ALL
SELECT
 'Christopher Marlowe',
 'Come Live With Me',
 'Come live with me, and be my love;
And we will all the pleasures prove
That valleys, groves, hills, and fields,
Woods or steepy mountain yields.'
UNION ALL
SELECT
 'Thomas Campbell',
 'Freedom and Love',
 'How delicious is the winning
Of a kiss at love''s beginning,
When two mutual hearts are sighing
For the knot there''s no untying!.';

-- Create a fulltext index on title and poem
CREATE FULLTEXT INDEX ON database1.dbo.poems
(
 title
 Language 0X0,
 poem
 Language 0X0
)
KEY INDEX PK__poems__00551192 ON ft_catalog_database1
WITH CHANGE_TRACKING AUTO;
 
*note - PK__poems__00551192 (primary key name)


It will populate error
Warning: Table or indexed view 'Documents' has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.
--it will return result
SELECT * 
FROM poems
WHERE CONTAINS(title, '"I love you"');
 
error is here
because it does not support writetext/updatetext as below:
 
UPDATETEXT
 Following is the code snippet to update portion of the string of the column. We will replace ‘I love you’ with ‘hello’ so that result will be ‘hello...’

DECLARE @ptr varbinary(16)
SELECT @ptr = textptr(poem)  FROM poems  WHERE id = 1
UPDATETEXT poems.poem @ptr 0 10 'hello'
 
 
--RESOLUTION 
--it will not return any result 
SELECT * 
FROM poems
WHERE CONTAINS(poem, 'hello');

Saturday, October 20, 2012

SqlDeveloper : Backspace button not working

How to resolve the problem:
Tools -> Preferences -> Accelerators -> Load Preset -> Default -> OK


Saturday, October 13, 2012

Forgot username password of POSTGRES

Look for pg_hba.conf file in installtion directory
C:\Program Files\PostgreSQL\9.1\data\pg_hba.conf

edit this file below:
(replace md5 with trust)
# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:
host         all          all                127.0.0.1/32            trust
# IPv6 local connections:
host         all          all                ::1/128                     trust



restart the service


login annd enjoy




Thursday, September 27, 2012

Error While configuring "emca"

C:\Documents and Settings\user> emca -config dbcontrol db -repos create

STARTED EMCA at **** **:**:** PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl
Exception in thread "main" oracle.sysman.emcp.exception.DatabaseUnavailableException: Database instance unavailable.
at oracle.sysman.emcp.DatabaseChecks.throwDBUnavailableException(DatabaseChecks.java:151)
at oracle.sysman.emcp.DatabaseChecks.checkDbAvailabilityImpl(DatabaseChecks.java:144)
at oracle.sysman.emcp.DatabaseChecks.checkDbAvailability(DatabaseChecks.java:163)
at oracle.sysman.emcp.DatabaseChecks.getDbServiceName(DatabaseChecks.java:582)
at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1272)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:574)
at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:522)

orcl is the oracle sid on my computer and database status is RUNNING
                                                                  ----------------------------


                                                                  ----------------------------
C:\Documents and Settings\*****>emctl
EM Configuration issue. E:\app\*****\product\11.2.0\dbhome_1/*****_orcl not found.

Due to this error ... "emctl start dbconsole" throws error.



SOLUTION
configuring the Enterprise Manager using DBCA ?

dbca->configure database options -> "select the database" -> Configure Enterprise Manager



Saturday, September 15, 2012

Enabling Remote Connections for MSsql

unable to connect with sql server remotely (using sql developer or other jdbc connections)

 “Cannot connect to SQL-Server-Instance-Name
Login failed for user ‘username‘.

Step-by-step
1.Open SQL Server Configuration Manager.
         Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools ->SQL Server   Configuration Manager.
2.Start sqlserver services.
3.On the left window, expand SQL Server Network Configuration -> Protocols for                                  SQLEXPRESS. You see that TCP/IP protocol status is disabled.
4.Right-click on TCP/IP and select Enable to enable the protocol.
5.There is a pop-up shown up that you have to restart the SQL Service to apply changes.
6.On the left window, select SQL Server Services. Select SQL Server (SQLEXPRESS) on the right window -> click Restart. The SQL Server service will be restarted.

Sunday, September 9, 2012

Full Text Index error

SQL> create table t ( a nvarchar2(2000));

Table created.

SQL>
SQL>
SQL> create index idx on t(a) indextype is ctxsys.context;
create index idx on t(a) indextype is ctxsys.context
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10509: invalid text column: A
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364


solution
Change datatype of column to 'blob' or 'clob'
then try again

Sunday, September 2, 2012

dbms_metadata.get_ddl Not DisPlay full query

SQL> select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14

solve::

set heading off;
set echo off;
Set pages 999;
set long 90000;

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

Saturday, August 25, 2012

v$session equivalent in sqlserver ,User Connected to SqlServer and Kill session

SELECT login_name,session_id
FROM sys.dm_exec_sessions
go



For kill Session
select  login_name, session_id,  host_name from 
FROM sys.dm_exec_sessions ;


KILL SESSION_ID
go

Monday, July 23, 2012

Create table at Run Time

create or replace procedure p1
as
begin
execute immediate 'create table table_name (column_name datatype)';
end;
/



SQL> exec p1;
ORA-01031: insufficient privileges
ORA-06512: at "schema.procedure", line 4 / line 7
ORA-06512: at line 1

oops error !!!!!!


Don't worry

Just add one line to your procedure:



create or replace procedure p1
AUTHID CURRENT_USER
as
begin
execute immediate 'create table table_name (column_name datatype)';
end;
/

SQL> exec p1;
PL/SQL procedure successfully completed.

Sunday, July 1, 2012

TRY to execute select query on tablAe , that contains BLOB datatype

sql>select * from blob_test; (in 10g)
error
SP2-0678: Column or attribute type can not be displayed by SQL*Plus

solve (try this)
-------
 select utl_raw.cast_to_varchar2(a)  from blob_test;
-- where 'a' is column_name and 'blob_test' is table_name

Wednesday, June 27, 2012

ORACLE Error - ORA-01034 & ORA-27101

Error : ORA-01034: ORACLE not available
       ORA-27101: shared memory realm does not exist

Cause: This error shown while connected to oracle schema,because of Oracle service is running but database is not opened.

Solution -> connect to oracle schema and startup database
 sql>sys/password as sysdba
 sql>startup


Monday, June 18, 2012

SP2-0110: Cannot create save file "afiedt.buf"

This error is most general error, everybody face this error either he is a student or trainee or experienced. Most of trainee starts from oracle and we generally used the SQL CMD to write the commands and execute the command. The feature of SQL CMD is that we can re-edit the SQL if it is not executed well, to use this feature we need to assign a buffer file where it can save the the previous execution history.

Error
SP2-0110: Cannot create save file "afiedt.buf"

SOLUTION-

1.Open CMD
2.set editor=notepad
3.open sqlplus and login with user

4.Thats it.

ORA-28056: Writing audit rec .... solved

ORA-28056: Writing audit records to Windows Event Log failed Error



Error faced during login user

SQL> conn scott/tiger
ERROR:
ORA-28056: Writing audit records to Windows Event Log failed
ORA-01031: insufficient privileges

solution
This was because the Event Viewer log is full and unable to log events.We need to clear the event log as follows Control Panel->Administrative Tools->Event Viewer->Clear All Events

picture can vary syatem to system

this is worked for me
web stats