本文介绍一些常用的数据库管理和维护命令。

##数据库相关操作命令

1.创建LMT 本地管理的表空间

CREATE TABLESPACE tbs_01 

   DATAFILE 'tbs_f2.dbf' SIZE 5M ; 

select file#, bytes, name from v$datafile where name like '%tbs_f2%';

>/u01/app/oracle/product/11.2.0/dbhome_1/dbs/tbs_f2.dbf

alter database datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/tbs_f2.dbf' resize 50 M;

或者

alter tablespace tbs_01 add datafile 'tbs_f3.dbf' size 45M;

2.创建Bigfile表空间

CREATE BIGFILE TABLESPACE bigtbs_01

  DATAFILE 'bigtbs_f1.dbf'

  SIZE 20M;

DROP TABLESPACE bigtbs_01 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE tbs_01 INCLUDING CONTENTS AND DATAFILES;

Refer：

Home / Database / Oracle Database Online Documentation 11g Release 2 (11.2) / Database Administration

Database SQL Language Reference

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7003.htm#SQLRF01403

>CREATE TABLESPACE

3.配置网络

cd $ORACLE_HOME/network/admin

/connect string：tnsnames.ora

testorcl =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SID = orcl)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl.us.oracle.com)

    )

  )

/listener：listener.ora

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = PCNAME.us.oracle.com)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

LISTENER2 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = PCNAME.us.oracle.com)(PORT = 1561))

    )

  )

SID_LIST_LISTENER2 =

(SID_LIST =

  (SID_DESC =

   (SID_NAME = orcl)

   (GLOBAL_DBNAME = orcl.us.oracle.com)

   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

  )

)

lsnrctl start LISTENER2

lsnrctl status LISTENER2

4.共享（Shared）和专有(dedicated)服务器连接

确认相关信息的方法

--进程数

$ ps -ef | grep oracle |wc -l

119

--连接服务器的内容

select username,server from v$session where username ='SYSTEM';

--共享服务器连接相关设定

SYS@TEST1 09-JAN-18> show parameter shared_servers

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_shared_servers                   integer

shared_servers                       integer     1

SYS@TEST1 09-JAN-18> show parameter dispatchers

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

dispatchers                          string      (PROTOCOL=TCP) (SERVICE=ORCLXDB)

max_dispatchers                      integer

alter system set shared_servers=2;

alter system set dispatchers='(PROTOCOL=TCP) (DISPATCHERS=2)'

SYS@orcl 09-JAN-18> show parameter shared_servers

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_shared_servers                   integer

shared_servers                       integer     2

SYS@orcl 09-JAN-18> show parameter dispatchers

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

dispatchers                          string      (PROTOCOL=TCP) (DISPATCHERS=2)

max_dispatchers                      integer

EZConnect方法：

  sqlplus system/oracle@11.11.11.11:1521/orcl.us.oracle.com

select username,server from v$session where username ='SYSTEM';

TNS的设定方法：

共有服务器接续：(SERVICE = shared)

专有服务器接续：(SERVER = dedicated) 默认

shared_orcl =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE = shared)

      (SERVICE_NAME = orcl.us.oracle.com)

    )

  )

$ sqlplus system/oracle@shared_orcl

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 9 06:16:05 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SYSTEM@shared_orcl 09-JAN-18> select username,server from v$session where sid in (select sid from v$mystat);

USERNAME                       SERVER

------------------------------ ---------

SYSTEM                         SHARED

5.密码的大小写区分控制参数sec_case_sensitive_logon

SYS@orcl 09-JAN-18> show parameter sec_case

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon             boolean     TRUE

SYS@orcl 09-JAN-18> conn hr/hr

Connected.

HR@orcl 09-JAN-18> conn HR/HR

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

@ 09-JAN-18> conn hr/HR

ERROR:

ORA-01017: invalid username/password; logon denied

@ 09-JAN-18> conn / as sysdba

Connected.

SYS@orcl 09-JAN-18> alter system set sec_case_sensitive_logon=false;

System altered.

SYS@orcl 09-JAN-18> show parameter sec_case

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon             boolean     FALSE

SYS@orcl 09-JAN-18> conn hr/HR

Connected.

HR@orcl 09-JAN-18> conn hr/hr

Connected.

HR@orcl 09-JAN-18>

6.password file 密码的大小不区分.

使用orapwd的ignorecase参数进行控制

SYSTEM@orcl 09-JAN-18> conn sys/oracle@orcl as sysdba

Connected.

SYS@orcl 09-JAN-18> conn sys/ORACLE@orcl as sysdba

ERROR:

ORA-01017: invalid username/password; logon denied

@ 09-JAN-18> !ls $ORACLE_HOME/dbs/

Warning: You are no longer connected to ORACLE.

$ rm $ORACLE_HOME/dbs/orapworcl

$ orapwd file=$ORACLE_HOME/dbs/orapworcl entries=30 password=oracle ignorecase=Y force=Y

@ 09-JAN-18> conn sys/oracle@orcl as sysdba

Connected.

SYS@orcl 09-JAN-18>  conn sys/ORACLE@orcl as sysdba

Connected.

$ rm $ORACLE_HOME/dbs/orapworcl

$ orapwd file=$ORACLE_HOME/dbs/orapworcl entries=30 password=oracle ignorecase=N force=Y

SYS@orcl 09-JAN-18> conn sys/oracle@orcl as sysdba

Connected.

SYS@orcl 09-JAN-18> conn sys/ORACLE@orcl as sysdba

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

参考：

Oracle® Database Administrator's Guide11g Release 2 (11.2) E25494-03

>Creating and Maintaining a Password File

##操作过程遇到的错误（踩到的坑）：

配置SID连接，tns错误：

tns:

testorcl =

(DESCRIPTION =

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

(CONNECT_DATA =

         (SID = orcl)

   )

)

[oracle@PCNAME-TEST1 admin]$ sqlplus system/oracle@testorcl

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 9 05:18:13 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied

$ tnsping testorcl

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 09-JAN-2018 05:24:10

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION =

TNS-12533: TNS:illegal ADDRESS parameters

解决：

修改tnsname配置如下：

testorcl =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SID = orcl)

    )

  )

[oracle@PCNAME-TEST1 admin]$ tnsping testorcl

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 09-JAN-2018 05:26:57

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))) (CONNECT_DATA = (SID = orcl)))

OK (0 msec)

[oracle@PCNAME-TEST1 admin]$ sqlplus system/oracle@testorcl

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 9 05:27:09 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SYSTEM@testorcl 09-JAN-18>