你的位置:首页 > 数据库

[数据库]oracle 11G创建表空间、用户、配置监听和TNS


最近总在安装各种版本的oralce数据库做测试,11G,32位的,64位的,12C的,每次都折腾表空间,用户、tns啥的,这里记录下,再也不用现用现百度找了

一、创建表空间、用户 

在plsql工具中执行以下语句,可建立Oracle表空间。

/*分为四步 */
/*第1步:创建临时表空间  */
create temporary tablespace tempdb
tempfile 'C:\oracledata\cognos_temp.dbf'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 

/*第2步:创建数据表空间  */
create tablespace budget 
--logging 
datafile 'C:\oracledata\cognos_data.dbf'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local;


/*第3步:创建用户并指定表空间  */
create user aicognos identified by qaz#1234
default tablespace budget
temporary tablespace tempdb; 

/*第4步:给用户授予权限  */
grant connect,resource,dba to aicognos;

二、配置服务器端监听文件

c$\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN的listener.ora

COGNOSDB86 =
  (ADDRESS_LIST =
        (ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCCOGNOSDB86))
        (ADDRESS= (PROTOCOL= TCP)(Host= 10.1.1.86)(Port= 1521))
  )

SID_LIST_COGNOSDB86 =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= C:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = COGNOSDB86)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
    )
  )

STARTUP_WAIT_TIME_COGNOSDB86 = 0
CONNECT_TIMEOUT_COGNOSDB86 = 10
TRACE_LEVEL_COGNOSDB86 = OFF

 

三、配置TNS

c$\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN的tnsnames.ora

COGNOSDB86 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = COGNOSTEST4.ai.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cognosdb86.ai.com)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )