oracle数据库 各种问题及解决

解决： ORA-01034: ORACLE not available

sqlplus /nolog
conn /as sysdba
select instance_name from v$instance;
如果提示Oracle not available，执行：
startup pfile='E:\oracle\appInstall\admin\orcl\pfile\init.ora.8292018****'

解决：ora-00119和ora-00132
参考https://blog.csdn.net/xb12369/article/details/26720275

\\

=======
tnsnames.ora:
# tnsnames.ora Network Configuration File: H:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#*****
ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 60.*****)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.sunny.com)
    )
  )
#*****
ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.*****)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  ) 
#*****
ORCL3=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.*****)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.sunny)
    )
  )



========
listener.ora:
# listener.ora Network Configuration File: H:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = H:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:H:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 60.*****)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.*****)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.*****)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = H:\app\Administrator

===================================================
//2015-03-27熟悉oracle数据库的基本知识，熟悉plsql操作：
//查询当前数据库名：
select name from v$database;               //结果：ORCL
//查询当前数据库实例名：（SID）
//实例名指的是用于响应某个数据库操作的数据库管理系统的名称。它同时也叫SID。
//数据库实例名(instance_name)用于对外部连接。在操作系统中要取得与数据库的联系，必须使用数据库实例名。
//比如我们作开发，要连接数据库，就得连接数据库实例名：
//jdbc:oracle:thin:@localhost:1521:orcl（orcl就为数据库实例名）
//一个数据库可以有多个实例，在作数据库服务集群的时候可以用到。

select instance_name from v$instance;     //结果：orcl,数据库实例名用于对外部连接，要连接数据库，就得连接数据库实例名。
===========================================================================================================
//scott用户访问表emp;
select * from emp;

//dept表
select * from dept;

//salgrade表
select * from salgrade;
===========================================================================================================
//创建完数据库后，并不能立即在数据库中建表，必须先创建该数据库的用户，并且为该用户指定表空间。
//安徽工情
//1.创建表空间"ahdemo"
select instance_name from v$instance;
  create tablespace ahdemo datafile 'H:\app\Administrator\oradata\orcl\ahdemo.dbf' size 500m autoextend on next 100m maxsize 2048m; //表空间最大2g
//查看已经创建好的表空间
select default_tablespace,temporary_tablespace,d.username from dba_users d  where d.username like '%ah%'
 group by d.default_tablespace, d.temporary_tablespace, d.username.
 //查看所有表空间大小
 select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

//2.创建用户"ahuser",并为之分配创建的表空间和临时表空间，并为之授权
create user ahuser identified by "Sunny_123456]" default tablespace ahdemo temporary tablespace temp;
//有了用户，要想使用用户账号管理自己的表空间，还得给它分权限：(ahuser已被授予了connect/resource/dba三种权限)
grant connect to ahuser; 
grant resource to ahuser;
grant dba to ahuser;
//查看数据库用户
select * from dba_users order by created desc;
//=====================================================================================
//操作表
//==========================tb_users===
select *  from tb_users;

//==========================tb_dept===
select *  from tb_dept;

oracle客户端乱码问题解决：

通常我们在导入导出Oracle数据时，会遇到乱码现象，一般是由于oracle字符集不一致造成的，下面将展示如何修改oracle字符集。

[oracle@ORACLE ~]$ 
1、sqlplus窗口下
sqlplus /nolog

SQL> conn /as sysdba;

SQL>select userenv('language') from dual;

SQL>SHUTDOWN IMMEDIATE

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 INTERNAL_USE ZHS16GBK;


SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP;

SQL>select userenv('language') from dual;

其中常见的Oracle字符集有ZHS16GBK、AL32UTF8、US7ASCII等等。

2、vi .bash_profile
文件末尾追加：
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
或者
export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

# 使 bash_profile 设置生效

3、source .bash_profile

问：plsql访问远程数据库正常，程序访问时报错ORA-12505（TNS:listener does not currently know of SID given in connect descriptor）
解决：
将
jdbc:oracle:thin:@10.138.100.100:1521:omdt
修改为
jdbc:oracle:thin:@(description=(address=(protocol=tcp)(port=1521)(host=10.138.100.100))(connect_data=(service_name=omdt)))
如果是在properties文件中，则上述字符串中的“:”改为“\:”转义一下即可。
参考：
https://www.cnblogs.com/cbread/p/5751228.html