分类目录归档：Oracle

ORA-01031: insufficient privileges

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

今天接到开发反馈，说数据库动态sql创建表语句无法执行，提示ORA-01031: insufficient privileges
1、数据库版本
SQL> select * from v$version where rownum=1;

BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production

2、创建用户并授权
SQL> create user test identified by xifenfei;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

3、查看权限
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’TEST’;

GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE=’TEST’;

GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST CONNECT NO YES
TEST RESOURCE NO YES

SQL> CONN TEST/XIFENFEI
Connected.
SQL> SELECT * FROM SESSION_PRIVS;

PRIVILEGE
—————————————-
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER

PRIVILEGE
—————————————-
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

14 rows selected.
注：目前create table权限是通过resource role授予test用户的

4、创建存储过程（Authid Current_User）
SQL> create or replace procedure create_table(t varchar2) Authid Current_User
2 is
3 P_SQL VARCHAR2(300);
4 BEGIN
5 P_SQL := ‘create table ‘||t||’ as select * from user_tables where 1=0′;
6 EXECUTE IMMEDIATE P_SQL;
END; 7
8 /

Procedure created.

5、执行存储过程
SQL> EXEC CREATE_table(‘a’);

PL/SQL procedure successfully completed.

6、重建存储过程（不包括Authid Current_User）
SQL> create or replace procedure create_table(t varchar2)
2 –Authid Current_User
3 is
4 P_SQL VARCHAR2(300);
5 BEGIN
6 P_SQL := ‘create table ‘||t||’ as select * from user_tables where 1=0′;
7 EXECUTE IMMEDIATE P_SQL;
8 END;
9 /

Procedure created.

SQL> EXEC CREATE_table(‘b’);
BEGIN CREATE_table(‘b’); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “TEST.CREATE_TABLE”, line 7
ORA-06512: at line 1

7、授权（create table权限）
SQL> conn / as sysdba
Connected.
SQL> grant create table to test;

Grant succeeded.

SQL> conn test/xifenfei
Connected.
SQL> EXEC CREATE_table(‘c’);

PL/SQL procedure successfully completed.

8、查看结果
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
A TABLE
C TABLE

9、查询资料
默认情况下，在调用存储过程用户的角色是不起作用的，即在执行存储过程时只有Public权限。所以如果被调用的存储过程中如果有execute immediate ‘create table..’语句，将会引发ORA-01031: insufficient privileges错误。
存储过程分为两种，即DR(Definer’s Rights ) Procedure和IR(Invoker’s Rights ) Procedure。为什么会有两种存储过程呢？其实考虑完下面的问题就清楚了。比如说用户hrch创建了删除表tar_table的存储过程drop_table()，当用户hrch调用时，即删除用户hrch下的表tar_table；如果是另一个用户scott调用呢？是删除用户scott下的tar_table表呢，还是删除用户hrch下的tar_table呢？另外，如果存储过程中包含建表语句，不管是用户hrch还是用户scott调用都会失败，因为Public没有建表权限，除非为Public grant建表权限。

10、动态sql中执行create table解决方案（10g也存在同样问题）
1)加上Authid Current_User
2)直接赋予create table to 该用户

11、匿名块的动态sql不在此限制中
SQL> conn / as sysdba
Connected.
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’TEST’;

GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO

SQL> CONN TEST/XIFENFEI
Connected.
SQL> DECLARE
2 P_SQL VARCHAR2(300);
3 BEGIN
4 P_SQL := ‘create table t_t as select * from user_tables where 1=0′;
5 EXECUTE IMMEDIATE P_SQL;
6 END;
7 /

PL/SQL procedure successfully completed.

发表在 ORA-xxxxx | 评论关闭

ASM迁移至文件系统

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

--创建pfile文件
SQL> create  pfile ='/tmp/pfile' from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

--修改pfile中关于asm中的内容
control_files
db_recovery_file_dest
log_archive_dest_1
指定到文件系统

--登录rman
[oracle@localhost tmp]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 12:48:26 2011

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

connected to target database: TOS (DBID=1569606545)

--执行backup as copy datafile
RMAN>  backup as copy  datafile '+DATA/tos/datafile/users.276.754906035' format '/u01/oradata/tos/USERS01.dbf';

Starting backup at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DATA/tos/datafile/users.276.754906035
output filename=/u01/oradata/tos/USERS01.dbf tag=TAG20110627T124853 recid=17 stamp=754922939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 27-JUN-11

RMAN>  backup as copy  datafile '+DATA/tos/datafile/sysaux.271.754905929' format '/u01/oradata/tos/SYSAUX01.dbf';

Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+DATA/tos/datafile/sysaux.271.754905929
output filename=/u01/oradata/tos/SYSAUX01.dbf tag=TAG20110627T124929 recid=18 stamp=754923029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
Finished backup at 27-JUN-11

RMAN>  backup as copy  datafile '+DATA/tos/datafile/undotbs1.273.754906021' format '/u01/oradata/tos/UNDOTBS101.dbf';

Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+DATA/tos/datafile/undotbs1.273.754906021
output filename=/u01/oradata/tos/UNDOTBS101.dbf tag=TAG20110627T125049 recid=19 stamp=754923057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 27-JUN-11

RMAN>  backup as copy  datafile '+DATA/tos/datafile/system.270.754905833' format '/u01/oradata/tos/SYSTEM01.dbf';

Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DATA/tos/datafile/system.270.754905833
output filename=/u01/oradata/tos/SYSTEM01.dbf tag=TAG20110627T125112 recid=20 stamp=754923150
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
copying current control file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/27/2011 12:52:39
ORA-01580: error creating control backup file /u01/oradata/tos/SYSTEM01.dbf
ORA-27038: created file already exists
Additional information: 1
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 27-JUN-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/27/2011 12:52:42
ORA-19504: failed to create file "/u01/oradata/tos/SYSTEM01.dbf"
ORA-27038: created file already exists
Additional information: 1
注：因为默认情况下，备份system数据文件是，会自动备份控制文件，这里因为system01.dbf已经备份好，而控制文件再次备份为该名称所以失败

RMAN>  backup as copy  datafile '+DATA/tos/datafile/example.272.754905995' format '/u01/oradata/tos/EXAMPLE01.dbf';

Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+DATA/tos/datafile/example.272.754905995
output filename=/u01/oradata/tos/EXAMPLE01.dbf tag=TAG20110627T125341 recid=21 stamp=754923244
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 27-JUN-11

RMAN>  backup as copy  datafile '+DATA/tos/datafile/xff.274.754906027' format '/u01/oradata/tos/XFF01.dbf';

Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=+DATA/tos/datafile/xff.274.754906027
output filename=/u01/oradata/tos/XFF01.dbf tag=TAG20110627T125415 recid=22 stamp=754923257
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JUN-11

RMAN>  backup as copy  datafile '+DATA/tos/datafile/xff.275.754906031' format '/u01/oradata/tos/XFF02.dbf';

Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=+DATA/tos/datafile/xff.275.754906031
output filename=/u01/oradata/tos/XFF02.dbf tag=TAG20110627T125507 recid=23 stamp=754923309
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 27-JUN-11

RMAN> exit

Recovery Manager complete.

--登录sqlplus
[oracle@localhost tmp]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 27 12:55:29 2011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

--备份控制文件
SQL> alter database backup controlfile to '/tmp/control.ctl';

Database altered.

--关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

--启动数据库只nomount状态
SQL> startup pfile='/tmp/pfile' nomount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1260672 bytes
Variable Size              79692672 bytes
Database Buffers           79691776 bytes
Redo Buffers                7127040 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@localhost tmp]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 12:58:22 2011

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

connected to target database: tos (not mounted)

--恢复控制文件
RMAN> restore controlfile from '/tmp/control.ctl';

Starting restore at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=/u01/oradata/tos/control01.ctl
output filename=/u01/oradata/tos/control02.ctl
Finished restore at 27-JUN-11

--启动数据库只mount状态
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

--修改数据文件在控制文件中位置
RMAN> switch tablespace SYSTEM to copy;

Starting implicit crosscheck backup at 27-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 27-JUN-11

Starting implicit crosscheck copy at 27-JUN-11
using channel ORA_DISK_1
Crosschecked 15 objects
Finished implicit crosscheck copy at 27-JUN-11

searching for all files in the recovery area
cataloging files...
no files cataloged

datafile 1 switched to datafile copy "/u01/oradata/tos/SYSTEM01.dbf"

RMAN> switch tablespace UNDOTBS1 to copy;

datafile 2 switched to datafile copy "/u01/oradata/tos/UNDOTBS101.dbf"

RMAN>  switch tablespace SYSAUX to copy;

datafile 3 switched to datafile copy "/u01/oradata/tos/SYSAUX01.dbf"

RMAN> switch tablespace USERS to copy;

datafile 4 switched to datafile copy "/u01/oradata/tos/USERS01.dbf"


RMAN>  switch tablespace EXAMPLE to copy;

datafile 5 switched to datafile copy "/u01/oradata/tos/EXAMPLE01.dbf"

RMAN>  switch tablespace XFF to copy;

datafile 6 switched to datafile copy "/u01/oradata/tos/XFF01.dbf"
datafile 7 switched to datafile copy "/u01/oradata/tos/XFF02.dbf"

--恢复数据库
RMAN> recover database;

Starting recover at 27-JUN-11
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 9 is already on disk as file +DATA/tos/onlinelog/group_6.279.754906321
archive log filename=+DATA/tos/onlinelog/group_6.279.754906321 thread=1 sequence=9
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-11

--打开数据库
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/27/2011 13:00:36
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened
注：不能直接使用open打开

RMAN> exit


Recovery Manager complete.

[oracle@localhost tmp]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 27 13:02:53 2011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

--增加redo log
SQL> alter database add logfile group 1 '/u01/oradata/tos/redo01.log' size 10m;

Database altered.

SQL> alter database add logfile group 2 '/u01/oradata/tos/redo02.log' size 10m;

Database altered.

SQL> alter database add logfile group 3 '/u01/oradata/tos/redo03.log' size 10m;

Database altered.

--切换日志
SQL>  alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

--内存中数据写入硬盘
SQL> alter system checkpoint;

System altered.

--查询当前日志组状态
SQL>  select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE
         4 INACTIVE
         5 INACTIVE
         6 INACTIVE

6 rows selected.

--删除asm中日志
SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

--添加临时文件
SQL> alter tablespace temp add tempfile '/u01/oradata/tos/temp01.dbf' size 30m autoextend on maxsize 1g;

Tablespace altered.

--查看临时表空间中临时文件
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/tos/temp01.dbf
+DATA/tos/tempfile/temp.280.754906369

--删除asm中临时文件
SQL> alter tablespace temp drop tempfile '+DATA/tos/tempfile/temp.280.754906369';

Tablespace altered.

--查看迁移结果
SQL> set pagesize 100
SQL> select name from v$datafile
  2   union
  3   select member from v$logfile
  4   union
  5   select name from v$controlfile
  6   union
  7   select name from v$tempfile;

NAME
------------------------------------------------------------------
/u01/oradata/tos/EXAMPLE01.dbf
/u01/oradata/tos/SYSAUX01.dbf
/u01/oradata/tos/SYSTEM01.dbf
/u01/oradata/tos/UNDOTBS101.dbf
/u01/oradata/tos/USERS01.dbf
/u01/oradata/tos/XFF01.dbf
/u01/oradata/tos/XFF02.dbf
/u01/oradata/tos/control01.ctl
/u01/oradata/tos/control02.ctl
/u01/oradata/tos/redo01.log
/u01/oradata/tos/redo02.log
/u01/oradata/tos/redo03.log
/u01/oradata/tos/temp01.dbf

13 rows selected.

--创建spfile文件
SQL> create spfile from pfile='/tmp/pfile';

File created.

发表在 Oracle ASM | 2 条评论

create spfile to asm

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

--查看sid
SQL> show parameter instance_name  ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      tos

--创建pfile
SQL> create pfile='/tmp/tospfile' from spfile;

File created.

--创建spfile in asm
SQL> create spfile='+data' from pfile='/tmp/tospfile';

File created.

--查看spfile name in asm
ASMCMD> pwd
+data/tos/parameterfile
ASMCMD> ls
spfile.282.754913039

--编辑pfile内容（如果有该文件，先删除/重命名）
[oracle@localhost ~]$ vi  $ORACLE_HOME/dbs/inittos.ora
#内容为
spfile='+data/tos/parameterfile/spfile.282.754913039'

--重命名spfile文件
[oracle@localhost dbs]$ mv spfiletos.ora spfiletos.ora_bak

--重启数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1260672 bytes
Variable Size              75498368 bytes
Database Buffers           83886080 bytes
Redo Buffers                7127040 bytes
Database mounted.
Database opened.

--查看spfile
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/tos/parameterfile/spfile
                                                 .282.754913039

发表在 Oracle ASM | 评论关闭

17813235971

QQ 咨询

分类目录归档：Oracle

ORA-01031: insufficient privileges

ASM迁移至文件系统

create spfile to asm

文章分类

最近发表

分类目录归档：Oracle

ORA-01031: insufficient privileges

ASM迁移至文件系统

create spfile to asm

标签云

文章分类

最近发表