Thursday, March 17, 2016

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

This error can be seen during import export of database in Oracle server. Error can be throw because of wrong command fired on prompt. I my case i have been using fixed dump file name with parallel processing.
In order to take database backup we need to use different syntax so that we will not face this error in future. I have writing my observation below, how i resolved this error. Please provide your valuable comments if it works.

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
  Object Schema: scott
  Object Name: emp
  Completed Objects: 1
  Total Objects: 1014
  Worker Parallelism: 3
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

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 of primary field (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

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

group by num;

You can use above logic any where for further logic's. 

Sunday, March 13, 2016

How to auto delete macro code after execution

We can also auto delete macro code written in excel workbook. Some times workbooks are generated thru the java/.net and other automation process, it can have macro codes or can have compatibility issues for further usage of spreadsheet/workbook. To remove those issues we have to delete macro codes. See below step we need to perform to achieve the same.

Lets edit macro code from previous post.

Sub Ato_Open()

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

vbCom.Remove VBComponent:= _

End Sub

* note - highlighed/Bold code  is newly added in previous post.

Now save the macro code close and reopen the workbook.

Run-time error '1004':

Programmatic access to Visual Basic Projects is not trusted

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.

Earlier we were able to manage to execute macro code for each sheet available in spreadsheet or Workbook. We had achieved one task and now mind think why i need to execute macro code manually, it should be executed automatically as soon as i open the spreadsheet. I started working on this, it is well said nothing is impossible. we did this, see below.

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
    With wk.Range("I5")
    .FormulaR1C1 = "testqqq"
    End With
    Next wk
End Sub

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