SQL Server - Delete data from table contained huge records and control the log file size

 OK, first of all. I do not need to explain the difference between truncate and delete. You most probably already knew it. I don't think truncate will help at this moment.

My situation here is my table has almost 90M records and this records was a monthly data snapshot for a year. I need to remove records from Jan to Jun and the rest will remain.

Let summarize this;

Objective : To remove past 6 months records which approximately 45M ++ records to be deleted

Constraint : My disk utilization almost 90% and insert/delete command both will generate log which will make log file increase drastically during the insert/delete operation. 

What I do?

As I mentioned earlier, my data is a monthly snapshot data. I cannot simply execute delete command with filtering condition where date < '2020-07-01' right? If you do this, your log file with increase skyrocketly.

I prepared 6 delete command lines, each specifically will delete data for only 1 month, in sequence. And to handle the log? I will put extra DBCC command so that it will shrink the log file before another delete operation taking place.

DBCC SHRINKFILE (your_log_file_name, 1);  * 1 means shrink the file to 1MB

Before that, I need to make sure that the database recovery log are set to Simple instead of Full.

delete from my_table where date < '2020-02-01';

DBCC SHRINKFILE (your_log_file_name, 1);

delete from my_table where date < '2020-03-01';

DBCC SHRINKFILE (your_log_file_name, 1);

delete from my_table where date < '2020-04-01';

DBCC SHRINKFILE (your_log_file_name, 1);

delete from my_table where date < '2020-05-01';

DBCC SHRINKFILE (your_log_file_name, 1);

delete from my_table where date < '2020-06-01';

DBCC SHRINKFILE (your_log_file_name, 1);

delete from my_table where date < '2020-07-01';

DBCC SHRINKFILE (your_log_file_name, 1);

That approach works for me and I believe it should work for you too. I you have suggestion or comment, do comment me below.

Thanks!

 

APEX : ORA-01008: not all variables bound

It is really frustrating if everything run not as per plan right?..:-)

Today I encounter error ORA-01008: not all variables bound, when executing a report from a SQL script. At first, I suspected because of a LEFT JOIN syntax problem.

What Oracle say about ORA-01008?

ORA-01008: not all variables bound

Cause: A SQL statement containing substitution variables was executed without all variables bound. All substitution variables must have a substituted value before the SQL statement is executed.

Action: In OCI, use an OBIND or OBINDN call to substitute the required values.

I not really understand it much. But below solution works well for me on my APEX application. And the report generation have no problem any more.

Initially, my SQL code was as below:

where employee = :P1_EMPLOYEE

Solution: 

where employee = v('P1_EMPLOYEE')

Have a good day!

Obtain machine IP Address using Oracle SQL command

I never know this function has exist until I was required to explore Oracle APEX to design a website. Here is the code, which can get an IP address of machine and the value can be store into a database or pass it into other variable for processing, or whatever your programming do.

select sys_context('userenv','ip_address')  from dual

Have fun.