Hi, experts
Currently I wanna create a test table TEST1 for doing something else on my oracle db server 11.2.0.4.0 for Linux x86_64.
At the same time I don't make sure whether creating table failed is related to the value (default 15) of the parameter arraysize of SQL*Plus.
The following are my some operation steps,
(1) on SQL*Plus,
SYS@test> show arraysize
arraysize 15
SYS@test> set arraysize
SP2-0267: arraysize option 0 out of range (1 through 5000)
(2) on SQLcl 18.1.1,
SQL> show arraysize
arraysize 15
SQL> set arraysize
SQLPLUS command failed - not enough arguments
SQL> set arraysize 5000
SQL>
SQL> show arraysize
arraysize 5000
SQL>
SQL> set arraysize 5001
SP2-0267: arraysize option 5001 out of range (1 through 5000)
SQL> set arraysize 5000
SQL>
SQL> show arraysize
arraysize 5000
SQL>
SQL> set timing on
SQL> show timing
timing ON
SQL>
SQL> create table test1
2 segment creation immediate
3 nologging
4 as
5 select rownum as id
6 , to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
7 , trunc(dbms_random.value(0, 100)) as random_id
8 , dbms_random.string('x', 20) random_string
9 from dual
10 connect by level <= 100000000
11 ;
Error starting at line : 1 in command -
create table test1
segment creation immediate
nologging
as
select rownum as id
, to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
, trunc(dbms_random.value(0, 100)) as random_id
, dbms_random.string('x', 20) random_string
from dual
connect by level <= 100000000
Error report -
ORA-30009: Not enough memory for CONNECT BY operation
Elapsed: 00:37:11.019
As you can see it has shown error ORA-30009 finally, could you help me for trouble-shooting it?
Thanks in advance.
Best Regards
Quanwen Zhao