ITPub博客

首页 > Linux操作系统 > Linux操作系统 > create table 出现Ora-00604 And Ora-18008 OUTLN用户不存在

create table 出现Ora-00604 And Ora-18008 OUTLN用户不存在

原创 Linux操作系统 作者:howard_zhang 时间:2011-03-25 10:17:41 0 删除 编辑

SQL> alter trigger MDSYS.SDO_ST_SYN_CREATE disable;

触发器已更改

SQL>
SQL> @c:\create_outln.sql
OUTLN CREATION SUCCESSFUL

PL/SQL 过程已成功完成。


用户已更改。

SQL> alter trigger MDSYS.SDO_ST_SYN_CREATE enable;

触发器已更改

create_outln.sql

set serveroutput on
DECLARE
user_exists EXCEPTION;
outln_user number;
outln_tables number;
extra_outln_tables number;
DDL_CURSOR integer;
BEGIN
select count(*) into outln_user from user$ where name='OUTLN';
select count(*) into outln_tables from obj$ where name in
('OL$', 'OL$HINTS','OL$NODES') and owner#=
(select user# from user$ where name='OUTLN');
select count(*) into extra_outln_tables from obj$ where name not in
('OL$', 'OL$HINTS','OL$NODES') and type#=2 and owner#=
(select user# from user$ where name='OUTLN');
DDL_CURSOR := dbms_sql.open_cursor;
IF outln_user = 0 THEN
dbms_sql.parse(DDL_CURSOR, 'create user outln identified by outln',
dbms_sql.native);
dbms_sql.parse(DDL_CURSOR,
'grant connect, resource, execute any procedure to outln',
dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create table outln.ol$ ( '||
'ol_name varchar2(30), ' ||
'sql_text long, ' ||
'textlen number, ' ||
'signature raw(16), ' ||
'hash_value number, ' ||
'hash_value2 number, ' ||
'category varchar2(30), ' ||
'version varchar2(64), ' ||
'creator varchar2(30), ' ||
'timestamp date, ' ||
'flags number, ' ||
'hintcount number, ' ||
'spare1 number, ' ||
'spare2 varchar2(1000))', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create table outln.ol$hints ( '||
'ol_name varchar2(30), '||
'hint# number, '||
'category varchar2(30), '||
'hint_type number, '||
'hint_text varchar2(512), '||
'stage# number, '||
'node# number, '||
'table_name varchar2(30), '||
'table_tin number, '||
'table_pos number, '||
'ref_id number, '||
'user_table_name varchar2(64), '||
'cost FLOAT(126),'||
'cardinality FLOAT(126),'||
'bytes FLOAT(126),'||
'hint_textoff number, '||
'hint_textlen number,'||
'join_pred varchar2(2000),'||
'spare1 number, '||
'spare2 number, '||
'hint_string clob)', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create table outln.ol$nodes ( '||
'ol_name varchar2(30), '||
'category varchar2(30), '||
'node_id number, '||
'parent_id number, '||
'node_type number, '||
'node_textlen number, '||
'node_textoff number, '||
'node_name varchar2(64))', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$name '||
'on outln.ol$(ol_name)', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$signature '||
' on outln.ol$(signature,category)', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$hnt_num '||
' on outln.ol$hints(ol_name, hint#)', dbms_sql.native);
dbms_output.put_line('OUTLN CREATION SUCCESSFUL');
ELSE
IF outln_tables!=3 or extra_outln_tables!=0 THEN
dbms_output.put_line('ERROR - OUTLN USER ALREADY EXISTS');
RAISE user_exists;
ELSE
dbms_output.put_line('OUTLN CREATION SUCCESSFUL');
END IF;
END IF;
EXCEPTION
WHEN user_exists THEN
RAISE;
END;
/

alter user outln account lock;

问题解决~

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7882490/viewspace-690444/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-22

  • 博文量
    42
  • 访问量
    133196