Modification TBS

SELECT tablespace, file_name FROM dba_temp_files;
SELECT tablespace, file_name FROM dba_data_files;

create tablespace STATSPACK datafile '<file>' size 250M reuse extent management local uniform size 1M;

# Temporary
create temporary tablespace STATSTEMP tempfile '<file>' size 50M reuse extent management local uniform size 1M;

ALTER TABLESPACE <tbs> ADD DATAFILE  '<file>'SIZE 25M AUTOEXTEND OFF;
ALTER TABLESPACE <tbs> DROP DATAFILE '<file>' ;

ALTER DATABASE DATAFILE '<file>' RESIZE 225M;
ALTER DATABASE TEMPFILE '<file>' RESIZE 2048M;

DROP tablespace <tbs> INCLUDING CONTENTS and datafiles;

To make a long running request in Oracle :

View:

create or replace function TEST_PROC return number 
IS
start_time DATE := sysdate;
end_time DATE;
curr_time DATE;
id number :=0;
begin
end_time := start_time + interval '3' minute;
loop id := id + 1;
curr_time := sysdate;
exit when curr_time > end_time;
end loop;
return id;
end TEST_PROC;

create or replace VIEW TEST_VIEW (ID) as SELECT TEST_PROC() as ID from dual;

select * from TEST_VIEW;

showing current expensive queries plus most expensive object from execution plan:

select sa.sql_id,
  sp.child_number,
  sp.plan_hash_value,
  sa.parsing_schema_name SQL_PARSED_BY,
  sa.module,
  sa.action,
  sa.buffer_gets,
  sa.BG_PER_EXEC,
  sp.object_owner,
  --sp.object_name, -- this is the group factor
  sa.optimizer_cost,
  sp.cost COST_PER_EXP_OBJECT,
  round((sp.cost/sa.optimizer_cost)*100,0) OBJECT_COST_RELATION,
  listagg(OBJECT_NAME,', ') within group (order by object_name) MOST_EXPENSIVE_OBJECTS,
  sa.sql_text
 from
   (select sql_id,
     child_number,
     parsing_schema_name,
     module,action,
     buffer_gets,
     round(buffer_gets/nullif(executions,0),0) BG_PER_EXEC,
     optimizer_cost,
     sql_text
    from v$sql
    where parsing_schema_name <>'SYS'
   ) sa,
   (select sql_id, child_number, plan_hash_value, object_owner, object_name,cost,
     rank() over (partition by sql_id,child_number order by cost desc nulls last) costrank
     -- result set partitioned by sql and child to avoid duplicates 
     -- when same sql is executed by multiple users
    from v$sql_plan
    where (operation like '%INDEX%' or operation like '%TABLE%' or operation like '%MAT%')
     --and options='FULL' -- would limit to full table scans / full object scans
     and object_owner <>'SYS'
   ) sp
 where sa.sql_id=sp.sql_id
   and sa.child_number=sp.child_number
   and costrank=1 -- only use top costly object of each partition
 group by sa.sql_id,
   sp.child_number,
   sp.plan_hash_value,
   sa.parsing_schema_name,
   sa.module,
   sa.action,
   sa.buffer_gets,
   sa.BG_PER_EXEC,
   sp.object_owner,
   --sp.object_name, -- this is the group factor
   sa.optimizer_cost,
   sp.cost,
   round((sp.cost/sa.optimizer_cost)*100,0),
   sa.sql_text
 order by BG_PER_EXEC desc nulls last
 ;

To follow Oracle statistics daily execution :

 select * from DBA_AUTOTASK_CLIENT_HISTORY ;

 select * from DBA_AUTOTASK_JOB_HISTORY;
 select * from DBA_AUTOTASK_JOB_HISTORY where WINDOW_START_TIME > TO_DATE('27/02/19','DD-MM-YY') and client_name = 'auto optimizer stats collection';
 ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_MAINTENANCE_PLAN';
  exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
 // see JOB_INFO column

Ex : ORA-29373: resource manager is not on

To alter the password expiry policy for a certain user profile in Oracle first check wich profile the user is in using:

select profile from DBA_USERS where username = '<username>';

Then you can change the limit to never expire using:

alter profile <profile_name> limit password_life_time UNLIMITED;

If you want to previously check the limit you may use:

select resource_name,limit from dba_profiles where profile='<profile_name>';

Unlock account :

ALTER USER account IDENTIFIED BY password ACCOUNT UNLOCK;

if user was in grace period, reset password

# if you don't know the password
select spare4 from sys.user$ where name='[user name]';
# reset password
alter user [user name] identified by values '[result from above query]';

Exposer une base de données sur une IP particulière (en dev seulement, non recommandé, security breach, etc...) pour une connection sans service
Dans le fichier listener.ora ($ORACLE_HOME/network/admin/listener.ora)

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      #  (GLOBAL_DBNAME=localhost) -- commenter pour autoriser la connection externe
      (ORACLE_HOME=/home/oracle/oracle-product/product/12.1.0/dbhome_1)
      (SID_NAME=sid11))
    (SID_DESC=
      #  (GLOBAL_DBNAME=localhost) -- commenter pour autoriser la connection externe
      (ORACLE_HOME=/home/oracle/oracle-product/product/12.1.0/dbhome_1)
      (SID_NAME=sid12))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/home/oracle/oracle-product/product/12.1.0/dbhome_1)
      (PROGRAM=extproc)))

Et pense-bête

 lsnrctl start/stop

Compter les objets Oracle, leur taille et quelques statistiques.
Nécessite d'avoir les catalogues Oracle installés

# Long a exécuter
@${ORACLE_HOME}/rdbms/admin/catalog
# dba_data_files est créé ici (encore plus long)
@${ORACLE_HOME}/rdbms/admin/catproc

Requête traficotée :

SET PAGESIZE 50000
set linesize 300
COLUMN owner FORMAT A10
COLUMN tablespace FORMAT A15
COLUMN nomtable FORMAT A40
COLUMN OBJECT_NAME FORMAT A40
COLUMN OBJECT_TYPE FORMAT A15
COLUMN bytes FORMAT 99999999999999
COLUMN num_rows FORMAT 99999999999999
COLUMN avg_row_len FORMAT 99999999999999

SPOOL ./objectSize.txt

select
DBA_OBJECTS.owner as owner,
max(NVL2(DBA_INDEXES.tablespace_name, DBA_INDEXES.tablespace_name, NVL2(DBA_TABLES.tablespace_name, DBA_TABLES.tablespace_name, DBA_LOBS.tablespace_name))) as tablespace,
NVL2(DBA_INDEXES.TABLE_NAME, DBA_INDEXES.TABLE_NAME, NVL2(DBA_TABLES.TABLE_NAME, DBA_TABLES.TABLE_NAME, DBA_LOBS.TABLE_NAME)) as nomtable,
DBA_OBJECTS.OBJECT_NAME,
DBA_OBJECTS.OBJECT_TYPE,
sum(DBA_SEGMENTS.bytes) as bytes,
max(dba_tab_statistics.num_rows) as num_rows,
max(dba_tab_statistics.avg_row_len) as avg_row_len
from
DBA_OBJECTS
left join DBA_TABLES on (DBA_OBJECTS.OBJECT_NAME=DBA_TABLES.TABLE_NAME and DBA_OBJECTS.OWNER = DBA_TABLES.OWNER)
left join DBA_INDEXES on (DBA_OBJECTS.OBJECT_NAME=DBA_INDEXES.INDEX_NAME and DBA_OBJECTS.OWNER = DBA_INDEXES.OWNER)
left join DBA_LOBS on (DBA_OBJECTS.OBJECT_NAME=DBA_LOBS.SEGMENT_NAME and DBA_OBJECTS.OWNER = DBA_LOBS.OWNER)
left join dba_tab_statistics on (dba_tab_statistics.table_name = DBA_TABLES.TABLE_NAME) and (dba_tab_statistics.partition_name is null) and (DBA_OBJECTS.OWNER = dba_tab_statistics.OWNER)
left join DBA_SEGMENTS on (DBA_SEGMENTS.SEGMENT_NAME=DBA_OBJECTS.OBJECT_NAME and DBA_SEGMENTS.OWNER = DBA_OBJECTS.OWNER)
where
DBA_OBJECTS.OBJECT_NAME not like 'BIN$%'
and
DBA_OBJECTS.OWNER not in ('DBSNMP', 'SYS', 'SYSTEM','OUTLN', 'APPQOSSYS', 'WMSYS')
and
DBA_OBJECTS.OBJECT_TYPE IN ('TABLE', 'INDEX', 'LOBINDEX', 'LOBSEGMENT')
group by
DBA_OBJECTS.owner,
DBA_OBJECTS.OBJECT_NAME,
DBA_OBJECTS.OBJECT_TYPE,
NVL2(DBA_INDEXES.TABLE_NAME, DBA_INDEXES.TABLE_NAME, NVL2(DBA_TABLES.TABLE_NAME, DBA_TABLES.TABLE_NAME, DBA_LOBS.TABLE_NAME))
order by
DBA_OBJECTS.owner,
NVL2(DBA_INDEXES.TABLE_NAME, DBA_INDEXES.TABLE_NAME, NVL2(DBA_TABLES.TABLE_NAME, DBA_TABLES.TABLE_NAME, DBA_LOBS.TABLE_NAME)), DBA_OBJECTS.OBJECT_TYPE
;

Que je mets dans un fichier extractSize.sql et

@./extractSize.sql

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT object_name, object_type 
                  FROM   user_objects
                  WHERE  object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')) LOOP
    BEGIN
      IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
    END;
  END LOOP;
END;
/

Connect

sqlplus / as sysdba

sqlplus

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
SELECT DIRECTORY_NAME, DIRECTORY_PATH FROM ALL_DIRECTORIES

export

expdp scott/tiger full=Y SCHEMA=scott directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

import

impdp scott_new/<password> remap_schema=scott:scott_new remap_tablespace=scott_tbs:scott_tbs_new directory=test_dir dumpfile=DB10G.dmp logfile=impdpDB10G.log schemas=scott content=all

exp userid=scott/tiger file=export_scott.dump log=export_scott.log owner=scott

imp userid=scott/tiger file=export_scott.dump log=import_scott.log owner=scott

SELECT * FROM test_tab WHERE ctxsys.catsearch (company_name, '<query><textquery grammar="context">bb%</textquery><query>', NULL)>0;

SELECT * FROM test_tab WHERE company_name like 'BB%';

=> le like a l'air plus rapide

GRANT EXECUTE ON CTXSYS.CTX_DDL TO test;

CREATE TABLE test_tab (company_name VARCHAR2 (60));

INSERT ALL
INTO test_tab VALUES ('CONSULTING')
INTO test_tab VALUES ('LW CONSULTING')
INTO test_tab VALUES ('LW CONSULTING INC')
INTO test_tab VALUES ('LW CONSULTING INC.')
INTO test_tab VALUES ('LW CONSULTING, INC.')
INTO test_tab VALUES ('LW CONSULTING, INC.')
INTO test_tab VALUES ('ORACLE CORPORATION')
SELECT * FROM DUAL;

EXEC CTX_DDL.CREATE_INDEX_SET ('company_set');
CREATE INDEX idx_company ON test_tab (company_name) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('INDEX SET company_set');

CREATE USER test IDENTIFIED BY test;
GRANT CONNECT,RESOURCE TO test;
GRANT CREATE SESSION TO test;
GRANT UNLIMITED TABLESPACE TO test;

alter index index-name unusable
alter index index-name rebuild

oracle : retrouver les FK qui n'ont pas d'index (source)

select
a.owner ,a.constraint_name cons_name
,a.table_name tab_name
,b.column_name cons_column
,nvl(c.column_name,'No Index') ind_column
from all_constraints a
join
ALL_CONS_COLUMNS b on a.constraint_name = b.constraint_name
left outer join
all_ind_columns c on b.column_name = c.column_name
and b.table_name = c.table_name
where constraint_type = 'R'
and a.owner not in ('SYSTEM', 'SYS', 'DBSNMP')
order by 1,3,2;

Enq TX : row lock contention, c’est du blocage au niveau ligne (row). Concrètement, une session est en attente de verrou d’un ROW qui est verrouillé par une autre session.

Segments by row lock wait : Il indique des objets (tables,index,..) qui sont bloqués par les verrous « row lock ».

Rappel : La durée de ces blocages correspond à la durée de transaction et non la durée de réponse des ordres SQL = (Date de début – date de roolback/commit).

Memento : 3 chiffres max dont 1 après la virgule

tout simplement : drop SYS_IMPORT_SCHEMA_0

Purger les tables de statistics

Supprimer l'expiration de password Oracle pour les bases de dev

SELECT PROFILE FROM dba_users WHERE username = 'SCOTT';    
SELECT  LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='SCOTT';

et aussi :

alter user <username> identified by <password>;