QueryDB

Change the password or follow below steps to update password life limit to UNLIMITED. Step 1: Identify the Users Profile SQL> SELECT profile FROM dba_users WHERE username = 'USER'; Step 2: View the Profile settings SQL> select resource_name, resource_type, limit from dba_profiles where profile='USER_PROFILE' Step 3: Set PASSWORD_LIFE_TIME SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; Step 4: Re-Enter the Password SQL> alter user USER1 identified by "password";

I have an application where I have implemented Oracle AQ. I ran in to a behavior where average time for processing varied as depicted in graph below: In above graph when volume of orders was less, average processing time came out to be more whereas when load increased with time, average time for processing got constant and then when volume started declining, time again started increasing. I analyzed the behavior and found that there is delay in message consumption after message has been produced to AQ. On further analysis I found that AQjmsListenerWorker goes for sleep if message is not available for consumption and sleep time doubles each time (up to peak limit) if message is not available for consumption. Thus optimizing resource utilization if there is no messages in AQ for consumption. On enabling ( -Doracle.jms.traceLevel=6 ) diagnostics logs for aq api.  I analyzed that Listener thread sleep time doubles till 15000 ms (15 sec), starting...

Oracle EXECUTE IMMEDIATE statement implements Dynamic SQL in Oracle. Before Oracle 11g, EXECUTE IMMEDIATE supported SQL string statements. Oracle 11g allows the usage of CLOB datatypes as an argument which eradicates the constraint we faced on the length of strings when passed as an argument to Execute immediate. But a PLSQL block written on Oracle 11g with  C LOB datatypes as an argument to  EXECUTE IMMEDIATE  will not be executed on Oracle 10g.

Problem:- I have recently exported a 11g schema using 11g EXP utility and tried to import into another 11g Instance using 11g IMP utility. But, not all the tables got transferred to the destination instance. On further debugging, I found out that empty tables i.e. tables with NO ROWS (0 rows) did not get exported to dump thus they were missing. Cause:- This is due to oracle feature "Segment creation on Demand (Deferred Segment Creation). Solution :- 1) Use the new Oracle Data Pump utilities for the export and import:  expdp/ impdp instead of exp/imp 2) Turn off the Oracle feature before creating any object     ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE; 3) Force the allocation of extents on each empty table using the following command     ALTER TABLE <table_name> ALLOCATE EXTENT;     Re-run the export EXP command, which would export the empty tables as well.

The new  PIVOT  operator   in 11g  takes data in separate rows, aggregates it and converts it into columns:- For example we have data in such a format:-  SQL> Select * from (.....) Name Time_Diff Pivot_For A 0 0:0:0.0 First A 0 0:0:2.428 Second A 0 0:0:5.548 Third A 0 0:1:1.991 Fourth B 0 0:2:0.0 First B 0 0:0:3.428 Second B 0 0:0:5.248 Third B 0 0:2:3.991 Fourth C 0 1:2:0.0 First C 0 2:0:3.428 Second C 0 3:0:5.248 Third C 0 0:45:3.991 Fourth To aggregate Data in columns we can use Pivot operator in Oracle as follows:- SQL> Select * from (.....) PIVOT(           Max(Time_Diff)           FOR  Pivot_For in ('First' as First, 'Second' as Second,           'Third' as Third, 'Fourth' as Fourth) ...