Skip to main content

Oracle Daily SQL

Enable System-wide tracing:
alter system set events '10046 trace name context forever,level 12';
Disabled in all sessions:
alter system set events '10046 trace name context off';
Session Tracing:
alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
crsctl for Oracle RAC
./crsctl start cluster
./crsctl check crs
Make column to CLOB
ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME LONG);
ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME CLOB);
RENAME <TABLE_NAME> TO <NEW_TABLE_NAME>;
Rename column, change data type, drop column:
ALTER TABLE TABLE_NAME RENAME COLUMN C:OLUMN_NAME TO COLUMN_NAME1;
ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME VARCHAR2(10));
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;
Create Primary Key:
ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME PRIMARY KEY (COLUMN1,COLUMN2,COLUMN3,COLUMN4) USING INDEX;
Create Unique Index, Rename Index:
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME (COLUMN1,COLUMN2,COLUMN3,COLUMN4);
ALTER INDEX <INDEX_NAME> RENAME TO <NEW_INDEX>;
Privilege and Role management:
SELECT * FROM ROLE_SYS_PRIVS;
Create synonym:
create synonym <SYNONYM_NAME>for  <SCHEMA_NAME>.<TABLE_NAME>;
Add Data file:
select FILE_NAME || '    ' || TABLESPACE_NAME from dba_temp_files;

ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '/u01/data_file/<file_name>.dbf' SIZE 5G AUTOEXTEND ON;

Find out Tablespace size
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b where a.tablespace_name(+)=b.tablespace_name;

Generate script for permission for TABLE in schema:
SELECT 'GRANT SELECT,INSERT,UPDATE,DELETE ON <SCHEMA_NAME>.' || OBJECT_NAME || ' ' ||  'TO <USER_NAME>;' FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE' ORDER BY OBJECT_NAME;
Explian plan for sql_id:
set pagesize 0
set linesize 5000
spool plan_q1.log
select * from table(dbms_xplan.display_awr('sql_id'));
spool off
Kill session in RAC environment
SELECT USERNAME, SQL_ID, SID, SERIAL#, INST_ID FROM GV$SESSION WHERE USERNAME LIKE 'I%';
ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>,@<INSTANCE_NO>' IMMEDIATE;
List compression for the table partition:
SET PAGESIZE 0
COLUMN TABLE_NAME FORMAT A30
COLUMN COMPRESSION FORMAT A30
SELECT DISTINCT TABLE_NAME, COMPRESSION FROM DBA_TAB_PARTITIONS WHERE COMPRESSION IN ('DISABLED') AND TABLE_OWNER='<TABLE_OWNER>';
SELECT DISTINCT TABLE_NAME, COMPRESSION FROM DBA_TAB_PARTITIONS WHERE COMPRESSION IN ('ENABLED') AND TABLE_OWNER='<TABLE_OWNER>';

List partitoned indexes
SET PAGESIZE 0
COLUMN INDEX_NAME FORMAT A30
COLUMN PARTITION_NAME FORMAT A30
SELECT INDEX_NAME, PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER='<TABLE_OWNER>' ORDER BY INDEX_NAME;
List index name partitioning type for tables:
SET PAGESIZE 0
SET LINESIZE 1000
COLUMN TABLE_NAME FORMAT A30
COLUMN INDEX_NAME FORMAT A30
COLUMN PARTITIONING_TYPE FORMAT A30
SELECT TABLE_NAME, INDEX_NAME, PARTITIONING_TYPE FROM ALL_PART_INDEXES WHERE OWNER='<TABLE_OWNER>';

SET PAGESIZE 0
SET LINESIZE 1000
COLUMN TABLE_NAME FORMAT A50
COLUMN COMPRESSOIN FORMAT A20
COLUMN COMPRESS_FOR FORMAT A10

SELECT DISTINCT TABLE_NAME, COMPRESSION,COMPRESS_FOR FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='<TABLE_OWNER>' AND COMPRESSION='ENABLED'

List all tables and degree of parallelism:
set pagesize 0
spool table_degree.txt
COLUMN TABLE_NAME FORMAT A30
COLUMN DEGREE FORMAT A10
SELECT TABLE_NAME, DEGREE

FROM DBA_TABLES
WHERE TABLE_NAME IN (SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER='<TABLE_OWNER>') ORDER BY DEGREE DESC;
spool off


List all indexes and degree of parallel:
set pagesize 0
spool index_degree.txt
COLUMN INDEX_NAME FORMAT A30
COLUMN DEGREE FORMAT A10
SELECT INDEX_NAME, DEGREE FROM DBA_INDEXES WHERE INDEX_NAME IN (SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='INDEX' AND OWNER='<TABLE_OWNER>') ORDER BY DEGREE DESC;
spool off

 
gen_datafile.sql
set head off
pages 0
echo off
verify off
feed off
set linesize 500
spool ren_datafiles.lst
select 'SET NEWNAME FOR DATAFILE ' || FILE# || ' TO ''' || '/u02/oradata/&1/' || substr(name,instr(name,'/',-1)+1) || ''';'
from v$datafile;
spool off
exit;

gen_logfiles.sql
set head off
pages 0
echo off
feed off
spool re_logfiles.lst
SELECT 'SQL "ALTER DATABASE RENAME FILE '''''||  MEMBER ||'''''' ||chr(10)||'to ''''' || member || '''''" ;'
FROM V$LOGFILE;
exit

Check cell smart scan is enables for ASM disk or not:
 Make sure to connect ASM instance, if you connect to DB instance you won't find value
SELECT dg.name AS diskgroup, SUBSTR(a.name,1,24) AS name, SUBSTR(a.value,1,24) AS value
FROM V$ASM_DISKGROUP dg,
V$ASM_ATTRIBUTE a
WHERE dg.group_number = a.group_number and a.NAME = 'cell.smart_scan_capable';

Monitor Lock for Oracle:

select * from v$lock where block=1 ;

If a session holds a lock that's blocking another session, BLOCK=1.
select * from v$lock where REQUEST > 0

The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.

select l1.sid, ' IS BLOCKING ', l2.sid from v$lock l1, v$lock l2 where
l1.block =1 and
l2.request > 0 and
l1.id1=l2.id1 and
l1.id2=l2.id2

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2 where
s1.sid=l1.sid and
s2.sid=l2.sid and
l1.BLOCK=1 and
l2.request > 0 and
l1.id1 = l2.id1 and
l2.id2 = l2.id2 ;


select  (select username from v$session where sid=a.sid) blocker, a.sid, ' is blocking ',
   (select username from v$session where sid=b.sid) blockee, b.sid
from
   v$lock a,
   v$lock b
where
   a.block = 1 and
   b.request > 0 and
   a.id1 = b.id1 and
   a.id2 = b.id2;


Select SQL_TEXT from DBA_HIST_SQLTEXT

SET PAGESIZE 0
set long 2000000000
SPOOL sqltext.txt
SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT;
spool off

 
table last time analyzed:
select owner,table_name,last_analyzed, global_stats
from dba_tables
where table_name in ('<table_name>') and
owner = '<owner_name>'
partition last time analyzed:
select table_owner, table_name, partition_name, last_analyzed, global_stats
from dba_tab_partitions
where table_owner in ('<owner_name>') and
table_name in ('<table_name>')
order by table_owner,table_name, partition_name
index last time analyzed:
select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner in ('<owner_name>') and
index_name in ('index_name')
order by owner, index_name
partitioned index last time analyzed:
select index_owner, index_name, partition_name, last_analyzed, global_stats
from dba_ind_partitions
where index_owner in ('<owner_name>') and
index_name in ('<index_name>')
order by index_owner, index_name, partition_name
DML against table:
SET LINESIZE 500
SET PAGESIZE 0
COLUMN TABLE_NAME FORMAT A30
SELECT TABLE_NAME, INSERTS, UPDATES,DELETES,TRUNCATED, TIMESTAMP FROM ALL_TAB_MODIFICATIONS WHERE TABLE_OWNER IN ('<TABLE_OWNER>') ORDER BY TIMESTAMP DESC ;

Check incremental statistics is implemented or not:
SELECT dbms_stats.get_prefs('INCREMENTAL','<schema_name>','<table_name>') "INCREMENTAL" FROM   dual;
Enable incremental statistics:
exec dbms_stats.set_table_prefs('<schema_name>','<table_name>','INCREMENTAL','TRUE');
Implement incremental statistics at partition level:
exec dbms_stats.gather_table_stats('<schema_name>','<table_name>',partname=>'<partition_name>',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity=>'PARTITION');


Granularity of statistics to collect (only pertinent if the table is partitioned).
DEFAULT: Gather global- and partition-level statistics.
SUBPARTITION: Gather subpartition-level statistics.
PARTITION: Gather partition-level statistics.
GLOBAL: Gather global statistics.
ALL: Gather all (subpartition, partition, and global) statistics.

List under score parameter which has TRUE value:
column "hidden parameter" format A50
column "value" format A20
set pagesize 0
select ksppinm as "hidden parameter", ksppstvl as "value"  from x$ksppi join x$ksppcv using (indx) where ksppstvl='TRUE';

List specific under score parameter which has TRUE value:
column "hidden parameter" format A50
column "value" format A20
select ksppinm as "hidden parameter", ksppstvl as "value"  from x$ksppi join x$ksppcv using (indx) where ksppinm like '\_%' escape '\' and ksppinm like '_db_fast_obj_ckpt'  order by ksppinm;


Ref.:

https://www.toadworld.com/platforms/oracle/w/wiki/11434.sophisticated-incremental-statistics-gathering-feature-in-12c
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
Explore scripts at Guy Harrison - http://guyharrison.squarespace.com/opsgsamples/
 

Comments

Popular posts from this blog

MySQL InnoDB cluster troubleshooting | commands

Cluster Validation: select * from performance_schema.replication_group_members; All members should be online. select instance_name, mysql_server_uuid, addresses from  mysql_innodb_cluster_metadata.instances; All instances should return same value for mysql_server_uuid SELECT @@GTID_EXECUTED; All nodes should return same value Frequently use commands: mysql> SET SQL_LOG_BIN = 0;  mysql> stop group_replication; mysql> set global super_read_only=0; mysql> drop database mysql_innodb_cluster_metadata; mysql> RESET MASTER; mysql> RESET SLAVE ALL; JS > var cluster = dba.getCluster() JS > var cluster = dba.getCluster("<Cluster_name>") JS > var cluster = dba.createCluster('name') JS > cluster.removeInstance('root@<IP_Address>:<Port_No>',{force: true}) JS > cluster.addInstance('root@<IP add>,:<port>') JS > cluster.addInstance('root@ <IP add>,:<port> ') JS > dba.getC...

MySQL slave Error_code: 1032 | MySQL slave drift | HA_ERR_KEY_NOT_FOUND

MySQL slave Error_code: 1032 | MySQL slave drift: With several MySQL, instance with master slave replication, I have one analytics MySQL, environment which is larger in terabytes, compared to other MySQL instances in the environment. Other MySQL instances with terabytes of data are running fine master, slave replication. But this analytics environment get started generating slave Error_code :1032. mysql> show slave status; Near relay log: Error_code: 1032; Can't find record in '<table_name>', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log <name>-bin.000047, end_log_pos 5255306 Near master section: Could not execute Update_rows event on table <db_name>.<table_name>; Can't find record in '<table_name>', Error_code: 1032; Can't find record in '<table_name>', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log <name>-bin.000047, end_l...

InnoDB cluster Remove Instance Force | Add InnoDB instance

InnoDB cluster environment UUID is different on node: To fix it stop group replication, remove instance (use force if require), add instance back Identify the node which is not in sync: Execute following SQL statement on each node and identify the node has different UUID on all nodes. mysql> select * from mysql_innodb_cluster_metadata.instances; Stop group replication: Stop group replication on the node which does not have same UUID on all nodes. mysql > stop GROUP_REPLICATION; Remove instances from cluster: Remove all secondary node from the cluster and add them back if require. $mysqlsh JS >\c root@<IP_Address>:<Port_No> JS > dba.getCluster().status() JS > dba.getCluster () <Cluster:cluster_name> JS > var cluster = dba.getCluster("cluster_name"); JS >  cluster.removeInstance('root@<IP_Address>:<Port_No>'); If you get "Cluster.removeInstance: Timeout reached waiting......" JS > cluster.removeInstance(...