Oracle雕虫小技集(一)
1. Oracle数据文件丢失导致启动停止不了解决办法：
SQL> startup mount;
SQL> alter database datafile '/dev/datafile/rsdp01_ib_conf_data_0001' offline drop;
SQL> recover database;
SQL> alter database open;
2. 用sqlplus连接其它单板Oracle数据库命令：
SQL> conn user/password@IP/ORACLE_SID;
如: SQL> conn sysdb/sysdb@10.71.116.110/mdspdb;
3. 表空间使用率查询:
用sysdba登录数据库执行:
select a.tablespace_name "tablespace", a.file_name "physical device file",trunc(a.total) "allocated
(M)",ceil((1-b.free/a.total)*100) "Usage %",
trunc(b.free) " free space(M)" from
(select tablespace_name,file_name, sum(nvl(bytes,0))/1024/1024 total from dba_data_files group
by tablespace_name,file_name,file_id) a,
(select tablespace_name, sum(nvl(bytes,0))/1024/1024 free from dba_free_space group by
tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 3 desc;
4. 创建表空间及扩充表空间语句：
1). 创建新表空间:
create tablespace model_tablespace datafile '/opt/oracle/oradata/seedb/model_tablespace.dbf' size
30g reuse;
表空间数据文件大于32G时,需要增加BigFile参数才能创建成功:
create BigFile tablespace model_tablespace datafile '/opt/oracle/oradata/seedb/model_tablespace.dbf'
size 60g reuse;
2). 增加表空间大小:
ALTER TABLESPACE tablespacename ADD DATAFILE '/dev/datavg01/rdata8g_007' SIZE xxxxxM;
5. 表空间数据文件移动位置：
1).alter tablespace tablespace_name offline;
2).os下copy /home/oracle/oradata/datafilename to /opt/oracle/oradata/datafilename;
3).sqlplus执行:
alter tablespace tablespace_name rename datafile '/home/oracle/oradata/datafilename' to
'/opt/oracle/oradata/datafilename';
recover datafile '/opt/oracle/oradata/datafilename';
alter tablespace tablespace_name online;
Oracle雕虫小技集(二)
1. Oracle数据库最大连接数和当前用了的连接数查询：
1).查询和修改processes的值：
sqlplus / as sysdba
SQL> show parameter processes
SQL> alter system set processes=1500 scope=spfile;
然后重启数据库实例；
2).当前使用的连接数个数统计:select count(*) from v$session;
2. 锁表进程清除：
1).select t1.sid,t1.serial#,t1.username,t1.logon_time from v$session t1,v$locked_object t2 where
t1.sid=t2.session_id order by t1.logon_time;
2).alter system kill session '1550,4'; //后面数字为查询到的sid+servial#
3)还是失败的话：
select spid,s.osuser,s.program from v$session s,v$process p where s.paddr=p.addr and s.sid =1550，
用root用户执行kill -9 spid删除进程。
3. 数据库多国语言字符集检查:
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1 --(Please check with the HQ, if this should be
"AL32UTF8")
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.1.0.6.0
4. 取消Oracle11g数据库用户密码180天过期默认设置:
1)、查看用户的proifle是哪个，一般是default：
sql>SELECT username,PROFILE FROM dba_users;
2)、查看指定概要文件（如default）的密码有效期设置：
sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND
s.resource_name='PASSWORD_LIFE_TIME';
如果查询结果为180天,则6个月后数据库密码过期，业务进程一旦重启会提示连接失败
3)、将密码有效期由默认的180天修改成“无限制”：
sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
修改之后不需要重启动数据库，会立即生效。
4)、修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示；
已经被提示的帐户必须再改一次密码，举例如下：
$sqlplus / as sysdba
sql> alter user smsc identified by <原来的密码> ----不用换新密码
oracle11g启动参数resource_limit无论设置为false还是true，密码有效期都是生效的，所以必须通过以上方式进行修改。以上的帐户名请根据实际使用的帐户名更改。
5. 取消Oracle11g数据库用户输入密码错误次数最大为10次配置:
1)、查看用户的proifle是哪个，一般是default：
sql>SELECT username,PROFILE FROM dba_users;
2)、查看指定概要文件（如default）的密码有效期设置：
sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND
s.resource_name='FAILED_LOGIN_ATTEMPTS';
3)、将尝试登录失败次数由默认的10次修改成“无限制”：
sql>ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
4)、修改后，还没有被提示ORA-28000警告的用户不会再碰到同样的提示；
已经被锁定的帐户必须解除锁定，举例如下：
$sqlplus / as sysdba
sql> alter user smsc identified by oracle account unlock;
5)、修改后default profile应该如下：
sql> select * from dba_profiles WHERE dba_profiles.profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------- ------------- ----------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
修改之后不需要重启动数据库，会立即生效。
6. 让oracle随linux开机自动启动:
修改Oracle系统配置文件/etc/oratab
/etc/oratab 格式为： SID:ORACLE_HOME:AUTO
把AUTO域设置为 Y (大写)，只有这样，oracle 自带的dbstart和dbshut才能够发挥作用
在/etc/rc.local文件中加入
su - oracle -c "lsnrctl start"
