New answers tagged

0

Put the query in a CURSOR and use the %ROWTYPE of that cursor. Declare CURSOR C is select * from something; C_nt is table of C%rowtype; ... NOTE From my experience, those a.* don't always match up to those b.*. I've always had to declare the columns.


3

SELECT COUNT(*) FROM SYS.ALL_VIEWS; Is not a simple query. ALL_VIEWS is a very complicated view, it has many joins requiring special logic to determine what the calling user is actually allowed to see. You can use all_views to see the logic behind all_views, this is how it looks on 19c. select OWNER, VIEW_NAME, TEXT_LENGTH, TEXT, TEXT_VC,TYPE_TEXT_LENGTH, ...


0

Its worth to check this doc id as it looks similar to your context. Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g (Doc ID 260192.1)


0

I'd have a look at the create database command, specifically the "national character set" .


1

I ran this and I have not seen any more of these errors in the mean time: exec dbms_scheduler.disable('LOAD_OPATCH_INVENTORY'); Still too early to tell but that seems to be the culprit.


0

Simplest solution: the queries are actually not the same. Not so obvious solution: the sessions from the application server use different optimizer or NLS parameters and this affects the optimizer in a way it choses another execution plan due to different features enabled, different estimated query cost, NLS specific indexes. You can find some of these ...


2

select a.*, b.Start_Date, b.End_date, sum(b.cost) as total_Cost from Ess.TZeewrld a left join Ess.Wrldest b on a.account_number, = b.account_number, group by b.Start_Date, b.End_date; For starters, assuming we're talking about Oracle (given the reference to TOAD), the group by clause must explicitly include every non-...


0

I intend to to make password file, spfile and control files as part of my backup script Let RMAN take care of both the SPFILE and Control Files: CONFIGURE CONTROLFILE AUTOBACKUP ON The Password File should be swept up by your Server backup.


0

I was able to do this by using a Select As statement. INSERT INTO TARGET_CROSSREF_TABLE (SELECT 0 AS PRIMARY_KEY, DATA_SOURCE_TABLE.FOREIGN_KEY_COND AS FOREIGN_KEY_COND, 1 AS FOREIGN_KEY_LITERAL FROM DATA_SOURCE_TABLE WHERE >CONDITION GOES HERE<) Note that I have a trigger for the primary key that forces it to be correct. I just needed something in ...


1

The exact queries for SGA and PGA tuning you might be looking for are: SELECT sga_size, sga_size_factor, estd_db_time_factor FROM v$sga_target_advice ORDER BY sga_size ASC; SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, ESTD_OVERALLOC_COUNT FROM v$pga_target_advice; You should ...


0

SELECT q'{error detail : &ERROR_DETAIL }' FROM DUAL;


0

SQL> SELECT 'error detail : ' || &ERROR_DETAIL from dual; Enter value for error_detail: 'the field x.y is mandatory' old 1: SELECT 'error detail : ' || &ERROR_DETAIL from dual new 1: SELECT 'error detail : ' || 'the field x.y is mandatory' from dual 'ERRORDETAIL:'||'THEFIELDX.YISMANDATORY' ----------------------------------------- error ...


2

Oracle® Database, Administrator’s Guide, 12c Release 1 (12.1) describes this Enabling Automatic Memory Management With MEMORY_TARGET set, the SGA_TARGET setting becomes the minimum size of the SGA and the PGA_AGGREGATE_TARGET setting becomes the minimum size of the instance PGA. By setting both of these to zero as shown, there are no minimums, and the SGA ...


1

You should not set SGA_MAX_SIZE parameter when using AMM. It fixes the SGA size. If you set pga_aggregate_target to non-zero value when using AMM, then you set a lower bound for PGA size. This is usually not required. Parameter pga_aggregate_limit sets upper PGA bound. For the start, leave it to default value.


Top 50 recent answers are included