Oracle: Export multiple table in a single command example

exp falhrma/falhrma@xe file=E:\ERP_BACKUP\FALHRMA_ACC_01122016.DMP log=E:\ERP_BACKUP\exp_FALHRMA_ACC_01122016.log  tables=HRM_EMP_SALARY_BANK_MYVW_TB,HRM_EMPLOYEE_ENTIT_MYVW_TB,HRM_EMPLOYEE_MYVW_TB,HRM_EMPLOYEE_SALARY_MYVW_TB,HRM_LEAVE_TRANS_DTL_MYVW_TB,HRM_LEAVE_TRANS_MAIN_MYVW_TB,HRM_RAW_DATA_ATTND_BKP_MYVW_TB,HRM_SALARY_DTLS_MYVW_TB,HRM_SALARY_MST_MYVW_TB,HRM_WEEKEND_MYVW_TB MANUAL_ATTENDANCE_INFO_MYVW_TB consistent=y

SQL SERVER: Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF

If you faces, mentioned  kind of  problem , pls run the following :

SET IDENTITY_INSERT <Table1> ON

After running this command, you can easily insert identical table data.

SQL SERVER: SQL Server blocked access to procedure 'sys.sp_OACreate'

Run the Following commands together:

sp_configure 'show advanced options', 1  
GO   
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1  
GO   
RECONFIGURE;  
GO   
sp_configure 'show advanced options', 1  
GO   
RECONFIGURE;

Oracle: Duplicate rows deleted example

To delete duplicate rows from any table, you can follow following example. For multiple column relation, you have to use MULTIPLE COLUMN.


DELETE FROM NANO_TABLE
WHERE rowid not in
(SELECT MIN(rowid)
FROM  NANO_TABLE
GROUP BY COLUMN1);

Oracle: Linux Shell script for exporting database example

Example execute below command from root

# vi /soft/Nano_Ora_Export.sh


#!/bin/bash
now="$(date +'%d%m%Y')"

ORA_HOME=/DATABASE/u01/app/oracle/product/11.2.0/dbhome_1

ORACLE_HOME=/DATABASE/u01/app/oracle/product/11.2.0/dbhome_1

ORA_OWNER=oracle

su - $ORA_OWNER -c "$ORA_HOME/bin/exp userid=user_schema/user_pass@db_name statistics=none full=N owner=user1,user2 consistent=y file=/soft/DBUser1User2"$now"DB.dmp log=/soft/DBUser1User2"$now".log";

zip -r /var/www/DBUser1User2"$now"DB.zip /soft/DBUser1User2"$now"DB.dmp;

exit $?


After  that execute below command:

# chmod 750 /soft/Nano_Ora_Export.sh

Now, you can set this file to your crontab location to export database automatically.

Oracle: ORA-12712: new character set must be a superset of old character set

To change the character set of  database , we have to follow  following steps:

1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

2. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.

3. Startup Oracle database

SQL> startup mount;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

If you get the error ORA-12712, please update the following table.

"ORA-12712: new character set must be a superset of old character set"

ORACLE: To change the database character set ALTER DATABASE CHARACTER SET WE8ISO8859P1;

To change the character set of  database , we have to follow  following steps:

1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

2. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.

3. Startup Oracle database

SQL> startup mount;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

If you get the error ORA-12712, please update the following table.

"ORA-12712: new character set must be a superset of old character set"