Monday 16 June 2014

IMPORTING OBJECTS OF A TABLE FROM ONE SCHEMA TO OTHER SCHEMA USING DATA PUMP

Here Below i have explained to import the object of specific table from one schema to other schema

impdp userid=\"/ as sysdba\" tables=T1 directory=DP dumpfile=table1.dmp logfile=tableimp1.log TABLE_EXISTS_ACTION=SKIP REMAP_SCHEMA=source schema:target schema

Above command will only include objects related to that table.



Friday 6 June 2014

Query throwing ORA-00979 not a group by expression in oracle 11g 11.2.0 but it works fine in 10g

CAUSE- Its i a bug in oracle 11g  bug number 9411496

Action to be performed to resolve this issues is 


SOLUTION 1

*Firsrt of all create the pfile from spfile 

*Then in pfile set the hidden parameter  “_FIX_CONTROL”=’5520732:OFF’ in the pfile

* This parameter disable a given bug number

*_FIX_CONTROL is a undocumented parameter  that can be used to turn off/on a particular bug fix identifier.

*Now, We run the query and it successfully gives a result

SOLUTION 2

 set the parameter optimizer_features_enable to a previous version of Oracle, ’11.1.0.7′ for example

ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='11.1.0.7';

Thank you very much,


Monday 19 May 2014

Problems related to .bash_profile,.bashrc,.bash_history

*Does not getting the .bash_profilewhen executing

we did not get .bash_profile because either our oracle user is not made with the useradd command or it is deleted some how


To recover or resolve this and to get our .bash_profile we should follow the following steps


* su - oracle

* cd /etc/skel

*ls -a (to list out all hidden files)

*copy the .bash_profile from etc/skel to the users home location

* cp /etc/skel .bash_profile  $HOME



Tuesday 13 May 2014

FOR MIGRATING DICTIONARY MANAGED TABLESPACE TO LOCALLY MANAGED

EXECUTE dbms_space_admin.tablespace_migrate_to_local ('TABLESPACE NAME') ;


 


Sunday 11 May 2014

How to kill all similar processes in Unix

In this example all opmn processes are being killed. This command is useful if we find a lot of processes still present at the OS level even after shutting down in this case the Oracle Application Server

ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}

Friday 9 May 2014

To Find Active Session in Database

 

SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
FROM V$Session
WHERE
Status=‘ACTIVE’AND
UserName IS NOT NULL;


To Find Out the Size Of Database

select sum(bytes/(1024*1024*1024) "DB Size in GB" from dba_data_files;