Oracle Sql

USING SQL PLUS or Any Oracle Tool Like “SQL Developer or TOAD”
==============================================================
create user supplier2 identified by supplier2
grant connect,resource,dba to supplier2

unlock
======
alter user joe account unlock;
alter user awb account unlock identified by awb;

change password
===============
alter user awb identified by awb;

To use import tool go to the following path in cmd:
===================================================
1. C:\Oracle\app\Administrator\product\11.2.0\dbhome_1\BIN
Then run the following command with different parameter:
========================================================

imp supplier2/supplier2 file = c:\SUPPLIER2.DMP full = yes
expired users return
select username,account_status,expiry_date from dba_users;

select password from sys.user$ where name=’MDSYS’;

alter user MDSYS identified by values ‘72979A94BAD2AF80’;

alter user joe account unlock;
Import & Export Dumps
================
exp emaar/emaar file = c:\emaar.dmp

Full database all users
=======================
exp target/target file = c:\target_Aust.dmp

exp target/target file = c:\target_Aust.dmp owner=target

from user to another user
==========================
imp emaar/emaar file = c:\emaar.dmp fromuser=emaar touser=emaar ignore=y

imp emaar/emaar@trngdb fromuser=emaar touser=emaar file=c:\emaar.dmp ignore=y

To Import Data Pump it is more faster and flexible than imp/exp
================================================================
http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
CONN / AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS ‘/u01/app/oracle/oradata/’;
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

How to Fix ORA-12162 TNS:net service name is incorrectly specified Error in Oracle?

sometimes  the oracle profile is not prepared correctly and this give so many errors and you should go direct to directory of SqlPlus to start DB

/u01/app/oracle/product/11.2.0/dbhome_1/bin

./sqlplus sys/password as sysdba;

the following error will arises

ORA-12162 TNS:net service name is incorrectly specified

Cause

This arises if you have not set ORACLE_HOME and ORACLE_SID.

Solution

you can fix it by setting the ORACLE_HOME and ORACLE_SID as following

> export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
> export ORACLE_SID=ECMTEST

and try to start the DB 

./sqlplus sys/password as sysdba;
sql> startup force;

run linux command from java and reading result?

import java.*;
class Test {

public static void main(String[] args) {
int ch;
try {
Process myProcess = Runtime.getRuntime().exec(“ls -al”);
InputStreamReader myIStreamReader = new InputStreamReader(myProcess.getInputStream());
while ((ch = myIStreamReader.read()) != -1) {
System.out.print((char) ch);
}
} catch (IOException anIOException) {
System.out.println(anIOException);
}
}
}

SqlExecuter vs JDBC

public class CmdExec {

	public static void main(String argv[]) {
	           executeSql();
	  
	}
	private static void executeSql() {
	    final class SqlExecuter extends SQLExec {
	        public SqlExecuter() {
	            Project project = new Project();
	            project.init();
	            setProject(project);
	           // setTaskType("sql");
	            setTaskName("sql");
	        }
	    }

	    SqlExecuter executer = new SqlExecuter();
	    executer.setSrc(new File("/home/admin/Obama/scripfile.sql"));
	    executer.setDriver("com.mysql.jdbc.Driver");
	    executer.setPassword("root");
	    executer.setUserid("root");
	    executer.setUrl("jdbc:mysql://localhost:3306/Central_Share");
	    executer.execute();
	}
	
	}
//add dependency 
 <dependency>
        <groupId>izpack</groupId>
        <artifactId>izpack-standalone-compiler</artifactId>
        <version>3.7.2</version>
      </dependency>

how to get last insert id by java

	public static int getLastInsertID(Connection conn, String statment)throws SQLException {
		int lastInsertedId = -1;
		PreparedStatement ps = conn.prepareStatement(statment,PreparedStatement.RETURN_GENERATED_KEYS);
		ps.executeUpdate();

		ResultSet rskey = ps.getGeneratedKeys();
		if (rskey != null && rskey.next()) {
			lastInsertedId = rskey.getInt(1);
		}
		return lastInsertedId;
	}

How to find and replace in mysql

To find a string in a certain field and replace it with another string:

update [table_name] set [field_name] = replace([field_name],'[string_to_find]’,'[string_to_replace]’);

 

update `wp_posts` set `post_content` = replace(`post_content`,'http://localhost/elrashd/holding/','http://elrashad.promolinks.com/')

unknown-table-engine-innodb

http://www.osterman.com/wordpress/2007/12/23/unknown-table-engine-innodb

 

I was getting the error “ERROR 1286 (42000): Unknown table engine ‘InnoDB’” when trying to create/alter tables with the InnoDB engine. You can check for this warning by issuing the create or alter statements and then running

show warnings;

in the same mysql client session.

Check if you have InnoDB support enabled:

mysql> show variables like ‘have_innodb’;
+—————+———-+
| Variable_name | Value    |
+—————+———-+
| have_innodb   | DISABLED |
+—————+———-+

The problem is that InnoDB is not enabled by default in Debian distributions of my.cnf.  To enable it, simply comment the following lines under the [mysqld] section.

#skip-innodb