Purpose
The purpose of optimizer_index_cost_adj parameter is to force use index even the cost is high. (decides large-table full-table scans or index range scans)
Usage
Current setting:
SQL> show parameter optimizer_index_cost_adj;
-- default is 100
-- ranges between 1 and 10000
(options like -> 10 | 100 | 1000)
Change setting permanently:
add this into init.ora file;
*.optimizer_index_cost_adj=10
-- "10" is to force it uses index
-- "100" is the default value
-- "1000" is not to force and it tries to different method (large-table full-table) to search data
Then restart the instance to perform.
Change setting for current instance - temporary:
SQL> alter system set optimizer_index_cost_adj = 10;
Result
SQL> show parameter optimizer_index_cost_adj;
-- output; 10
Showing posts with label oracle. Show all posts
Showing posts with label oracle. Show all posts
Friday 26 January 2018
Friday 19 January 2018
How to define primary key on existed table?
e.g.
steps;
lock table -> add new column for primary key -> update data with counter -> define sequence -> define trigger -> lastly, define primary key -> recompile invalid objects (optional)
SQL>
LOCK TABLE HR.EMPLOYEES_NEW IN EXCLUSIVE MODE NOWAIT
/
ALTER TABLE HR.EMPLOYEES_NEW
ADD (ID NUMBER)
/
DECLARE
str_exec VARCHAR2 (250);
counter NUMBER := 0;
last_seq_id NUMBER;
BEGIN
FOR cur_node IN ( SELECT ROWID, id
FROM HR.EMPLOYEES_NEW
ORDER BY HIRE_DATE ASC)
LOOP
BEGIN
str_exec :=
'update HR.EMPLOYEES_NEW set id = '
|| counter
|| ' where rowid = '
|| ''''
|| cur_node.ROWID
|| '''';
--DBMS_OUTPUT.put_line (str_exec);
EXECUTE IMMEDIATE str_exec;
COMMIT;
counter := counter + 1;
END;
END LOOP;
SELECT ID
INTO last_seq_id
FROM ( SELECT ID
FROM HR.EMPLOYEES_NEW
WHERE ID IS NOT NULL
ORDER BY ID DESC)
WHERE ROWNUM < 2;
str_exec :=
'CREATE SEQUENCE HR.EMPLOYEES_NEW_ID_SEQ START WITH '
|| last_seq_id
|| ' MAXVALUE 999999999999999999999999999999 MINVALUE '
|| last_seq_id
|| ' NOCYCLE NOCACHE NOORDER';
--DBMS_OUTPUT.put_line (str_exec);
EXECUTE IMMEDIATE str_exec;
END;
/
CREATE OR REPLACE TRIGGER HR.TRG_EMPLOYEES_NEW_ID
BEFORE INSERT
ON HR.EMPLOYEES_NEW
FOR EACH ROW
BEGIN
IF :new.ID IS NULL
THEN
:new.ID := HR.EMPLOYEES_NEW_ID_SEQ.NEXTVAL;
END IF;
END TRG_EMPLOYEES_NEW_ID;
/
ALTER TABLE HR.EMPLOYEES_NEW ADD
CONSTRAINT EMPLOYEES_NEW_PK
PRIMARY KEY (ID)
ENABLE
VALIDATE
/
EXEC SYS.UTL_RECOMP.recomp_parallel(4)
/
steps;
lock table -> add new column for primary key -> update data with counter -> define sequence -> define trigger -> lastly, define primary key -> recompile invalid objects (optional)
SQL>
LOCK TABLE HR.EMPLOYEES_NEW IN EXCLUSIVE MODE NOWAIT
/
ALTER TABLE HR.EMPLOYEES_NEW
ADD (ID NUMBER)
/
DECLARE
str_exec VARCHAR2 (250);
counter NUMBER := 0;
last_seq_id NUMBER;
BEGIN
FOR cur_node IN ( SELECT ROWID, id
FROM HR.EMPLOYEES_NEW
ORDER BY HIRE_DATE ASC)
LOOP
BEGIN
str_exec :=
'update HR.EMPLOYEES_NEW set id = '
|| counter
|| ' where rowid = '
|| ''''
|| cur_node.ROWID
|| '''';
--DBMS_OUTPUT.put_line (str_exec);
EXECUTE IMMEDIATE str_exec;
COMMIT;
counter := counter + 1;
END;
END LOOP;
SELECT ID
INTO last_seq_id
FROM ( SELECT ID
FROM HR.EMPLOYEES_NEW
WHERE ID IS NOT NULL
ORDER BY ID DESC)
WHERE ROWNUM < 2;
str_exec :=
'CREATE SEQUENCE HR.EMPLOYEES_NEW_ID_SEQ START WITH '
|| last_seq_id
|| ' MAXVALUE 999999999999999999999999999999 MINVALUE '
|| last_seq_id
|| ' NOCYCLE NOCACHE NOORDER';
--DBMS_OUTPUT.put_line (str_exec);
EXECUTE IMMEDIATE str_exec;
END;
/
CREATE OR REPLACE TRIGGER HR.TRG_EMPLOYEES_NEW_ID
BEFORE INSERT
ON HR.EMPLOYEES_NEW
FOR EACH ROW
BEGIN
IF :new.ID IS NULL
THEN
:new.ID := HR.EMPLOYEES_NEW_ID_SEQ.NEXTVAL;
END IF;
END TRG_EMPLOYEES_NEW_ID;
/
ALTER TABLE HR.EMPLOYEES_NEW ADD
CONSTRAINT EMPLOYEES_NEW_PK
PRIMARY KEY (ID)
ENABLE
VALIDATE
/
EXEC SYS.UTL_RECOMP.recomp_parallel(4)
/
Labels:
lock,
oracle,
primary key,
snapshot
How to use "Toad for Oracle" Help?
e.g.
Toad for Oracle -> Help -> Contents -> Search "Debugging a Procedure or Function Tutorial"
Toad for Oracle -> Help -> Contents -> Search "Debugging a Procedure or Function Tutorial"
Friday 12 January 2018
enq: JI - contention wait event solution
Action
Synchronizing data with remote database(NEW_XE)
SQL>
BEGIN DBMS_SNAPSHOT.REFRESH(LIST => 'HR.SS_EMPLOYEES_NEW',METHOD => 'F' ); END;
Problem
enq: JI - contention wait event occurs
Determine the problem
SQL>
SELECT eq_name "Enqueue",
ev.name "Enqueue Type",
eq.req_description "Description"
FROM v$enqueue_statistics eq, v$event_name ev
WHERE eq.event# = ev.event# AND ev.name = 'enq: JI - contention'
ORDER BY ev.name;
Description -> Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
Solution I
Kill problematic session
SQL>
SELECT DISTINCT
'ALTER SYSTEM KILL SESSION '''
|| SID
|| ','
|| SERIAL#
|| ''' IMMEDIATE;'
AS KILL_THEM_ALL
FROM V$SESSION
WHERE TYPE <> 'BACKGROUND'
AND STATUS = 'ACTIVE'
AND EVENT = 'enq: JI - contention';
Solution II
Check database link
SQL>
SELECT * FROM V$INSTANCE@NEW_XE
Synchronizing data with remote database(NEW_XE)
SQL>
BEGIN DBMS_SNAPSHOT.REFRESH(LIST => 'HR.SS_EMPLOYEES_NEW',METHOD => 'F' ); END;
Problem
enq: JI - contention wait event occurs
Determine the problem
SQL>
SELECT eq_name "Enqueue",
ev.name "Enqueue Type",
eq.req_description "Description"
FROM v$enqueue_statistics eq, v$event_name ev
WHERE eq.event# = ev.event# AND ev.name = 'enq: JI - contention'
ORDER BY ev.name;
Description -> Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
Solution I
Kill problematic session
SQL>
SELECT DISTINCT
'ALTER SYSTEM KILL SESSION '''
|| SID
|| ','
|| SERIAL#
|| ''' IMMEDIATE;'
AS KILL_THEM_ALL
FROM V$SESSION
WHERE TYPE <> 'BACKGROUND'
AND STATUS = 'ACTIVE'
AND EVENT = 'enq: JI - contention';
Solution II
Check database link
SQL>
SELECT * FROM V$INSTANCE@NEW_XE
Labels:
database link,
enq: JI - contention,
oracle,
snapshot
Friday 5 January 2018
e.g. tablespace move
Check tablespaces on database
SQL>
SELECT * FROM DBA_TABLESPACES WHERE CONTENTS = 'PERMANENT';
Delete all records on tables if needs
SQL>
SELECT 'TRUNCATE TABLE ' || OWNER || '.' || TABLE_NAME || ';' FROM ALL_TABLES WHERE OWNER = 'HR';
Before movement operation, enable row movement feature
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ENABLE ROW MOVEMENT;' FROM ALL_TABLES WHERE TABLESPACE_NAME = 'HR';
Shrink tables
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' SHRINK SPACE;' from ALL_TABLES WHERE TABLESPACE_NAME = 'HR';
If ORA-10631 error occurs;
ALTER TABLE HR.EMPLOYEES_NEW SHRINK SPACE
Error at line 99
ORA-10631: SHRINK clause should not be specified for this object
Solution:
If FUNCTION-BASED NORMAL index stores on related table, this error occurs
1- Drop the function-based index.
2- Shrink the table.
3- Recreate the index again on the table.
Define a fresh tablespace
SQL>
CREATE TABLESPACE HR_NEW DATAFILE
'/oradata/XE/data.dbf' SIZE 1G AUTOEXTEND ON NEXT 8K MAXSIZE 5G,
'/oradata/XE/data01.dbf'' SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE 50G,
'/oradata/XE/data02.dbf SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE 50G
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
If need, move related tablespace to a fresh tablespace
For tables:
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE TS_HR_NEW;' from ALL_TABLES WHERE TABLESPACE_NAME = 'TS_HR';
For indexes:
SQL>
SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE TS_HR_NEW;' FROM ALL_INDEXES WHERE TABLESPACE_NAME='TS_HR';
For lob segments:
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE LOB (' || COLUMN_NAME || ')' || ' STORE AS ( TABLESPACE TS_HR_NEW);' FROM DBA_LOBS WHERE TABLESPACE_NAME = 'TS_HR'
Rebuild unusable index for other schema's indexes:
SQL>
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM ALL_INDEXES WHERE STATUS='UNUSABLE' ;
Gather statistics of tables:
SQL>
SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS(' || '''' || OWNER || '''' || ',' || '''' || TABLE_NAME || ''');' FROM ALL_TABLES WHERE TABLESPACE_NAME='TS_HR_NEW';
Check size of data files
SQL>
SELECT OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
PARTITION_NAME,
ROUND (BYTES / (1024 * 1024), 2) SIZE_MB,
TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE',
'TABLE PARTITION',
'TABLE SUBPARTITION',
'INDEX',
'INDEX PARTITION',
'INDEX SUBPARTITION',
'TEMPORARY',
'LOBINDEX',
'LOBSEGMENT',
'LOB PARTITION')
AND TABLESPACE_NAME LIKE '%TS_HR%'
--AND SEGMENT_NAME LIKE 'P2010201%'
--AND partition_name LIKE 'P20100201%'
--AND segment_type = 'TABLE'
--AND OWNER = 'HR'
AND ROUND (BYTES / (1024 * 1024), 2) > 10000
ORDER BY BYTES DESC;
Ref:
https://ozsoyler.blogspot.com.tr/2017/08/eg-table-shrink-usage.html
SQL>
SELECT * FROM DBA_TABLESPACES WHERE CONTENTS = 'PERMANENT';
Delete all records on tables if needs
SQL>
SELECT 'TRUNCATE TABLE ' || OWNER || '.' || TABLE_NAME || ';' FROM ALL_TABLES WHERE OWNER = 'HR';
Before movement operation, enable row movement feature
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ENABLE ROW MOVEMENT;' FROM ALL_TABLES WHERE TABLESPACE_NAME = 'HR';
Shrink tables
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' SHRINK SPACE;' from ALL_TABLES WHERE TABLESPACE_NAME = 'HR';
If ORA-10631 error occurs;
ALTER TABLE HR.EMPLOYEES_NEW SHRINK SPACE
Error at line 99
ORA-10631: SHRINK clause should not be specified for this object
Solution:
If FUNCTION-BASED NORMAL index stores on related table, this error occurs
1- Drop the function-based index.
2- Shrink the table.
3- Recreate the index again on the table.
Define a fresh tablespace
SQL>
CREATE TABLESPACE HR_NEW DATAFILE
'/oradata/XE/data.dbf' SIZE 1G AUTOEXTEND ON NEXT 8K MAXSIZE 5G,
'/oradata/XE/data01.dbf'' SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE 50G,
'/oradata/XE/data02.dbf SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE 50G
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
If need, move related tablespace to a fresh tablespace
For tables:
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE TS_HR_NEW;' from ALL_TABLES WHERE TABLESPACE_NAME = 'TS_HR';
For indexes:
SQL>
SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE TS_HR_NEW;' FROM ALL_INDEXES WHERE TABLESPACE_NAME='TS_HR';
For lob segments:
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE LOB (' || COLUMN_NAME || ')' || ' STORE AS ( TABLESPACE TS_HR_NEW);' FROM DBA_LOBS WHERE TABLESPACE_NAME = 'TS_HR'
Rebuild unusable index for other schema's indexes:
SQL>
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM ALL_INDEXES WHERE STATUS='UNUSABLE' ;
Gather statistics of tables:
SQL>
SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS(' || '''' || OWNER || '''' || ',' || '''' || TABLE_NAME || ''');' FROM ALL_TABLES WHERE TABLESPACE_NAME='TS_HR_NEW';
Check size of data files
SQL>
SELECT OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
PARTITION_NAME,
ROUND (BYTES / (1024 * 1024), 2) SIZE_MB,
TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE',
'TABLE PARTITION',
'TABLE SUBPARTITION',
'INDEX',
'INDEX PARTITION',
'INDEX SUBPARTITION',
'TEMPORARY',
'LOBINDEX',
'LOBSEGMENT',
'LOB PARTITION')
AND TABLESPACE_NAME LIKE '%TS_HR%'
--AND SEGMENT_NAME LIKE 'P2010201%'
--AND partition_name LIKE 'P20100201%'
--AND segment_type = 'TABLE'
--AND OWNER = 'HR'
AND ROUND (BYTES / (1024 * 1024), 2) > 10000
ORDER BY BYTES DESC;
Ref:
https://ozsoyler.blogspot.com.tr/2017/08/eg-table-shrink-usage.html
Labels:
index,
ORA-10631,
oracle,
shrink,
tablespace move
Monday 18 December 2017
char vs byte
e.g.
SQL>
CREATE TABLE exp_table
(
col1 CHAR (1 CHAR),
col2 CHAR (1 BYTE),
col3 CHAR (1)
)
-> col1 can store 4 byte at most (if db charset is AL32UTF8)
-> col1 can store 3 byte at most (if db charset is UTF8)
-> col3 stores string according to NLS_LENGTH_SEMANTICS parameter
SQL>
SELECT * FROM v$nls_parameters WHERE parameter = 'NLS_LENGTH_SEMANTICS';
Check db charset
SQL>
SELECT * FROM nls_database_parameters WHERE parameter like '%SET%';
-> NLS_CHARACTERSET set for CHAR, VARCHAR2 and CLOB data types
-> NLS_NCHAR_CHARACTERSET set for NCHAR, NVARCHAR2 and NCLOB (N, National) data types
e.g.
NLS_NCHAR_CHARACTERSET UTF8
NLS_CHARACTERSET WE8ISO8859P9
Ref: https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm
SQL>
CREATE TABLE exp_table
(
col1 CHAR (1 CHAR),
col2 CHAR (1 BYTE),
col3 CHAR (1)
)
-> col1 can store 4 byte at most (if db charset is AL32UTF8)
-> col1 can store 3 byte at most (if db charset is UTF8)
-> col3 stores string according to NLS_LENGTH_SEMANTICS parameter
SQL>
SELECT * FROM v$nls_parameters WHERE parameter = 'NLS_LENGTH_SEMANTICS';
Check db charset
SQL>
SELECT * FROM nls_database_parameters WHERE parameter like '%SET%';
-> NLS_CHARACTERSET set for CHAR, VARCHAR2 and CLOB data types
-> NLS_NCHAR_CHARACTERSET set for NCHAR, NVARCHAR2 and NCLOB (N, National) data types
e.g.
NLS_NCHAR_CHARACTERSET UTF8
NLS_CHARACTERSET WE8ISO8859P9
Ref: https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm
Labels:
byte,
char,
nls_characterset,
nls_length_semantics,
nls_nchar_characterset,
oracle
Friday 8 December 2017
How to install/use rlwrap?
Installation
e.g.
# rpm -ivh rlwrap-0.42-1.el6.x86_64.rpm
Usage
Add alias to .bash_profile file (or .bashrc, etc.)
alias sqlplus='rlwrap sqlplus'
$ sqlplus hr/hr
SQL> select 1 from dual;
Key trick is that while in sqlplus command line, use up or down keys to navigate sql history.
e.g.
# rpm -ivh rlwrap-0.42-1.el6.x86_64.rpm
Usage
Add alias to .bash_profile file (or .bashrc, etc.)
alias sqlplus='rlwrap sqlplus'
$ sqlplus hr/hr
SQL> select 1 from dual;
Key trick is that while in sqlplus command line, use up or down keys to navigate sql history.
Wednesday 6 December 2017
How to define "Code Templates" in SQL Developer?
e.g.
SQL DEVELOPER -> TOOLS -> PREFERENCES -> DATABASE -> SQL EDITOR CODE TEMPLATES -> Click "ADD TEMPLATE" button -> Input Id as "sl", Template as "select * from " -> OK
Usage:
Press "sl" and press "ctrl+space"
Output:
select * from
SQL DEVELOPER -> TOOLS -> PREFERENCES -> DATABASE -> SQL EDITOR CODE TEMPLATES -> Click "ADD TEMPLATE" button -> Input Id as "sl", Template as "select * from " -> OK
Usage:
Press "sl" and press "ctrl+space"
Output:
select * from
Labels:
Code Templates,
oracle,
oracle sql developer
Tuesday 5 December 2017
ORA-00900, ORA-00911 solution
SQL
DECLARE
sql_text VARCHAR (1000);
BEGIN
sql_text := 'exec P_COMPILE_INVALID_OBJECTS()';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;
Error
ORA-00900: invalid SQL statement
ORA-06512: at line 8
Solution
Use 'CALL' instead of 'EXEC' keyword.
DECLARE
sql_text VARCHAR (100);
BEGIN
sql_text := 'call P_COMPILE_INVALID_OBJECTS()';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;
SQL
DECLARE
sql_text VARCHAR (1000);
BEGIN
sql_text := 'select sysdate from dual;';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;
Error
ORA-00911: invalid character
ORA-06512: at line 8
Solution
Do not put comma (;) at the end of the sql statement.
DECLARE
sql_text VARCHAR (100);
BEGIN
sql_text := 'select sysdate from dual';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;
DECLARE
sql_text VARCHAR (1000);
BEGIN
sql_text := 'exec P_COMPILE_INVALID_OBJECTS()';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;
Error
ORA-00900: invalid SQL statement
ORA-06512: at line 8
Solution
Use 'CALL' instead of 'EXEC' keyword.
DECLARE
sql_text VARCHAR (100);
BEGIN
sql_text := 'call P_COMPILE_INVALID_OBJECTS()';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;
SQL
DECLARE
sql_text VARCHAR (1000);
BEGIN
sql_text := 'select sysdate from dual;';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;
Error
ORA-00911: invalid character
ORA-06512: at line 8
Solution
Do not put comma (;) at the end of the sql statement.
DECLARE
sql_text VARCHAR (100);
BEGIN
sql_text := 'select sysdate from dual';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;
Wednesday 8 November 2017
How to export/set ORACLE_SID?
Linux
export variable
$ export ORACLE_SID=XE
check variable
$ echo $ORACLE_SID
Windows
set variable
> set ORACLE_SID=XE
check variable
> echo %ORACLE_SID%
export variable
$ export ORACLE_SID=XE
check variable
$ echo $ORACLE_SID
Windows
set variable
> set ORACLE_SID=XE
check variable
> echo %ORACLE_SID%
Labels:
echo,
linux,
oracle,
ORACLE_SID,
windows
Friday 3 November 2017
ORA-01950 Solution
Action
SQL> CREATE TABLE HR2.EMPLOYEES_NEW AS SELECT * FROM HR.EMPLOYEES;
Error
ORA-01950: no privileges on tablespace 'USERS'
Solution I
SQL> ALTER USER HR2 QUOTA 1024M ON USERS:
Solution II
SQL> GRANT UNLIMITED TABLESPACE TO HR2;
SQL> CREATE TABLE HR2.EMPLOYEES_NEW AS SELECT * FROM HR.EMPLOYEES;
Error
ORA-01950: no privileges on tablespace 'USERS'
Solution I
SQL> ALTER USER HR2 QUOTA 1024M ON USERS:
Solution II
SQL> GRANT UNLIMITED TABLESPACE TO HR2;
Wednesday 25 October 2017
ORA-02266 solution
Action
A table has a PK (Primary Key)
B table has a FK (Foreign Key) and referenced to A table's PK
-> User tries to truncate table A
Error
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Solution I
Disable B table's FK
SQL> alter table HR.B disable constraint FK_B;
-- e.g. schema HR
Solution II
First truncate table B
SQL> truncate table B;
Then truncate table A
SQL> truncate table A;
A table has a PK (Primary Key)
B table has a FK (Foreign Key) and referenced to A table's PK
-> User tries to truncate table A
Error
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Solution I
Disable B table's FK
SQL> alter table HR.B disable constraint FK_B;
-- e.g. schema HR
Solution II
First truncate table B
SQL> truncate table B;
Then truncate table A
SQL> truncate table A;
Labels:
foreign key,
ORA-02266,
oracle,
primary key
How to disable/enable all triggers for tables?
Disable triggers for a table
SQL> alter table HR.TRG_EMPLOYEES disable all triggers;
Enable triggers for a table
SQL> alter table HR.TRG_EMPLOYEES enable all triggers;
Disable triggers belong to a schema (e.g. HR)
SQL>
DECLARE
STR_EXEC VARCHAR2 (1000);
BEGIN
FOR cur_node IN (SELECT owner, table_name
FROM all_triggers
WHERE owner = 'HR')
LOOP
BEGIN
str_exec :=
'alter table '
|| cur_node.owner
|| '.'
|| cur_node.table_name
|| ' DISABLE ALL TRIGGERS';
DBMS_OUTPUT.put_line (str_exec);
EXECUTE IMMEDIATE str_exec;
END;
END LOOP;
END;
/
Enable triggers belong to a schema (e.g. HR)
SQL>
DECLARE
STR_EXEC VARCHAR2 (1000);
BEGIN
FOR cur_node IN (SELECT owner, table_name
FROM all_triggers
WHERE owner = 'HR')
LOOP
BEGIN
str_exec :=
'alter table '
|| cur_node.owner
|| '.'
|| cur_node.table_name
|| ' ENABLE ALL TRIGGERS';
DBMS_OUTPUT.put_line (str_exec);
EXECUTE IMMEDIATE str_exec;
END;
END LOOP;
END;
/
SQL> alter table HR.TRG_EMPLOYEES disable all triggers;
Enable triggers for a table
SQL> alter table HR.TRG_EMPLOYEES enable all triggers;
Disable triggers belong to a schema (e.g. HR)
SQL>
DECLARE
STR_EXEC VARCHAR2 (1000);
BEGIN
FOR cur_node IN (SELECT owner, table_name
FROM all_triggers
WHERE owner = 'HR')
LOOP
BEGIN
str_exec :=
'alter table '
|| cur_node.owner
|| '.'
|| cur_node.table_name
|| ' DISABLE ALL TRIGGERS';
DBMS_OUTPUT.put_line (str_exec);
EXECUTE IMMEDIATE str_exec;
END;
END LOOP;
END;
/
Enable triggers belong to a schema (e.g. HR)
SQL>
DECLARE
STR_EXEC VARCHAR2 (1000);
BEGIN
FOR cur_node IN (SELECT owner, table_name
FROM all_triggers
WHERE owner = 'HR')
LOOP
BEGIN
str_exec :=
'alter table '
|| cur_node.owner
|| '.'
|| cur_node.table_name
|| ' ENABLE ALL TRIGGERS';
DBMS_OUTPUT.put_line (str_exec);
EXECUTE IMMEDIATE str_exec;
END;
END LOOP;
END;
/
Tuesday 5 September 2017
How to enable/disable DBA auto task jobs?
Jobs
Automatic Optimizer Statistics Collection -> Gathers stale or missing statistics
Automatic Segment Advisor -> Identifies segments that reorganized to save space
Automatic SQL Tuning Advisor -> Tune high load SQL
Check auto tasks
SQL> SELECT client_name, status FROM dba_autotask_client;
To disable all auto task jobs
SQL> EXEC DBMS_AUTO_TASK_ADMIN.disable;
To enable all auto task jobs
SQL> EXEC DBMS_AUTO_TASK_ADMIN.enable;
To disable a spesific task
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);
To enable a spesific task
SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);
Ref: https://smarttechways.com/2015/09/03/disable-and-enable-auto-task-job-for-11g-and-12c-version-in-oracle/
Automatic Optimizer Statistics Collection -> Gathers stale or missing statistics
Automatic Segment Advisor -> Identifies segments that reorganized to save space
Automatic SQL Tuning Advisor -> Tune high load SQL
Check auto tasks
SQL> SELECT client_name, status FROM dba_autotask_client;
To disable all auto task jobs
SQL> EXEC DBMS_AUTO_TASK_ADMIN.disable;
To enable all auto task jobs
SQL> EXEC DBMS_AUTO_TASK_ADMIN.enable;
To disable a spesific task
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);
To enable a spesific task
SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);
Ref: https://smarttechways.com/2015/09/03/disable-and-enable-auto-task-job-for-11g-and-12c-version-in-oracle/
Labels:
dba auto task,
oracle
Tuesday 29 August 2017
e.g. shrink table usage
Steps:
SQL>
exec dbms_stats.gather_table_stats('HR','EMPLOYEES');
-- Gathering table statistics
SQL>
SELECT owner,
table_name,
ROUND ( (num_rows * avg_row_len) / (1024 * 1024)) MB
FROM dba_tables
WHERE table_name = 'EMPLOYEES';
-- Check table actual size
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Check table current, actual, wasted size
SQL>
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
-- Enable feature before the operation
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
-- Online operation without HWM (High Water Mark) fix
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
-- Table will be locked for any DML commands
SQL>
ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
-- Disable feature after the operation
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Lastly, Check table current, actual, wasted size again
Ref: http://select-star-from.blogspot.com.tr/2013/09/how-to-check-table-fragmentation-in.html
SQL>
exec dbms_stats.gather_table_stats('HR','EMPLOYEES');
-- Gathering table statistics
SQL>
SELECT owner,
table_name,
ROUND ( (num_rows * avg_row_len) / (1024 * 1024)) MB
FROM dba_tables
WHERE table_name = 'EMPLOYEES';
-- Check table actual size
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Check table current, actual, wasted size
SQL>
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
-- Enable feature before the operation
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
-- Online operation without HWM (High Water Mark) fix
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
-- Table will be locked for any DML commands
SQL>
ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
-- Disable feature after the operation
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Lastly, Check table current, actual, wasted size again
Ref: http://select-star-from.blogspot.com.tr/2013/09/how-to-check-table-fragmentation-in.html
Labels:
dbms_stats,
oracle,
shrink
Monday 28 August 2017
DBMS_JOBS vs DBMS_SCHEDULER
-> DBMS_SCHEDULER introduced in 10g releases so before this, we were able to use only DBMS_JOBS for scheduler jobs.
e.g. DBMS_JOBS
SQL>
VARIABLE job_id NUMBER;
BEGIN
DBMS_JOB.submit (
job => :job_id,
what => 'BEGIN P_KILL_ALL_INACTIVE_SESSIONS; END;',
next_date => SYSDATE,
interval => 'SYSDATE + 1 /* 1 Day */');
COMMIT;
END;
/
PRINT job_id
e.g. DBMS_SCHEDULER
SQL>
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'example_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN P_KILL_ALL_INACTIVE_SESSIONS; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'SYSTIMESTAMP + 1 /* 1 Day */');
END;
/
Ref for example: https://ozsoyler.blogspot.com.tr/2017/02/how-to-kill-all-inactive-sessions-with.html
e.g. DBMS_JOBS
SQL>
VARIABLE job_id NUMBER;
BEGIN
DBMS_JOB.submit (
job => :job_id,
what => 'BEGIN P_KILL_ALL_INACTIVE_SESSIONS; END;',
next_date => SYSDATE,
interval => 'SYSDATE + 1 /* 1 Day */');
COMMIT;
END;
/
PRINT job_id
e.g. DBMS_SCHEDULER
SQL>
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'example_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN P_KILL_ALL_INACTIVE_SESSIONS; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'SYSTIMESTAMP + 1 /* 1 Day */');
END;
/
Ref for example: https://ozsoyler.blogspot.com.tr/2017/02/how-to-kill-all-inactive-sessions-with.html
Labels:
dbms_jobs,
dbms_scheduler,
oracle
Tuesday 22 August 2017
How to monitor TEMP tablespace resource usage?
-> Check Total TEMP usage:
SQL>
SELECT B.TOTAL_MB,
B.TOTAL_MB - ROUND (A.USED_BLOCKS * 8 / 1024) CURRENT_FREE_MB,
ROUND (USED_BLOCKS * 8 / 1024) CURRENT_USED_MB,
ROUND (MAX_USED_BLOCKS * 8 / 1024) MAX_USED_MB
FROM V$SORT_SEGMENT A,
(SELECT ROUND (SUM (BYTES) / 1024 / 1024) TOTAL_MB FROM DBA_TEMP_FILES) B;
-> Check Total TEMP usage according to database blocks:
SQL>
SELECT DISTINCT S.SID,
S.USERNAME,
U.TABLESPACE,
S.SQL_HASH_VALUE || '/' || U.SQLHASH HASH_VALUE,
U.SEGTYPE,
U.CONTENTS,
U.BLOCKS
FROM V$SESSION S, V$TEMPSEG_USAGE U
WHERE S.SADDR = U.SESSION_ADDR
ORDER BY U.BLOCKS DESC;
-> Find problematic SQL for TEMP
SQL>
SELECT
S.SID
,S.CLIENT_INFO
,S.MACHINE
,S.PROGRAM
,S.TYPE
,S.LOGON_TIME
,S.OSUSER
,SQ.SORTS
,SQ.DISK_READS
,SQ.BUFFER_GETS
,SQ.ROWS_PROCESSED
,SQ.SQLTYPE
,SQ.SQL_TEXT
FROM GV$SESSION S
, GV$SQL SQ
WHERE S.SQL_HASH_VALUE = SQ.HASH_VALUE
--AND s.inst_id= 1
AND S.SID = 555
AND SQ.INST_ID= S.INST_ID;
SQL>
SELECT B.TOTAL_MB,
B.TOTAL_MB - ROUND (A.USED_BLOCKS * 8 / 1024) CURRENT_FREE_MB,
ROUND (USED_BLOCKS * 8 / 1024) CURRENT_USED_MB,
ROUND (MAX_USED_BLOCKS * 8 / 1024) MAX_USED_MB
FROM V$SORT_SEGMENT A,
(SELECT ROUND (SUM (BYTES) / 1024 / 1024) TOTAL_MB FROM DBA_TEMP_FILES) B;
-> Check Total TEMP usage according to database blocks:
SQL>
SELECT DISTINCT S.SID,
S.USERNAME,
U.TABLESPACE,
S.SQL_HASH_VALUE || '/' || U.SQLHASH HASH_VALUE,
U.SEGTYPE,
U.CONTENTS,
U.BLOCKS
FROM V$SESSION S, V$TEMPSEG_USAGE U
WHERE S.SADDR = U.SESSION_ADDR
ORDER BY U.BLOCKS DESC;
-> Find problematic SQL for TEMP
SQL>
SELECT
S.SID
,S.CLIENT_INFO
,S.MACHINE
,S.PROGRAM
,S.TYPE
,S.LOGON_TIME
,S.OSUSER
,SQ.SORTS
,SQ.DISK_READS
,SQ.BUFFER_GETS
,SQ.ROWS_PROCESSED
,SQ.SQLTYPE
,SQ.SQL_TEXT
FROM GV$SESSION S
, GV$SQL SQ
WHERE S.SQL_HASH_VALUE = SQ.HASH_VALUE
--AND s.inst_id= 1
AND S.SID = 555
AND SQ.INST_ID= S.INST_ID;
Labels:
oracle,
TEMP,
temporary tablespace
Tuesday 25 July 2017
e.g. changing the DBID and Database Name
e.g.
Backup database
RMAN> backup database
Mount database
SQL> shu immediate;
SQL> startup mount;
Change DBID
$ nid TARGET=SYS
or
Change both DBID and DBNAME
$ nid TARGET=SYS DBNAME=new_xe
Change database name from parameter file (init.ora)
*.db_name='NEW_XE'
Mount database again
SQL> startup mount;
Open database
SQL> alter database open resetlogs;
Ref: https://docs.oracle.com/database/121/SUTIL/GUID-05B4733C-9EAA-438F-A2A4-4E84EB1E1E65.htm#SUTIL1544s
Backup database
RMAN> backup database
Mount database
SQL> shu immediate;
SQL> startup mount;
Change DBID
$ nid TARGET=SYS
or
Change both DBID and DBNAME
$ nid TARGET=SYS DBNAME=new_xe
Change database name from parameter file (init.ora)
*.db_name='NEW_XE'
Mount database again
SQL> startup mount;
Open database
SQL> alter database open resetlogs;
Ref: https://docs.oracle.com/database/121/SUTIL/GUID-05B4733C-9EAA-438F-A2A4-4E84EB1E1E65.htm#SUTIL1544s
Labels:
database_name,
dbid,
nid,
oracle
Monday 17 July 2017
SYS_CONTEXT function parameters and examples
Active parameters
ACTION, AUDITED_CURSORID, AUTHENTICATED_IDENTITY, AUTHENTICATION_DATA, AUTHENTICATION_METHOD, BG_JOB_ID, CLIENT_IDENTIFIER, CLIENT_INFO, CURRENT_BIND, CURRENT_SCHEMA, CURRENT_SCHEMAID, CURRENT_SQL, CURRENT_SQLn, CURRENT_SQL_LENGTH, DB_DOMAIN, DB_NAME, DB_UNIQUE_NAME, ENTRYID, ENTERPRISE_IDENTITY, FG_JOB_ID, GLOBAL_CONTEXT_MEMORY, GLOBAL_UID, HOST, IDENTIFICATION_TYPE, INSTANCE, INSTANCE_NAME, IP_ADDRESS, ISDBA, LANG, LANGUAGE, MODULE, NETWORK_PROTOCOL, NLS_CALENDAR, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, NLS_TERRITORY, OS_USER, POLICY_INVOKER, PROXY_ENTERPRISE_IDENTITY, PROXY_GLOBAL_UID, PROXY_USER, SERVER_HOST, SERVICE_NAME, SESSION_USER, SESSION_USERID, SESSIONID, SID, STATEMENTID, TERMINAL
e.g.
SQL> select SYS_CONTEXT ('USERENV', 'SESSION_USER') from dual;
-- HR
e.g.
SQL> select SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') from dual;
-- HR
Deprecated parameters
AUTHENTICATION_TYPE, CURRENT_USER, CURRENT_USERID, EXTERNAL_NAME
Extra for sys_context
Link
Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm
ACTION, AUDITED_CURSORID, AUTHENTICATED_IDENTITY, AUTHENTICATION_DATA, AUTHENTICATION_METHOD, BG_JOB_ID, CLIENT_IDENTIFIER, CLIENT_INFO, CURRENT_BIND, CURRENT_SCHEMA, CURRENT_SCHEMAID, CURRENT_SQL, CURRENT_SQLn, CURRENT_SQL_LENGTH, DB_DOMAIN, DB_NAME, DB_UNIQUE_NAME, ENTRYID, ENTERPRISE_IDENTITY, FG_JOB_ID, GLOBAL_CONTEXT_MEMORY, GLOBAL_UID, HOST, IDENTIFICATION_TYPE, INSTANCE, INSTANCE_NAME, IP_ADDRESS, ISDBA, LANG, LANGUAGE, MODULE, NETWORK_PROTOCOL, NLS_CALENDAR, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, NLS_TERRITORY, OS_USER, POLICY_INVOKER, PROXY_ENTERPRISE_IDENTITY, PROXY_GLOBAL_UID, PROXY_USER, SERVER_HOST, SERVICE_NAME, SESSION_USER, SESSION_USERID, SESSIONID, SID, STATEMENTID, TERMINAL
e.g.
SQL> select SYS_CONTEXT ('USERENV', 'SESSION_USER') from dual;
-- HR
e.g.
SQL> select SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') from dual;
-- HR
Deprecated parameters
AUTHENTICATION_TYPE, CURRENT_USER, CURRENT_USERID, EXTERNAL_NAME
Extra for sys_context
Link
Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm
Labels:
oracle,
sys_context
Friday 14 July 2017
Quiesce restricted mode vs Restricted mode
Differences;
* Quiesce restricted option is less raugh than restricted option and they are quite similar.
* In quiesce restricted option, active session allows to continue until it become inactive.
* In restricted session, only new users can login who has "restrict session" privileges, but in quiesce restricted session, system blocks new non-dba users.
commands;
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
check the state;
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
-- normal, quiescing, quiesced
SQL> ALTER SYSTEM UNQUIESCE;
check the state;
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
-- normal, quiescing, quiesced
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Ref: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:902149038276
* Quiesce restricted option is less raugh than restricted option and they are quite similar.
* In quiesce restricted option, active session allows to continue until it become inactive.
* In restricted session, only new users can login who has "restrict session" privileges, but in quiesce restricted session, system blocks new non-dba users.
commands;
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
check the state;
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
-- normal, quiescing, quiesced
SQL> ALTER SYSTEM UNQUIESCE;
check the state;
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
-- normal, quiescing, quiesced
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Ref: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:902149038276
Labels:
oracle,
quiesce restricted,
restricted
Subscribe to:
Posts (Atom)