Thursday, March 17, 2016

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

I offered a command to take database backup in oracle and ended up with error after a few minutes

expdp scott/tiger dumpfile=scott.dmp directory=DATA_PUMP_DIR parallel=4 status=1

Worker 4 Status:
  Process Name: DW03
  State: EXECUTING
  Object Schema: scott
  Object Name: emp
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1014
  Worker Parallelism: 3
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes


SOLUTION
Use %U in dumpfile creation, expdp will automatically take care of number of file need to created as per size of database size

expdp scott/tiger dumpfile=scott_%U.dmp directory=DATA_PUMP_DIR parallel=4

Tuesday, March 15, 2016

want to divide table into 10 equal parts

if you want to divide table into 10 equal parts then you might need to min max of number (field which holds the primary key)
Instead of manual work, you can easily identify the min max for division purpose, you can use below query

select
min(object_id) as v_start
,max(object_id) as v_end
,num
from
(
select 
object_id,
--// divide all rows into group number
ntile(10) over (order by object_id) num
from all_objects

group by num;

Sunday, March 13, 2016

How to auto delete macro code after execution

We can also auto delete macro code writen in excel workbook. Some times workbooks are generated thru the java/.net and other code, it can have macro codes or can have compatibility issues. To remove those issues we have to delete macro codes.

Lets edit macro code from previous post.

Sub Auto_Open()

    Dim wk As Worksheet
    ' Declare object variable
    Dim vbCom As Object
    Set vbCom = Application.VBE.ActiveVBProject.VBComponents
   
    MsgBox "Fill Values in cell of all sheets"
    For Each wk In ThisWorkbook.Worksheets
    wk.Activate
    With wk.Range("I5")
    .FormulaR1C1 = "testqqq"
    End With
    Next wk

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
   
End Sub

 
* note - highlighed code in yellow color is newly added in previous post.

Now save the macro code close and reopen the workbook.

ERROR:
Run-time error '1004':

Programmatic access to Visual Basic Projects is not trusted


SOLUTION
Please look for below screen shot and follow the same process and enable the trust access to the VBA code

   

Close and reopen the workbook.. It works!!!!!!

Auto Open [Run] macro code - when workbook open

Lets have look on older post, which is showing that how to execute macro code for individual sheet of a workbook.

http://j4info.blogspot.in/2013/05/execute-macro-for-each-sheet-in-workbook.html

In this post we will do workaround that how to auto open macro code and start its working after immediate open of workbook

Sub Auto_Open()
    Dim wk As Worksheet
    MsgBox "Fill Values in cell of all sheets"
    For Each wk In ThisWorkbook.Worksheets
    wk.Activate
    With wk.Range("I5")
    .FormulaR1C1 = "testqqq"
    End With
    Next wk
End Sub


USe Auto_Open() thats it....
web stats