分类目录归档：数据库

创建视图提示ORA-01031

发表于 2011 年 12 月 9 日由惜分飞

1、重现问题

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select table_name from user_tables where table_name in('X_T','DEPT');

TABLE_NAME
------------------------------
X_T

SQL> create view  v_xff1
  2  as 
  3  select * from X_T;

View created.


SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;
SELECT * FROM SCOTT.DEPT
                    *
ERROR at line 2:
ORA-01031: insufficient privileges


SQL> SELECT COUNT(*) FROM SCOTT.DEPT;

  COUNT(*)
----------
         4

通过上面的试验证明：
1）在同一个schema下，有查询权限，就可以创建视图
2）在不同schema下，即使有了查询权限，创建视图，还是会提示ORA-01031

2、查看相关权限情况

SQL> select * from SESSION_PRIVS where 
  2  PRIVILEGE in('SELECT ANY TABLE','CREATE ANY VIEW','CREATE VIEW');

PRIVILEGE
----------------------------------------
SELECT ANY TABLE
CREATE VIEW
CREATE ANY VIEW

SQL> SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE NOT IN(
  2  SELECT PRIVILEGE
  3    FROM ROLE_SYS_PRIVS
  4   WHERE ROLE IN(SELECT * FROM SESSION_ROLES));

no rows selected

通过上面权限查询得出：用户所具有的select 其他用户表的权限是用过role授权

3、单独授于select权限

SQL> conn / as sysdba
Connected.
SQL> grant select on SCOTT.DEPT to xff;

Grant succeeded.

SQL> conn xff/xifenfei
Connected.
SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;

View created.

SQL> select view_name from user_views;

VIEW_NAME
------------------------------
V_XFF
V_XFF1

4、产生问题原因
In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role. The reason is that privileges granted to roles cannot be inherited via objects
ORA-1031 While Creating A View On A Table On Which The Select Privilege Is Granted Via A Role

发表在 ORA-xxxxx | 标签为 ORA-01031 | 评论关闭

删除Oracle数据文件/临时文件

发表于 2011 年 12 月 8 日由惜分飞

有些时候，想删除一个数据文件(临时文件),在10g之前的版本，要删除一个数据文件，必须删除该数据文件所属的表空间(特殊处理方法除外)。不太懂数据库的朋友直接os级别删除数据文件，导致数据库不能正常启动;稍微等点数据库的朋友，会先offline数据文件，然后os级别删除，但是这条数据文件的记录还保留在数据字典中，不爽(特殊处理办法见:清除离线数据文件记录)。在10g及其以后版本中，oracle提供了alter tablespace talbespace_name drop datafile/tempfile path/file_id进行删除某个数据文件

一、删除数据文件

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 8 11:22:38 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col tablespace_name for a20
SQL> col file_name for a50
SQL> set line 120
SQL> select file_id,file_name,tablespace_name 
   2 from dba_data_files order by tablespace_name;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         9 /opt/oracle/oradata/test/odu01.dbf                 ODU
        10 /opt/oracle/oradata/test/odu03.dbf                 ODU
         8 /opt/oracle/oradata/test/odu02.dbf                 ODU
         3 /opt/oracle/oradata/test/sysaux01.dbf              SYSAUX
         1 /opt/oracle/oradata/test/system01.dbf              SYSTEM
         2 /opt/oracle/oradata/test/undotbs01.dbf             UNDOTBS1
         5 /opt/oracle/oradata/test/user32g.dbf               USERS
         7 /opt/oracle/oradata/test/user02.dbf                USERS
         4 /opt/oracle/oradata/test/users01.dbf               USERS
         6 /opt/oracle/oradata/test/xifenfei01.dbf            XFF
        11 /opt/oracle/oradata/test/xifenfei03.dbf            XFF

11 rows selected.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 12:36 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:45 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:45 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 00:05 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 00:05 /opt/oracle/oradata/test/xifenfei01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/xifenfei03.dbf

SQL> alter tablespace xff drop datafile 11;
alter tablespace xff drop datafile 11
*
ERROR at line 1:
ORA-03262: the file is non-empty

SQL> col segment_name for a20
SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
  2  where file_id=11;

OWNER                          SEGMENT_NAME            FILE_ID     BLOCKS
------------------------------ -------------------- ---------- ----------
CHF                            XFF_TEST                     11          8
CHF                            XFF_TEST                     11        128
CHF                            XFF_TEST                     11        128
CHF                            T_XFF                        11        128
CHF                            T_XFF                        11        128

SQL> alter table chf.xff_test move tablespace users;

Table altered.

SQL> alter table chf.t_xff move tablespace users;

Table altered.

SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
  2  where file_id=11;

no rows selected

SQL> alter tablespace xff drop datafile 11;

Tablespace altered.

SQL> select file_id,file_name,tablespace_name 
  2  from dba_data_files order by tablespace_name;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         9 /opt/oracle/oradata/test/odu01.dbf                 ODU
        10 /opt/oracle/oradata/test/odu03.dbf                 ODU
         8 /opt/oracle/oradata/test/odu02.dbf                 ODU
         3 /opt/oracle/oradata/test/sysaux01.dbf              SYSAUX
         1 /opt/oracle/oradata/test/system01.dbf              SYSTEM
         2 /opt/oracle/oradata/test/undotbs01.dbf             UNDOTBS1
         4 /opt/oracle/oradata/test/users01.dbf               USERS
         7 /opt/oracle/oradata/test/user02.dbf                USERS
         5 /opt/oracle/oradata/test/user32g.dbf               USERS
         6 /opt/oracle/oradata/test/xifenfei01.dbf            XFF

10 rows selected.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 12:36 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:45 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:45 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:52 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:52 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:52 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:52 /opt/oracle/oradata/test/xifenfei01.dbf

二、删除临时表空间文件

SQL> alter  tablespace temp add tempfile '/opt/oracle/oradata/test/xff_temp02.dbf' size 10m;

Tablespace altered.

SQL> select file_id,file_name,tablespace_name from dba_temp_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         2 /opt/oracle/oradata/test/xff_temp02.dbf            TEMP
         1 /opt/oracle/oradata/test/temp01.dbf                TEMP

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 13:00 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:58 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:57 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:57 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 13:00 /opt/oracle/oradata/test/xff_temp02.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:57 /opt/oracle/oradata/test/xifenfei01.dbf

SQL> alter tablespace temp drop tempfile 2;

Tablespace altered.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 13:00 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:58 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:57 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:57 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:57 /opt/oracle/oradata/test/xifenfei01.dbf

三、注意事项
1、Oracle Database 10g Release 2及其以上版本
2、删除数据文件从数据库和系统
3、数据文件必须为空
4、数据文件不是对应表空间的最后一个
5、不能删除只读表空间中数据文件
6、不能删除system中数据文件
7、不能删除本地管理的offline表空间数据文件

发表在 Oracle | 标签为 ORA-03262 | 评论关闭

TNS-12525 TNS-12535 TNS-12606

发表于 2011 年 12 月 8 日由惜分飞

一、发现TNS-12525错误
检查公司服务器listener日志中周期性发现如下错误提示：

08-DEC-2011 02:25:34 * (CONNECT_DATA=(SID=ora9i)) * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=4877)) * establish * ora9i * 0
08-DEC-2011 02:25:35 * (CONNECT_DATA=(SID=ora9i)) * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=4879)) * establish * ora9i * 0
08-DEC-2011 02:25:43 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=2486)) * establish * <unknown sid> * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred
08-DEC-2011 02:26:02 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=4876)) * establish * <unknown sid> * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred

继续检查日志发现，只有211.155.236.234是外网ip，其他都是通过内网ip访问，都未报类此错误。初步怀疑是公司和运营商网络之间的防火墙导致

二、数据库版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

三、MOS说明

Symptoms
At regular intervals (every three hours), you see new connections rejected with TNS-12525 "listener has not received client's request in time allowed".
The listener log shows the following error stack :
10-APR-2008 17:39:15 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=10.221.50.31)(PORT=4184)) * establish * <unknown sid> * 12525 
  TNS-12525: TNS:listener has not received client's request in time allowed 
    TNS-12535: TNS:operation timed out 
      TNS-12606: TNS: Application timeout occurred

At the same time the firewall log reports :
"TCP packet out of state: First packet isn't SYN; tcp_flags: PUSH-ACK"

Wireshark (Ethereal) traces show the TCP 3-way handshake never completes for those connections.

Changes
Value of idle timer was recently changed at firewall level.

Cause
Firewall closes ports not allowing the 3-way handshake to complete at TCP level.

Solution
Increase the firewall's idle timeout interval.

New Connections Intermittently Rejected with TNS-12525

发表在 Oracle 监听 | 评论关闭

17813235971

QQ 咨询

分类目录归档：数据库

创建视图提示ORA-01031

删除Oracle数据文件/临时文件

TNS-12525 TNS-12535 TNS-12606

文章分类

最近发表

分类目录归档：数据库

创建视图提示ORA-01031

删除Oracle数据文件/临时文件

TNS-12525 TNS-12535 TNS-12606

标签云

文章分类

最近发表