Paramètre obsolète

https://mikedietrichde.com/2016/11/22/optimizer_adaptive_features-obsolete-in-oracle-12-2/

 

The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2.

It gets replaced with two parameters of whom one is enabled, the other one is disabled by default:

• OPTIMIZER_ADAPTIVE_PLANS=TRUE by default
• OPTIMIZER_ADAPTIVE_STATISTICS=FALSE by default

https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2

Adjusting the optimizer_index_cost_adj Parameter

https://xcoolwinds.wordpress.com/2013/08/23/adjusting-the-optimizer-index-cost-adj-parameter/

 

Issue the following query to view the average wait times for both of the wait events:

SQL> select event, average_wait from v$system_event
       where event like 'db file s%read';
EVENT                                                            AVERAGE_WAIT
---------------------------------                            -------------------------------
db file sequential read                                                   .91
db file scattered read                                                   1.41

 

SQL>

Based on the output of this query, single block sequential reads take roughly 75% of the time it takes to perform a multiblock (scattered) read. This indicates that the optimizer_index_cost_adj parameter should be set to somewhere around 75. However, as mentioned earlier, setting the parameter at the database level isn't recommended—instead, use this parameter sparingly for specific statements where you want to force the use of an index. Note that setting the parameter at the session level requires the DBA to grant the user privileges to issue the alter session command (or you must do it through some other procedural mechanism). A good alternative to having to issue the alter session statement is to specify the OPT_PARAM hint to change the parameter for a specific query, such as /*+ opt_param('optimizer_index_cost_adj',50) */. 

PLug in sqlserver pour OEM 13c

https://docs.oracle.com/cd/E63000_01/SQLPG/overview.htm#SQLPG108

 

https://docs.oracle.com/cd/E24628_01/doc.121/e24473/plugin_mngr.htm#EMADM13102

OEM Agent 12c: Common issues and resolutions

https://developer.rackspace.com/blog/oemagent12c/

 

CPU too heavy

Constraint NOT NULL missing

 

Data Pump Import With EXCLUDE=CONSTRAINT Or INCLUDE=CONSTRAINT Is Excluding And Respectively Not Including NOT NULL Constraints (Doc ID 1930631.1)

After doing a Data Pump import (impdp) in 12.1.0.2 database release with parameter EXCLUDE=CONSTRAINT the NOT NULL constraints from source tables are not in target tables. Sequence of steps leading to the problem:
– Data Pump export of tables with NOT NULL and referential integrity constraints
– Data Pump import with EXCLUDE=CONSTRAINT

 

http://blog.umairmansoob.com/data-dump-import-missing-not-null-constraints/

Problème avant upgrade en 12.2

Pending dst session 

 

datapump (x) 

list des FREE Pps restant dans un VG AIX

 lsvg | egrep -w "backupvg|datavg" | lsvg -i | grep FREE

Taille des fichiers unix

du -ms expdp_$ORACLE_SID_20190* | awk '{ total += $1 }; END { print total }'

 

 

Lister la taille des blobs partitionnés

La requête suivant permet de te lister la taille des partitions qui contiennent des LOBS  :

 

SELECT DS.TABLESPACE_NAME, SEGMENT_NAME,PARTITION_NAME,ROUND(SUM(DS.BYTES)/(1024 * 1024)) AS MB
FROM DBA_SEGMENTS DS
WHERE
DS.TABLESPACE_NAME not in ('SYSAUX','SYSTEM')
AND
SEGMENT_TYPE='LOB PARTITION'
GROUP BY DS.TABLESPACE_NAME,SEGMENT_NAME,PARTITION_NAME
ORDER BY 4;

La requête suivante permet de te lister la taille des partitions pour le segment TOTO : 

SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB
FROM DBA_SEGMENTS DS
WHERE SEGMENT_NAME ='TOTO'
GROUP BY DS.TABLESPACE_NAME,
SEGMENT_NAME;

Je ne suis pas un escroc, je suis un opportuniste...

Suppression des objets pour un schéma

begin
for f in (select table_name from user_tables) loop
execute immediate 'drop table "'||f.table_name||'" cascade constraints';
end loop;
for f in (select object_type, object_name from user_objects
where object_type in ('SEQUENCE','VIEW','FUNCTION','PROCEDURE','PACKAGE','SYNONYM','DATABASE LINK')) loop
execute immediate 'drop '||f.object_type||' "'||f.object_name||'"';
end loop;
for f in (select type_name from user_types) loop
execute immediate 'drop type "'||f.type_name||'" force';
end loop;
end;

/

Tuner une requête Oracle

http://www.br8dba.com/tag/manually-run-sql-advisor/#2

Repasser le code

LeCode

Agrandir datafile avant import

define TBS='SIM_SIM_LOB01'
define DFSZ='10000M'
select 'Alter database datafile '''||FILE_NAME||''' resize '||'&DFSZ'||';' from dba_data_files where TABLESPACE_NAME like '&TBS' order by FILE_NAME;

Déplacer des objets de type LOB dans le bon tabespace (LOB)

SET serveroutput ON;
DECLARE
ordre_sql VARCHAR2(32000 CHAR);
nomTable VARCHAR2(200 CHAR);
nomColonne VARCHAR2(200 CHAR);
nomSchema VARCHAR2(200 CHAR) := 'JOJO';
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
FOR lob_cols IN (
SELECT table_name nomTable, column_name nomColonne FROM ALL_TAB_COLS
WHERE data_type LIKE '%LOB%'
AND owner = nomSchema
AND table_name IN (SELECT table_name FROM ALL_TABLES WHERE partitioned = 'NO' AND TEMPORARY = 'N')
)
LOOP
ordre_sql := 'ALTER TABLE '||nomSchema||'."'
|| lob_cols.nomTable
|| '" MOVE LOB('
|| lob_cols.nomColonne
|| ') STORE AS SECUREFILE (TABLESPACE JOJO_LOB01 NOcompress);';
--EXECUTE IMMEDIATE ordre_sql;
DBMS_OUTPUT.PUT_LINE(ordre_sql);
END LOOP;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('Il faut créer le tablespace JOJO_LOB01 avant d effectuer cette action');
END;
/

Change permission using UT_FILE

https://jenniferlinca.wordpress.com/2008/03/28/using-utl_file-how-file-permissions-are-determined-working-sample/