将360,000行导入到全局临时表中 [英] Importing 360,000 rows into a Global Temporary Table
问题描述
将数字列表导入到我创建的全局临时表中时遇到一些问题.
I'm having some issues importing a list of numbers into a Global Temporary Table that I created.
问题:将数据导入全局临时表时，即使出现成功消息提示，所需的列也不会显示任何内容.
The Problem: When importing data into a Global Temporary Table, nothing will show up in the desired column even though there is a success message prompt.
先前的努力:
我当前正在使用数据导入向导"方法来执行此操作.我尝试从.txt导入，但它给了我一个空错误.我尝试从.xlsx导入，但是它给了我Java堆空间错误.我尝试将.txt文件重命名为.tsv文件，并说它可以工作，但是没有导入任何实际数据.下图显示完成，但所需列中的最终结果计数仍为0.
I'm currently using the Data Import Wizard method to do this. I tried importing from a .txt, but it gave me a null error. I tried importing from .xlsx but it gives me the Java heap space error. I tried renaming my .txt file into .tsv file and it said it worked, but no actual data was imported. The Images below shows completion but the end result count in the desired column is still 0.
我以前尝试过的工作是使用SQL Developer在工作表中执行插入语句，但是，我一次只能复制并粘贴大约20,000行插入语句到工作表中.我不想多次复制和粘贴.但是，这样做会更新表并保留数据.
What I previously tried was doing insert statements inside of the worksheet using SQL Developer however, I can only copy and paste about 20,000 rows of insert statements into the worksheet at a time. I do not want to have to copy and paste multiple times. However, doing this it will update the table and preserve the data.
问题已得到解答.见下文.
推荐答案
默认情况下，将使用隐式on commit delete子句创建全局临时表:
By default a global temporary table is created with an implicit on commit delete clause:
create global temporary table tblbc (bc number);
Global temporary TABLE created.
select duration from user_tables where table_name = 'TBLBC';
DURATION
---------------
SYS$TRANSACTION
根据对话消息的说明，导入正在落实.这意味着数据将从GTT中删除，因为这就是设置的持续时间.这等效于:
The import is committing, as the dialogue message states. That means that the data is then deleted from the GTT, as that's what the duration is set to. That's the equivalent of doing:
insert into tblbc(bc) values (42);
1 row inserted.
commit;
Commit complete.
select * from tblbc;
no rows selected
如果您重新定义GTT，则至少在同一会话中，您将能够在该提交之后看到导入的数据:
If you redefine the GTT then you will be able to see the imported data after that commit, at least within the same session:
drop table tblbc;
Table TBLBC dropped.
create global temporary table tblbc (bc number) on commit preserve rows;
Global temporary TABLE created.
select duration from user_tables where table_name = 'TBLBC';
DURATION
---------------
SYS$SESSION
insert into tblbc(bc) values (42);
1 row inserted.
commit;
Commit complete.
select * from tblbc;
BC
----------
42
您现在可以再次将GTT中的数据与其他表进行比较.会话结束后，GTT数据将丢失.
You can now compare the data in the GTT with your other tables, again just within that session. Once you end the session the GTT data will be lost.
这篇关于将360,000行导入到全局临时表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
