Can't create tables in Oracle XE 10g 10.2.0.1.0

[GoogleCodeExporter]

Hi,

I tried to create tables in Oracle XE 10g 10.2.0.1.0. I got a lot of errors 
:

- first, you should document that UTL_SMTP is needed. I had to grant 
execute privilege to public :
I connected to XE as "sys as sysdba" and I typed this command :
 grant execute on sys.UTL_SMTP to public;

- second, there is error in gen_jiffy_base_schema_oracle.sh :
--- gen_jiffy_base_schema_oracle.sh.orig        2009-08-12 
16:10:32.000000000 +0200
+++ gen_jiffy_base_schema_oracle.sh     2009-08-12 16:10:38.000000000 +0200
@@ -242,7 +242,7 @@
     for trec in (
                select (
                        decode(rownum,1,
-                               'create or replace viewSs MEASUREMENT_VIEW 
as',
+                               'create or replace view MEASUREMENT_VIEW 
as',
                    '  union all')
                        || ' select * from ' || table_name) sqlst
         from user_tables where table_name like 'MEASUREMENT_2%')
-bash-3.2$ diff -u gen_jiffy_base_schema_oracle.sh.orig 
gen_jiffy_base_schema_oracle.sh
--- gen_jiffy_base_schema_oracle.sh.orig        2009-08-12 
16:10:32.000000000 +0200
+++ gen_jiffy_base_schema_oracle.sh     2009-08-12 16:10:38.000000000 +0200
@@ -242,7 +242,7 @@
     for trec in (
                select (
                        decode(rownum,1,
-                               'create or replace viewSs MEASUREMENT_VIEW 
as',
+                               'create or replace view MEASUREMENT_VIEW 
as',
                    '  union all')
                        || ' select * from ' || table_name) sqlst
         from user_tables where table_name like 'MEASUREMENT_2%')


- third issue, I tried to execute call_jiffy_manage_tables.sql and I found 
that with "system" user (even as "sys as sysdba"), I don't have right to 
create trigger :

The following errors were encountered during table creation: 
ORA-01031: insufficient privileges

- I disabled trigger, and then after some time, I got this error :
The following errors were encountered during table creation: 
ORA-01658: unable to create INITIAL extent for segment in tablespace 
JIFFY_DATA

(tables were created to MEASUREMENT_20090925 )

- I also made a small patch :
--- create_jiffy_manage_tables_proc.sql 2009-08-12 17:12:10.000000000 +0200
+++ create_jiffy_manage_tables_proc.sql.new     2009-08-12 
17:12:40.000000000 +0200
@@ -128,9 +128,9 @@

     EXCEPTION
       WHEN OTHERS THEN
-        l_subject := 'Subject: ERROR : Jiffy Table Creation Log' || 
l_crlf;
+        l_subject := 'Subject: ERROR : Jiffy VIEW Creation Log' || l_crlf;
         l_message := '***** ERROR ****' || l_crlf || l_crlf ||
-                     'The following errors were encountered during table 
creation: ' || l_crlf ||
+                     'The following errors were encountered during View 
creation: ' || l_crlf ||
                      SQLERRM || l_crlf || l_crlf || '***** ERROR ****' || 
l_crlf || l_crlf || l_message;

   end;


So, I'm blocked and since I'm not an Oracle expert, I don't know how to fix 
it.


Thanks

Original issue reported on code.google.com by [email protected] on 12 Aug 2009 at 3:14

[GoogleCodeExporter]

On the third issue, where the user doesn't have privileges to create a trigger, 
the call_jiffy_manage_tables.sql script connects as the jiffy user before 
executing the jiffy_manage_tables procedure, so that's the user who needs the 
"create trigger" privilege granted.

Of course there are more errors beyond that one, but this should clarify that 
bit.

Original comment by [email protected] on 2 Jul 2011 at 4:20

Attachments:

• grant_create_trigger.patch

[GoogleCodeExporter]

The fourth issue can be fixed by creating tablespaces using the "AUTOEXTEND ON" 
setting.

Original comment by [email protected] on 2 Jul 2011 at 4:54

Attachments:

• autoextend_on.patch

[GoogleCodeExporter]

Here are all my fixes combined.  I also added an option to specify an SMTP 
server hostname.

Original comment by [email protected] on 2 Jul 2011 at 6:10

Attachments:

• 20110701_db.patch