If you want to delete duplicate rows but not all rows in table, you can use a procedure. For example, you have 10 duplicate rows and you want to delete 9 rows, then you can use procedure to delete process. For instance, you have a table as tmp_table. tmp_table’s columns are id,name,surname,city,phone. You want to…
Category: Databases
How to Take Export Backup in Oracle on Windows Servers
If you want to take export backup on windows servers, you can take with batch scripts and windows schedule tasks. You can create backup directory for this scripts and backups. We will use EXPORTDIR directory and below data, scripts, daily directories. You have to create three scripts file below scripts directory for this issue. Firstly,…
Turkish Letter (i) Convert Problem in Oracle
When you make converting Turkish chacter (i) with upper function, the result is (I). This is a problem. Because you want to pair two word but you can not pair with using the familiar functions which are upper, lower, initcap. You can use NLS_UPPER function for the solution to solve this problem. Example SELECT NLS_UPPER(‘şampiyon’,…
Schedule Backup in SQL Server 2008 Express Edition
If you use SQL Server Express, You can take schedule backup with sql script and windows task schedule. Firstly, you have to create backup script, after you have to create batch file for task schedule. Create Backup Script BACKUP DATABASE [dbname] TO DISK = N’D:\SQLBackup\dbname.bak’ WITH NOFORMAT, NOINIT, NAME = N’dbname-Full Database Backup’, SKIP, NOREWIND,…
Start and Stop Instance in Oracle Rac System
You can demand stop one instance in Oracle Rac System. This stop process have to perform step by step. Firstly, stop instance after asm and finally stop applications. The first step is to stop the Oracle instance. When the instance (and related services) is down, then bring down the ASM instance. Finally, shut down the…
Create Fast Refresh for Materialized Views in Oracle
You can demand fast refresh for materialized views. Because refresh of tables which have large data are hard. The refresh time is long. Thus, you have to use fast refresh statement for this object. Firstly, You have to create materialized view log for table. If change a data, this changes will write this log. You…
Show Last Refresh Time of Materialized Views in Oracle
You can demand last refresh time of materialized views. You can use all_mviews view for show refresh times. Example SQL> select last_refresh_date , mview_name from all_mviews;
Export and Import Data in Oracle
Oracle’s export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database. These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary…
Translate Function in Oracle
The translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the…
Alter Profile in Oracle
If you want to change profile parameters, you have to use alter command. Default User Life Time coming limited as 180 in Oracle 11g. You can change of this limit. Because this parameter cause of expired password. If you do not want to expired password, You have to change this parameter. Show Profile Parameters SQL>…