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

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/

[ORCL] List of all the free space in the tablespace or individual datafile

ts_extent_map.sql