Oracle 10g 临时表
我正在尝试将存储过程中使用的永久表转换为全局临时表。我查看了这些永久表的统计数据，有些永久表有数千万行数据，大小达到千兆字节（最多 10 GB）。
因此，
CREATE TABLE my_table (
column1 NUMBER,
column2 NUMBER,
etc...
)
TABLESPACE BIGTABLESPACE
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
应该成为
CREATE GLOBAL TEMPORARY TABLE my_table (
column1 NUMBER,
column2 NUMBER,
etc..
)
ON COMMIT PRESERVE ROWS;
我正在创建一个等效的全局临时表，其中的行应保留到每个现有永久表的会话结束为止。该全局临时表将在过程中使用，而不是永久表。
（EXECUTE IMMEDIATE 'TRUNCATE ...' 在开始时，以及 INSERT /*+ APPEND */ INTO 在稍后的某个时刻）
所有永久表都已在一个大表空间BIGTABLESPACE
Oracle 文档指出，全局临时表将在用户的临时表空间中创建（我假设这是TEMP）。这样做的问题是 TEMP 表空间很小，并且范围没有设置为增长到我需要它们在过程中增长的大小。
TEMP 表空间是在数据库创建期间创建的
create database "$oracle\_sid"
user sys identified by "$sys\_password"
user system identified by "$system\_password"
set default bigfile tablespace
controlfile reuse
maxdatafiles 256
maxinstances $maxinstances
maxlogfiles 16
maxlogmembers 3
maxloghistory 1600
noarchivelog
character set WE8MSWIN1252
national character set AL16UTF16
datafile
'$oracle\_home/oradata/$oracle\_sid/system01.dbf' size 512M
logfile
'$oracle\_home/oradata/$oracle\_sid/redo01.log' size 1G,
'$oracle\_home/oradata/$oracle\_sid/redo02.log' size 1G,
'$oracle\_home/oradata/$oracle\_sid/redo03.log' size 1G
sysaux datafile
'$oracle\_home/oradata/$oracle\_sid/sysaux01.dbf' size 256M
default temporary tablespace temp tempfile
'$oracle\_home/oradata/$oracle\_sid/temp01.dbf' size 5G
undo tablespace "UNDOTBS1" datafile
'$oracle\_home/oradata/$oracle\_sid/undotbs01.dbf' size 5G;
永久表（我计划替换的）最初是在表空间 BIGTABLESPACE 中创建的
-- 50G bigfile datafile size
create bigfile tablespace "BIGTABLESPACE"
datafile '$oracle\_home/oradata/$oracle\_sid/bts01.dbf' size 50G
extent management local
segment space management auto;
永久表索引最初是在表空间中创建的BIGTABLESPACE
-- 20G bigfile datafile size
create bigfile tablespace "BIGINDXSPACE"
datafile '$oracle\_home/oradata/$oracle\_sid/btsindx01.dbf' size 20G
extent management local
segment space management auto;
- 用全局临时表替换这些永久表是否可行？
- TEMP 表空间在扩展 TEMP 表空间时会遇到问题。有没有办法在表空间
BIGTABLESPACE和BIGINDXSPACE中创建全局临时表及其索引？ - 如果没有，如何使 TEMP 表空间表现得像大文件表空间并实现索引/表分离？
- 我可以创建两个 TEMP 大文件表空间并在其中一个表空间中创建索引，在另一个表空间中创建表吗？
我想使用全局临时表，但我在过程中处理的数据量似乎超出了全局临时表的预期设计。 有什么建议吗？
I'm trying to convert the permanent tables used in a stored procedure to global temp tables. I've looked at the stats on these permanent tables and some have tens of millions of rows of data and are on the order if gigabytes in size (up to 10 GB).
So,
CREATE TABLE my_table (
column1 NUMBER,
column2 NUMBER,
etc...
)
TABLESPACE BIGTABLESPACE
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
should become
CREATE GLOBAL TEMPORARY TABLE my_table (
column1 NUMBER,
column2 NUMBER,
etc..
)
ON COMMIT PRESERVE ROWS;
I'm creating an equivalent global temporary table with rows that should be preserved until the end of the session for each existing permanent table. This global temp table will be used in the procedure instead of the permanent table.
(EXECUTE IMMEDIATE 'TRUNCATE ...' at the start, and INSERT /*+ APPEND */ INTO at some later point)
All of the permanent tables have been created in a big tablespace BIGTABLESPACE
The Oracle docs state that the global temporary table will be created in the user's temp tablespace (I assume this is TEMP). The problem with this is that the TEMP tablespace is small and the extents are not set to grow to the size I need them to grow during the procedure.
The TEMP tablespace was created during the database creation
create database "$oracle\_sid"
user sys identified by "$sys\_password"
user system identified by "$system\_password"
set default bigfile tablespace
controlfile reuse
maxdatafiles 256
maxinstances $maxinstances
maxlogfiles 16
maxlogmembers 3
maxloghistory 1600
noarchivelog
character set WE8MSWIN1252
national character set AL16UTF16
datafile
'$oracle\_home/oradata/$oracle\_sid/system01.dbf' size 512M
logfile
'$oracle\_home/oradata/$oracle\_sid/redo01.log' size 1G,
'$oracle\_home/oradata/$oracle\_sid/redo02.log' size 1G,
'$oracle\_home/oradata/$oracle\_sid/redo03.log' size 1G
sysaux datafile
'$oracle\_home/oradata/$oracle\_sid/sysaux01.dbf' size 256M
default temporary tablespace temp tempfile
'$oracle\_home/oradata/$oracle\_sid/temp01.dbf' size 5G
undo tablespace "UNDOTBS1" datafile
'$oracle\_home/oradata/$oracle\_sid/undotbs01.dbf' size 5G;
The permanent tables (that I'm planning to replace) were originally created in tablespace BIGTABLESPACE
-- 50G bigfile datafile size
create bigfile tablespace "BIGTABLESPACE"
datafile '$oracle\_home/oradata/$oracle\_sid/bts01.dbf' size 50G
extent management local
segment space management auto;
The permanent table indexes were originally created in tablespace BIGTABLESPACE
-- 20G bigfile datafile size
create bigfile tablespace "BIGINDXSPACE"
datafile '$oracle\_home/oradata/$oracle\_sid/btsindx01.dbf' size 20G
extent management local
segment space management auto;
- Is replacing these permanent tables with global temporary tables feasable?
- The TEMP tablespace will run into a problem extending the TEMP tablespace. Is there a way to create global temporary tables and their indexes in tablespaces
BIGTABLESPACEandBIGINDXSPACE? - If not, how can I make the
TEMPtablespace behave like a bigfile tablespace and achieve index/table separation? - Can I create two
TEMPbigfile tablespaces and create indexes into one and tables into another?
I want to use global temporary tables, but the volume of data I am handling in the procedure would seem to be above and beyond the indended design of global temporary tables.
Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
将数据和索引分离到单独的表空间中除了可能使 DBA 更习惯将相似的对象分组在一起之外，没有任何好处。有一个长期存在的神话认为，出于性能原因，分离索引和数据是有益的，但这是不正确的。
临时对象应该（并且必须）存储在临时表空间中。如果您想将这些大型临时表分隔到单独的表空间中，则可以增加 TEMP 表空间的大小，或者仅为将拥有这些对象的用户创建单独的临时表空间。您不能（也不想）将它们存储在永久表空间中。
不过，从架构上来说，我很好奇为什么系统中需要临时表。如果您的会话将 10 GB 写入临时表，然后可能再次读取这 10 GB 以便将数据写入其他地方，我倾向于怀疑是否有更有效的解决方案。在 Oracle 中甚至很少需要临时表——这在其他数据库中更为常见，在这些数据库中，读取器可以阻止写入器在处理数据之前需要将数据从表中复制出来。 Oracle 没有这样的限制。
There is no benefit to separating data and indexes into separate tablespaces other than potentially making DBAs more comfortable that similar objects are grouped together. There is a long-standing myth that separating indexes and data was beneficial for performance reasons-- that is not correct.
Temporary objects should (and must) be stored in a temporary tablespace. You could increase the size of your TEMP tablespace or create a separate temporary tablespace just for the user(s) that will own these objects if you wanted to segregate these large temporary tables into a separate tablespace. You can't (and wouldn't want to) store them in your permanent tablespaces.
Architecturally, though, I would be very curious about why temporary tables were necessary in your system. If you have sessions that are writing 10's of GB into temporary tables, then presumably reading those 10's of GB out again in order to write the data somewhere else, I would tend to suspect that there were more efficient solutions. It is very rare in Oracle to even need temporary tables-- it is far more common in other databases where readers can block writers to need to copy data out of tables before working on it. Oracle has no such limitations.
我不认为你的描述中存在任何让 GTT 没有吸引力的地方。显然，您需要非常大的临时表空间，但总体上您不会消耗更多空间，除非您大量使用表压缩（在至少高达 10gR2 的 GTT 中不可用）。查看表空间组的使用： http: //download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#ADMIN01103
另外，不要忽视子查询分解子句的使用。它们通常可以替代临时表的使用。然而，它们可能仍然需要同样多的临时存储空间，因为来自 SQFC 的大型结果集可能会溢出到磁盘以避免消耗太多内存，因此您仍然必须继续增加 TEMP 空间。它们非常方便，因为不必每次需要新的临时表时都部署新的数据库对象。
I don't think that there's anything in your description that makes GTT's unattractive. You obviously need very large temporary tablespaces but you're not consuming more space overall unless you've been making heavy use of table compression (unavailable in GTT's at least up to 10gR2). Look into the use of tablespace groups: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#ADMIN01103
Also, don't neglect the use of subquery factoring clauses. They can often replace the use of temporary tables. However they might still require just as much temporary storage space because a large result set from a SQFC can spill to disk to avoid the consumption of too much memory, so you still have to go ahead with the increase in TEMP space. They're very handy for not having to deploy a new database object every time you need a new temporary table.
我查看了大型全局临时表以进行迁移练习。它有效，但为了调试和拒绝处理，我最终选择了普通表格。
如果 GTT 不起作用，请考虑行级安全性/VPD（甚至视图）。
您可以拥有从 sys_context('USERENV','SESSIONID') 派生的列，并使用它来确保用户只能看到自己的数据。
尽管如此，多个会话同时处理数千兆字节的数据集的想法还是有点可怕。
附言。我相信，对于通过过程使用的 GTT，请使用会话用户的临时表空间，而不是过程所有者的临时表空间。如果您可以作为单独的 Oracle 用户获取会话，那么您就有机会将文件 IO 分布在不同的表空间上。
I looked at large sized Global Temporary Tables for a migration exercise. It worked but for debugging and rejection hadling I eventually went with plain tables.
If the GTTs don't work out, consider either Row-Level Security / VPD (or even views).
You can have a column derived from sys_context('USERENV','SESSIONID') and use that to ensure that the user can only see their own data.
Still the thought of multiple sessions dealing with multi-gigabyte datasets concurrently is a bit scary.
PS. I believe that for GTTs used through a procedure use the temp tablespace of the session user rather than the temp tablespace of the procedure owner. If you can get the sessions as separate oracle users then you have a chance at spreading your file IO over different tablespaces.