Oracle: Delete all constraint from multiple table

If you want o delete all constraint from your required tables , you can use following PL/SQL program:

begin
    for r in ( select table_name, constraint_name
               from user_constraints
               where TABLE_NAME in ('TABLE1', 
                                    'TABLE2',
                                    'TABLE3') )
    loop
        execute immediate 'alter table '||r.table_name
                          ||' drop constraint '||r.constraint_name;
    end loop;
end loop;

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

 

In Oracle Database 11g you can get this error.

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set


SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Database altered.

All steps for change database character parameter.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             268437756 bytes
Database Buffers          335544320 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

SQL>
SQL>
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set


SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Database altered.

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             272632060 bytes
Database Buffers          331350016 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.

Oracle Database Auto Database Startup and Shutdown on Linux Platform

First login using Root user ;  then use the below command :

# vi /etc/oratab

You will find following line ( you may fund different line as per your install location of DB).

NANODB:/u01/app/oracle/product/11.2.0/dbhome_1:N

Now, you have to change it to

NANODB:/u01/app/oracle/product/11.2.0/dbhome_1:Y


Now, Create a file called '/etc/init.d/dbora' as the Root user, containing the following code.

#!/bin/sh

# chkconfig: 345 99 10

# description: Oracle auto start-stop script.

#

# Set ORA_HOME to be equivalent to the $ORACLE_HOME

# from which you wish to execute dbstart and dbshut;

#

# Set ORA_OWNER to the user id of the owner of the

# Oracle database in ORA_HOME.

mount -t tmpfs shmfs -o size=28g /dev/shm

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

if [ ! -f $ORA_HOME/bin/dbstart ]

then

echo "Oracle startup: cannot start"

exit

fi

case "$1" in

'start')

# Start the Oracle databases:

# The following command assumes that the oracle login

# will not prompt the user for any values

su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"

su - $ORA_OWNER -c $ORA_HOME/bin/dbstart

;;

'stop')

# Stop the Oracle databases:

# The following command assumes that the oracle login

# will not prompt the user for any values

su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop dbconsole"

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"

su - $ORA_OWNER -c $ORA_HOME/bin/dbshut

;;

esac

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

/etc/init.d/httpd stop

/etc/init.d/httpd start

route add default gw 225.57.178.188 em1

exit $?

  

Use the chmod command to set the privileges to 750 as well as service list

# chmod 750 /etc/init.d/dbora

#chkconfig --add dbora

Oracle and PHP: Using Bangla in Oracle and PHP (Unicode)

SQL> conn sys as sysdba
Connected.
SQL>
SQL>
SQL>  shutdown immediate;
         Database closed.
         Database dismounted.
         ORACLE instance shut down.
SQL> startup restrict;
         ORACLE instance started.

Total System Global Area  ####### bytes
.
.
.
.


Database mounted.
Database opened.

SQL> alter database character set AL32UTF8;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  ####### bytes
.
.
.
.


Database mounted.
Database opened.

Now , you can insert BANGLA in Oracle Database. 

Again, you need to access from PHP front end. You have to add character set in PHP connection file. 
I used below line:
   
 <?php
         $db_charset = 'AL32UTF8';

$con = oci_connect('nano', 'nan786', '192.168.1.111/nandb',$db_charset);
   ?>

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"

Oracle: EXP-00091 Exporting questionable statistics tips

We can remove the this kind of error like EXP-00091  using  statistics=none or by setting the client character set 

Below is an example 

exp userid= Schema_name/schema_password@db_service_name full=N  statistics=none owner=(Schema_name1, Schema_name2) file=/DATABASE/u01/DB_Schema.dmp log=/DATABASE/u01/ DB_Schema.log

Example : Multiple data update in single query in Oracle

For Multiple data update in single query in Oracle , following example can be used:


Update Table1 a
set (a.NAME) = (select b.NAME
                                  from Table2 b
                                  where b.id = a.id)

Create Segment using database procedure in Oracle

CREATE OR REPLACE PROCEDURE DATA_SEGMENT_ORCL IS
            cursor all_tab is select distinct table_name
            from SYS.ALL_TABLES
            where owner='YOUR_SCHEMAA' and SEGMENT_CREATED = 'NO';
               
           v_str_or Varchar2(2000);
                   v_err Varchar2(3000);
        BEGIN
         
         
        for i in all_tab loop
                   
            v_str_or :='ALTER TABLE '||i.table_name|| ' ALLOCATE EXTENT';
           
                          begin
                         
                          execute immediate v_str_or;
                         
                         
                           EXCEPTION
                             WHEN others THEN
                                  v_err:=sqlerrm;
                                   --insert into A_ERROR_ALTER(TBLE_NAME, ERR_CODE) Values (i.table_name,v_err);
                                   commit;
                                  -- Null;
                            end;    
                           
                  v_str_or:='';
                     
            end loop;
           
       
                 
           
        END ;

PHP : Short format of starting and ending scope

To use short format of  starting and ending scope in PHP like <? echo "Hello"; ?>
we have to change the paarmeter
short_open_tag=On

from /etc/php.ini file.

If we use short_open_tag=Off, we have to use  <?php  echo "Hello"; ?> .

MYSQL data process error: "The total number of locks exceeds the lock table size"

To solve the problem, please add below line  under the [mysqld] in /etc/my.cnf file. I have used

 innodb_buffer_pool_size=4256M

After that we have to execute the command from root user.

#service mysqld restart

Delete duplicate row in MYSQL database example

To delete duplicate rows in MYSQL database, we can use below query:


DELETE n1 FROM TABLE_A n1, TABLE_B n2
         WHERE n1.id > n2.id AND n1.emp_code = n2.emp_code AND n1.attnd_date = n2.attnd_date
          AND n1.attnd_date>='2016-01-11' AND n1.attnd_date<='2016-01-20'
          

Create table from another table in SQL SERVER Example

To create a table from another table SQL SERVER database with all data, following example can be used:

"Select * into NewNanoTable from OldNanoTable"

Following is also used:

 "Insert into  TABLE2
SELECT *
FROM TABLE1"

Change Oracle case sensitivity of database Password

At the time of development , we are facing a problem of case sensitivity of database schema password. To remove  this, please follow the following instruction.

1. Connect to SYS user

  sql> conn sys as sysdba

  sql> alter system set SEC_CASE_SENSITIVE_LOGON=FALSE scope=both;

After that , if you type mix case in your password, it will be accepted by database and your development will be faster.