自定义oracle数据库以及jdbc连接讲解

新建数据库

eg：实例名：forumbank

cmd管理员：

mkdir D:\app\Administrator\admin\forumbank\adump

mkdir D:\app\Administrator\admin\forumbank\dpdump

mkdir D:\app\Administrator\admin\forumbank\pfile

mkdir D:\app\Administrator\cfgtoollogs\dbca\forumbank

mkdir D:\app\Administrator\flash_recovery_area

mkdir D:\app\Administrator\oradata\forumbank

set ORACLE_SID=forumbank

一个实例一个数据库

ORAPWD FILE=“D:\app\yourInstallName\product\11.2.0\dbhome_1\database\PWDforumbank.ORA” PASSWORD=forumbank123

修改pfile

Oradim -NEW -SID forumbank -SYSPWD 123 -STARTMODE auto -PFILE “D:\app\Administrator\admin\forumbank\pfile\initforumbank.ora”

SQLPLUS /NOLOG

CONNECT SYS/forumbank123 as SYSDBA

STARTUP NOMOUNT PFILE=“D:\app\Administrator\admin\forumbank\pfile\initforumbank.ora”

CREATE DATABASE bankdb

MAXINSTANCES 8

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

DATAFILE ‘D:\app\Administrator\oradata\forumbank\SYSTEM01.DBF’ SIZE 200M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE ‘D:\app\Administrator\oradata\forumbank\temp01.dbf’ SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

UNDO TABLESPACE undotbs1 DATAFILE ‘d:\app\Administrator\oradata\forumbank\undotbs1.dbf’ size 51200k

SYSAUX DATAFILE ‘D:\app\Administrator\oradata\forumbank\SYSAUX01.DBF’ SIZE 50M AUTOEXTEND ON NEXT 10M

LOGFILE GROUP 1 (‘D:\app\Administrator\oradata\forumbank\redo01.log’) SIZE 51200K,

GROUP 2 (‘D:\app\Administrator\oradata\forumbank\redo02.log’) SIZE 51200K,

GROUP 3 (‘D:\app\Administrator\oradata\forumbank\redo03.log’) SIZE 51200K

CHARACTER SET ZHS16GBK

NATIONAL CHARACTER SET AL16UTF16;

create spfile=’D:\app\yourInstallName\product\11.2.0\dbhome_1\database\spfileforumbank.ora’ FROM pfile=’D:\app\Administrator\admin\forumbank\pfile\initforumbank.ora’;

spfile路径命名规范：database下 spfile+实例名

(SID_DESC =

(GLOBAL_DBNAME = forumbank)

(ORACLE_HOME = D:\app\yourInstallName\product\11.2.0\dbhome_1)

(SID_NAME = forumbank)

)

listener.ora ：添加在SID_LIST里面 配置监听

cba =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME =forumbank)

)

)

tnsnames.ora配置连接标志符

重新启动监听，使其包含新的实例

conn sys/forumbank123@cba as sysdba

总结：

oracle版本为11.2

yourInstallName 为你的安装目录app下的数据库目录（包含product）的名称

注意实例与数据库第一次(无 nomount)和以后(有)启动 （就直接startup pfile路径）的差异

数据库名称限定八个字符 bankdb

如果数据库创建第一行失败：1.检查pfile里的初始化文件的dbname 与你的代码是否一致 2.如果改正后还出错（包含控制文件说明是初始化文件的问题），就删掉日志和 D:\app\Administrator\oradata下新的实例文件夹，再用命令ORADIM –DELETE –SID forumbank

删掉你的 新实例，改好初始化文件后重新建一个实例，重新走一遍就好

jdbc java连接代码

import java.sql.*;
public class ConnOracle  {
    public static void main(String[] args){
        Connection con;
        Statement sql;
        ResultSet rs;
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch (ClassNotFoundException e){
            System.out.print(e);
        }
        try{
            String url = "jdbc:Oracle:thin:@127.0.0.1:1521:forumbank";//@ip:端口:实例名
            con = DriverManager.getConnection(url, "user1", "forumbank123");
            sql=con.createStatement();
            rs=sql.executeQuery("SELECT * FROM USER_IOT WHERE userid = '2015000000'");
            while(rs.next()){
             String userid=rs.getString(1);//列索引，从1开始
                String pwd=rs.getString(2);
                 System.out.printf("%-12s",userid);//-用来保证前后间隔，不会自动去掉尾部多余长度
                System.out.printf("%-4s",pwd);
            }
con.close();
        }catch (SQLException e){
            System.out.println(e);
        }
    }
}

连接前请打开数据库，并确保已创建表及插入相关数据。

用户名请用sys赋予一个角色权限与密码，重新登录，此处用user1。连接jar包用的是：classes12.jar,当然你有ojdbc6.jar是最好。安装了oracle自会有classes12.jar,路径：D:\app\ yourInstallName\product\11.2.0\dbhome_1\oui\jlib

最后：相关文件存放路径：

initforumbank.ora : D:\app\Administrator\admin\forumbank\pfile

listener.ora: D:\app\yourInstallName\product\11.2.0\dbhome_1\NETWORK\ADMIN

tnsnames.ora: D:\app\yourInstallName\product\11.2.0\dbhome_1\NETWORK\ADMIN