I needed to load 100,000 rows of data from an excel file into a temporary table that I created using "on commit preserve rows". But somehow the most efficient methods did not seem to populate the temporary table due to session issues?
我需要从excel文件中将100,000行数据加载到我使用“on commit preserve rows”创建的临时表中。但不知何故,由于会话问题,最有效的方法似乎没有填充临时表?
I used Toad to Import Table Data and it showed that x amount of records are imported. But when I select from the temp table, it was empty. Then I generated a bunch of insert scripts and saved them in a notepad.sql and called it from toad editor using @/script/location/notepad.sql and hit F5. It ran and showed how many records were inserted. Again the temp table was somehow still empty. So, I decided to run a random insert script manually in the editor and it showed up in the temp table. I believe the methods that didn't work are not considered to be the same session?
我使用Toad导入表数据,它显示导入了x个记录。但是当我从临时表中选择时,它是空的。然后我生成了一堆插入脚本并将它们保存在notepad.sql中,并使用@ / script / location / notepad.sql从toad编辑器调用它并点击F5。它运行并显示插入了多少条记录。临时表再次以某种方式显示为空。所以,我决定在编辑器中手动运行一个随机插入脚本,它出现在临时表中。我认为不起作用的方法不被认为是同一个会话?
I haven't try SQLLDR but I am assuming it will not work judging from the methods I tried. Can someone confirm? I can't access SQLLDR so I won't know.
我没有尝试SQLLDR,但我认为它不会从我尝试的方法判断。有人可以证实吗?我无法访问SQLLDR,所以我不知道。
Is there anyway to get this to work? I can't run the insert scripts manually. That will be time consuming and Toad can't take that many scripts at the same time.
反正有没有让这个工作?我无法手动运行插入脚本。这将耗费时间,并且Toad不能同时使用那么多脚本。
1 个解决方案
#1
Oracle temp tables created with ON COMMIT PRESERVE ROWS are session-specific, so the data put into them is only visible within a single session, and for the duration of that session. Toad may be creating a separate session for each window and thus data which is populated from one window/session isn't visible from another window/session. The fact that you can run an insert script and then select the data back suggests this may be the case if both operations were done from the same window. I expect you'd see the same behavior if you used SQL*Loader to load the tables because the load would run in one session and the data would be discarded when the session terminated. Best of luck.
使用ON COMMIT PRESERVE ROWS创建的Oracle临时表是特定于会话的,因此放入其中的数据仅在单个会话中以及该会话期间可见。 Toad可能正在为每个窗口创建一个单独的会话,因此从一个窗口/会话填充的数据在另一个窗口/会话中是不可见的。您可以运行插入脚本然后再选择数据这一事实表明,如果两个操作都是从同一个窗口完成的,则可能就是这种情况。如果您使用SQL * Loader加载表,我希望您看到相同的行为,因为加载将在一个会话中运行,并且数据将在会话终止时被丢弃。祝你好运。
#1
Oracle temp tables created with ON COMMIT PRESERVE ROWS are session-specific, so the data put into them is only visible within a single session, and for the duration of that session. Toad may be creating a separate session for each window and thus data which is populated from one window/session isn't visible from another window/session. The fact that you can run an insert script and then select the data back suggests this may be the case if both operations were done from the same window. I expect you'd see the same behavior if you used SQL*Loader to load the tables because the load would run in one session and the data would be discarded when the session terminated. Best of luck.
使用ON COMMIT PRESERVE ROWS创建的Oracle临时表是特定于会话的,因此放入其中的数据仅在单个会话中以及该会话期间可见。 Toad可能正在为每个窗口创建一个单独的会话,因此从一个窗口/会话填充的数据在另一个窗口/会话中是不可见的。您可以运行插入脚本然后再选择数据这一事实表明,如果两个操作都是从同一个窗口完成的,则可能就是这种情况。如果您使用SQL * Loader加载表,我希望您看到相同的行为,因为加载将在一个会话中运行,并且数据将在会话终止时被丢弃。祝你好运。