3 liens privés
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;
oracle - How to troubleshoot enq: TX - row lock contention? - Database Administrators Stack Exchange
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>;