expdp with sys without knowing password

Simple but very handy way of taking exports without knowing database password.

expdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=example.dmp LOGFILE=example.log SCHEMAS=MYSCHEMA

Why are we using root cause analysis?

RCA stands for Root-Cause Analysis. Why are we using RCA or specifically, why we are using RCA in operation.

It is clear that documenting, archiving, publishing RCA, helps to solve problems faster in re-occurences of problems.

But the there seems to be a deeper philosophy behind...

While looking at Project Management Quality Management topics in Rita Mulcahy(RIP), I see a good principle which may help us in future problems.

 

The 80 percent of problems are due to 20 percent of the root causes. [Pareto Principle, 80/20 principle by Joseph Juran]

If this is real then solving 20 percent of root causes will solve our 80 percent of our problems. So why not find and deal with those big fishes... Or preventing them before occuring.

PLS-00302 error worths me two cups of Turkish tea!

Problem:

A db user came to me that, something strange is happening, he could not manage to execute a PL/SQL block.

He is getting PLS-00302 error while executing an anonymous PLSQL block.

In PLSQL block, there is a function QUERYFUNC which is already created in the same schema.

Thus giving right with grant option is nonsense.

The weird thing does not happened when I executed in SYSTEM schema.

This is what happens:

DUMMY@MYDB:node1> select DUMMY.QUERYFUNC('345354') from dual;

DUMMY.QUERYFUNC('345354')
----------------------------------------------------------------------------
X

1 row selected.

DUMMY@MYDB:node1> declare
2
3  RetVal VARCHAR2(200);
4  MYVAR VARCHAR2(200);
5
6  BEGIN
7  MYVAR := '12345';
8
9  RetVal := DUMMY.QUERYFUNC ( MYVAR );
10
11  --DBMS_OUTPUT.Put_Line('RetVal = ' || DUMMY.QUERYFUNC ( MYVAR ));
12
13  END;
14
15
16
17  /
RetVal := DUMMY.QUERYFUNC ( MYVAR );
*
ERROR at line 9:
ORA-06550: line 9, column 15:
PLS-00302: component 'QUERYFUNC' must be declared
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored

Cause:

Below code worked, in which DUMMY schema is not specified.
DUMMY@MYDB:node1> declare
2
3  RetVal VARCHAR2(200);
4  MYVAR VARCHAR2(200);
5
6  BEGIN
7  MYVAR := '12345';
8
9  RetVal := QUERYFUNC ( MYVAR );
10
11  --DBMS_OUTPUT.Put_Line('RetVal = ' || DUMMY.QUERYFUNC ( MYVAR ));
12
13  END;
14
15  /

PL/SQL procedure successfully completed.

First of all I guessed it was because we have same function somewhere else, or there is synonym object which overrides the QUERYFUNC function.

But after querying dba_objects I could not find any.

At last, the goal comes from other side...

Solution

There was a table named DUMMY in the schema named DUMMY.

Which assumes DUMMY.QUERYFUNC is a table column or something I guess.

Thank you for the solution, Mustafa Saburlu. "Afiyet olsun" for Turkish tea :)

Using Index Unusable For Gaining Disk Space In Partitioned Tables

Why dedicate disk space for indexes that you are not using for data access in older partitions.

--index partition usable

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date('31.10.2011 23:45:00','dd.mm.yyyy hh24:mi:ss')

Plan
SELECT STATEMENT ALL_ROWS Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2
	3 PARTITION RANGE SINGLE Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2 Partition #: 1 Partitions accessed #1
		2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2 Partition #: 2 Partitions accessed #1
			1 INDEX RANGE SCAN INDEX (UNIQUE) ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK Search Columns: 1 Access Predicates: "DATETIME"=TO_DATE(' 2011-10-31 23:45:00', 'syyyy-mm-dd hh24:mi:ss') Cost: 159 Cardinality: 79.677 CPU Cost: 17.926.550 IO Cost: 158 Time: 1 Partition #: 3 Partitions accessed #1

--index partition unusable, after

ALTER INDEX ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK

MODIFY PARTITION P20111031

UNUSABLE;

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date('31.10.2011 23:45:00','dd.mm.yyyy hh24:mi:ss')

Plan
SELECT STATEMENT ALL_ROWS Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9
	2 PARTITION RANGE SINGLE Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9 Partition #: 1 Partitions accessed #1
		1 TABLE ACCESS FULL TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Filter Predicates: "DATETIME"=TO_DATE(' 2011-10-31 23:45:00', 'syyyy-mm-dd hh24:mi:ss') Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9 Partition #: 2 Partitions accessed #1

--other partitions goes over indexes (usable index partitions) Beware that, if you are using bind variables, you may go with FTS over indexed partitions. You may use hints for a solution for access path, like INDEX(CELLHANDOVERS CELLHANDOVERS_PK).

 

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date('01.11.2011 23:45:00','dd.mm.yyyy hh24:mi:ss');

Plan
SELECT STATEMENT ALL_ROWS Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1
	3 PARTITION RANGE SINGLE Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1 Partition #: 1 Partitions accessed #2
		2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1 Partition #: 2 Partitions accessed #2
			1 INDEX RANGE SCAN INDEX (UNIQUE) ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK Search Columns: 1 Access Predicates: "DATETIME"=TO_DATE(' 2011-11-01 23:45:00', 'syyyy-mm-dd hh24:mi:ss') Cost: 7 Cardinality: 2.920 CPU Cost: 749.500 IO Cost: 7 Time: 1 Partition #: 3 Partitions accessed #2

Unique constraint also worked for usable index partition.       

Inserting same row in the range usable index triggered below error:

ORA-00001: unique constraint (ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK) violated

Only thing to remember is: DML activity on unusable partitions is impossible, without rebuilding the index!

Inserting existing row, which must trigger unique constraint, gives unusable state error:

insert into ADMIN1.RNC_CELLSTATS_DERYA_CFO(datetime,rnc,cell) values (to_date('31.10.2011 23:45:00','dd.mm.yyyy hh24:mi:ss'),'KRP1R01','YUNUP32');

ORA-01502: index 'ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK' or partition of such index is in unusable state

Inserting non-existent row, which must not trigger unique constraint, also gives  unusable state error:

insert into ADMIN1.RNC_CELLSTATS_DERYA_CFO(datetime,rnc,cell) values (to_date('31.10.2011 23:44:00','dd.mm.yyyy hh24:mi:ss'),'KRP1R01','YUNUP32');

ORA-01502: index 'ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK' or partition of such index is in unusable state

In a 1TB sized table RNC_CELLSTATS, making unusable frees 15 GB. Thus, you may not gain big spaces from big tables. While selecting targets  focus on index segments having huge sizes.

SEGMENT_NAME	GB	# partitions
RNC_CELLSTATS	980	91
RNC_CELLSTATS_PK	15	91

Below, focusing on index segments sizes over 100G, you may earn 6 TB disk space. Data is extracted by using DBA_SEGMENTS dictionary view.

SEGMENT_NAME	Size GB	# partitions	Earned GB
CELLHANDOVERS_PK	2.666	1.100	2132
CELLHANDOVERS_IND1	859	1.100	686
CELLHANDOVERS_IND3	819	1.100	654
CELLHANDOVERS_IND2	811	1.100	648
SGSN_CDR_NDX03	725	65	362
RNC_CELLHO_PK	311	91	155
MGW_E1TTP_PK	271	91	216
RNC_GSMRELATION_PK	245	91	122
CELLIDENTITY_NDX	228	65	114
RNC_CELLHO_PK2	208	91	103
RECORDOPENINGDATE_NDX	193	65	96
INTERNAL_SOHO_DS_MISSING_NE_IX	188	31	93
ENUM_FULL_IND01	161	180	128
MGW_E1TTP_D_PK	146	91	72
NEIGHBOUR_CELL_PK	128	100	63
CELLHANDOVERS_CL_PK	123	1.100	98
CCNPERFCOUNTER_PK	122	31	60
MGW_TDMTERMGRP_PK	109	91	54
CELLHANDOVERS_CL_IND2	102	1.100	81
			6,280

How to kill, get rid of sticky RMAN processes

Killing RMAN processes with alter system kill session... will not have an effect on them.They will marked as killed but never killed.

You have no time to wait, but those silly RMAN processes have much of that time. So, how to deal with these guys?

Finding the SPID of the process and killing from OS side is a solution, which most people recommend.

I have a practical, handy solution for this case, don't wait them:) Use disconnect session clause with immediate keyword with alter system disconnect session. For example, you can disconnect a session having SID 1122 and serial# 222 like below:

alter system disconnect session '1122,222' immediate;

Below is a script which could be used to generate disconnect session sqls for all of the RMAN sessions.

SELECT 'alter system disconnect session '''||sid||','||serial#||''' immediate;' 
FROM V$SESSION s 
 WHERE 
(
UPPER( s.PROGRAM )LIKE 'RMAN%'
)

 

Remember that there are differences between disconnect and kill session clauses.
Most remarkable is ability to give inst_id or not.

In case of disconnect session, You cannot issue disconnect session to sessions spread on cluster instances from one session. Thus, you must directly connect to sessions' instance first, and then issue the alter system disconnect session statement.

In case of kill session, you have a chance to give third parameter "inst_id" in kill session clause. This allows you to kill sessons spread on cluster instances from one session.

It woths referring to SQL Language Reference for details of disconnect session and kill session clauses.

Hope this helps!