Server Administration :: Utilize Temp Tables As Part Of A Scheduled Job?
Jan 18, 2013
There is a readonly user on our reporting server. Developers want to use global temporary tables with this user. I don't want the user to have permissions other than readonly.I can grant the user CREATE TABLE privilege and did not grant quota on any permanent tablespace, therefore user would not be unable to create permanent tables but still should be able to create global temporary tables.
Question is would a user with such permissions still be able to utilize temp tables as part of a scheduled job?
View 2 Replies
ADVERTISEMENT
Jun 20, 2010
Is there any way to find the last run duration of a job which was scheduled using DBMS_JOB?
View 3 Replies
View Related
Jun 30, 2010
i got a error temp tablespace cannot be resized. How to Resize Temp Tablespace?
View 10 Replies
View Related
Oct 12, 2011
We have a database running with Oracle 10.2.0.4 on Solaris 5.10 [SUN SPARC v240] where the assigned TEMP tablespace size looks to be quite huge ( = 8GB!). There are not much SQL queries being run on the database, but what can I do/find/investigate to be sure of how much TEMP space is 'actually' required for the application to run OK?
Note. I have seen the previous DBA's have marked AUTOEXTEND = ON for the TEMP datafiles as well.
Separate query (but linked to TEMP tablespace):
Even if the TEMP datafiles are created with AUTOEXTEND = ON, sometimes I am seeing 'ORA-01652: unable to extend temp segment by 128'.
View 4 Replies
View Related
Jul 26, 2011
we have 5 tempfile ( each of 65 gb ) allocated to TEMP tablespace...and still we are running in short of space..when i checked the TEMP segment usage, i am able to see much FREE blocks. how to release those space ?
TABLESPACE_N FILE_ID FILE_NAME Size(MB)
------------ ---------- ------------------------------------------- ----------
TEMP 1 +DATA/tedw/tempfile/temp.3043.727779755 65535.9688
TEMP 2 +DATA/tedw/tempfile/temp.3042.727779749 65535.9688
TEMP 3 +DATA/tedw/tempfile/temp.3041.727779741 65535.9688
TEMP 4 +DATA/tedw/tempfile/temp.4065.730387401 65535.9688
TEMP 5 +DATA/tedw/tempfile/temp.4075.731586241 65535.9688SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024 as total_MB,
used_blocks*16/1024 as used_MB,
free_blocks*16/1024 as free_MB
FROM v$sort_segment;
TABLESPACE_N TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
------------ ------------ ----------- ----------- ---------- ---------- ----------
TEMP 9994624 1007360 8987264 156166 15740 140426
1 row selected.
further when i checked the session details using TEMP segment, i got below output:
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP 15001 3549184 576 475 1237 EQUIPMENT infa ACTIVE
TEMP 15001 4002368 64 796 4677 CRM infa ACTIVE
TEMP 15002 580608 20352 868 615 EDW infa ACTIVE
TEMP 15002 3962112 832 92 1065 EDWSTG infa ACTIVE
TEMP 15002 4021120 576 1236 7257 EQUIPMENT infa ACTIVE
TEMP 15003 23936 64 819 5586 EDW infa ACTIVE
TEMP 15003 3798400 832 855 1801 EDWSTG infa ACTIVE
TEMP 15004 205056 21632 795 8171 EDW infa ACTIVE
TEMP 15004 4031488 832 403 1299 EDWSTG infa ACTIVE
TEMP 15004 4131456 576 19 6802 EQUIPMENT infa ACTIVE
TEMP 15005 3617856 832 1166 6204 EDWSTG infa ACTIVE
TEMP 15005 3741760 576 862 953 EQUIPMENT infa ACTIVE
TEMP 15005 4042752 18176 1226 5379 CDM infa ACTIVE
3 rows selected.
if i killed the SID - 1226, then those temp blocks ( 18176 blocks ) will be released and can other session use that space further ?
there is one more column - SEGBLK#
explain what is the exact meaning of this column ?
to reclaim the space, should i issue below command -
sql>alter tablespace TEMP coalesce;
View 3 Replies
View Related
May 9, 2012
I am getting error "ORA-1652: unable to extend temp segment by 128 in tablespace" but i can see there is enough free space left in TEMP. I can see we have many active session. how can we drill down to solve this error. Below is the status for all the tablespaces.
Tablespace STA M A Init Total MB Free MB Used MB LrgstMB MaxExt %Fr A
------------ --- - - ---- ------------ ---------- ----------- -------- ------------ --- -
AMINDEX OLN L S 64K 5,120 5,120 0 3,968 2147483645 100
AMOWNER OLN L S 64K 5,120 5,120 0 3,968 2147483645 100
BOB_TS OLN L U 10M 17,240 16,130 1,110 9,740 2147483645 94
DDP_TAB OLN L U 1M 5,120 3,700 1,420 3,507 2147483645 72
[code]....
View 7 Replies
View Related
Mar 10, 2010
If the temp space left is 0%, i.e. all temp space used up, is it possible to make new DB connection ( can new users still connect to the DB)?
Or re-phrasing the question... How much of temp space (if at all ) is required for a new user to login to DB? Like SORT_AREA_SIZE in PGA. So, as memory sort area is already used ( Temp space is 100% full), can DB make more new connections?
View 7 Replies
View Related
Dec 1, 2011
I am having a very big Temp Tablespace with 4 tempfiles each of 32GB.Usually there is nightly run involving very big tables.
When i got the error ORA-1652:
unable to extend temp segment by 128 in tablespace TEMP
the temp usage was as follows
Tablespace name Total MB UsedMB FreeMB
TEMP 128818 100735 28083
Only 100GB was used and there was about 28GB free space in Temp.But still i was getting the error.Was it becos it was defragmented and was not able to find a contigous segment? Is it applicable for temp tablespace also..
View 6 Replies
View Related
Mar 10, 2012
When i try to create a duplicate table from an existing table i get error.
SQL> create table COMP_TEMP as select * from COMPETITIVE;
create table COMP_TEMP as select * from COMPETITIVE
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01
The table size is 15 gb.
Currently the tablespace has 2GB free space. If i need to increase the size of the tablespace DATA_01,how much space is required.
View 9 Replies
View Related
Nov 3, 2013
I have 3 pages: - Classes: a Classic Report that shows a list of classes and summarises attendance- Class Attendance Report: An Interactive Report that shows a row for each student that attended a class- Attendance Add / Edit Form On the Classes page, the Date value is a Link which passes a filter in the URL to the Class Attendance Report, showing only students who attended the class held on this date On the Class Attendance Report, I want to have a Region Button that, when clicked, goes to the Attendance Add / Edit Form and the Class Date field on this form is automatically populated with the same date value as was being used for the filter on the Interactive Report on the Class Attendance Report Page. So, I click on the Class Date link for 2013-11-01, this takes me to the Class Attendance Report and filters for students who have been already entered as attending on 2013-11-01. If I click on the "Add Attendance" button, it goes to the Attendance Add / Edit form and the Class Date is automatically set to 2013-11-01. I assume I need to do this via a Dynamic Action that runs when the "Add Attendance" button on the Class Attendance Report page is clicked?
View 6 Replies
View Related
Nov 2, 2012
I have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.
The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:
Select * From AddressSearch
Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;
But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?
View 2 Replies
View Related
Apr 13, 2011
I am using oracle 8.1.5 database and my temp01.dbf file size is increased upto 19.8 GB now i want reduce its size .
View 13 Replies
View Related
Jun 16, 2010
I am trying to use Global temporary tables, and index on this table to get my results faster. I can see even if I run any query on this table, it does full table scan and not Index scan..
create global temporary table abc_tab on commit preserve rows
as select a,b,c from xyz;
create index lmn on abc_tab(a,b,c)
View 10 Replies
View Related
Nov 23, 2012
Is it possible to create local temp variable based table within sql procedures, i am planning on writing few select queies,and want to store teh result of each select to a temp variable based table.
Within sql serevr, i can create temp tables within and then later after i use teh data at teh end drop the table.
Is it possible, since i don't want to create any physical tables.
View 1 Replies
View Related
Aug 20, 2010
I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
View 3 Replies
View Related
Dec 17, 2010
I have installed the Oracle 10g software and created Database my own in RHEL 4. i got the emp tables when i run @?/rdbms/admin/utlsampl.sql with main user system. After i created another user test and i tried to get the tables. but im getting error like table not exist.
View 11 Replies
View Related
Sep 2, 2010
I want to find out the columns on which primary constraints are enabled.Which view must I use to check this.
View 2 Replies
View Related
Mar 23, 2013
I have problem with moving old DB to the new (the same DB 10.2.0 in Win 2003, first in 32 bit, second in 64 bit). I want move DB from 32 to 64 bit. Problem is that all objects in old DB were created in SYSTEM schema by SYS. I can't export that objets (with data) because impdp nor imp don't touch this objects (tables with indexes). I can't use export import procedure. I'm looking for another method to transfer data, which will be the best and the fastest? Maybe files copy on OS? I suppose it will be problems with configuration files, database have other tablespaces.
View 13 Replies
View Related
Jun 14, 2001
I am trying to describe all the tables in a database.We use desc or describe tablename; to describe a table, but what is the command to describe all the tables in a database (i don't need the system tables)
Once i log into sqlplus as a say ABC (SID or HostString) as a user then if i do a desc table name i get column name, data type and null not null etc but i i need that for all the tables in that ABC database..
View 8 Replies
View Related
Dec 13, 2010
I have installed oracle 10g software and created Database on RHEL 4. when i run the run.sql its successively done but im not getting tables. whats the prob.
View 17 Replies
View Related
Feb 4, 2012
In an attempt to take older data off line and allow database refreshes to be faster, tablespaces associated with partitioned table data for a given time period was taken off line, leaving only tablespaces that relate to the current time period online. In effect, tablespaces related to 2010 and earlier were taken offline from a table.
1. Without giving a filter on the partition key (the business date) to scan for data greater than the dates in the off lined tablespace partition, we get a ORA-376/ORA-1110 error (data file cannot be read at this time).
2. Materialized views using fast refresh or refresh on commit, will also not work because of the partitions being off line.
Queries directly querying the tables are manageable from an application point of view.But the materialized views failing to aggregate is a bigger problem.
how we can manage this situation? I know that I can move the partitions to a different table in a tablespace to be taken off line. But if possible, we wanted to solve this without doing a move partition.
View 2 Replies
View Related
Aug 30, 2011
create a procedure or cursor to allocate extents to all tables with zero rows for all the user in the database.I have used the below query to check table with zero rows and no extents allocated.
select onwer,table_name,initial_extent
from dba_tables where initial_extent is null order by owner;
I generated the query to allocate extents by using concatenation in the above query.
select 'ALTER TABLE '||table_name|| ' ALLOCATE EXTENT; '
from dba_tables where initial_extent is null order by owner;
now I want the extent allocation for such table auutomatically for aal the tables with zero rows.
View 17 Replies
View Related
Aug 6, 2010
would want to check which tables located in which datafiles.
Tried with this command:
select B.FILE_NAME from dba_segments a, dba_data_files b
where a.header_file=b.file_ID and segment_name='TABLE_NAME'
Seems like it only shows 1 datafile. Some tables are large and i suppose it's resided in more than 1 datafile.
View 1 Replies
View Related
May 26, 2011
We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.
View 5 Replies
View Related
Sep 30, 2011
Is it possible to create trigger on the various tables and views exists (i.e. dynamic performance views) in data dictionary, when ever any DML operations performs by Oracle it self?
View 6 Replies
View Related
May 13, 2013
that are my biggest tables
Users use front end (called ESS Console) and when they try to open one of those tables they wait very long (really bad performance). Sometimes the GUI even hanging without displaying results.
Does Partitioned Tables feature works for better performance?
View 3 Replies
View Related
Jul 25, 2012
One of our customer have problem with following sql statement:
SELECT c.table_name, c.column_name
FROM user_tab_columns c, user_tables t
WHERE c.table_name = t.table_name
AND c.data_type IN ('CLOB', 'BLOB');
During execution it takes all the TEMP tablespace size(8GB).
I gather system stats (dbms_stats.gather_dictionary_stats(estimate_percent=>null)) but it doesn't resolve problem.Above sql statement works fine with RULE hint but I want to know what is the reason of problem with temporary tablespace.
View 10 Replies
View Related
Mar 7, 2011
I am trying to run on Oracle report via Oracle Application Concurrent job. Concurrent job is completing normal but I don't get anything on print out page. In log file of this request I see message 'MSG-01003: Errors =>ORA-01652: unable to extend temp segment by 128 in tablespace TEMP'. I almost doubled the TEMP tablespace in size but still I am not able to get rid of this error message.
View 1 Replies
View Related
Apr 19, 2010
The below query throws an error as mention below
My PGA_AGREGATOR_TARGET = 2GB
below query is given below.
RowsPlan
1SELECT STATEMENT
1 HASH JOIN
1 MERGE JOIN CARTESIAN
1 TABLE ACCESS BY INDEX ROWID WAT_SOURCE_DATA
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE INDX_WAT_SRC_DATA_BIT
[code]....
Error Message : ORA-01652:unable to extend temp segment by 128 in tablespace TEMP
Query :
SELECT OR004.wat_id "WAT_ID",
SYSDATE "DATE_FIRST_IDENTIFIED",
SYSDATE "DATE_LAST_IDENTIFIED",
'OR-004' "RULE_REFNO",
'RISK' "RULE_TYPE",
OR004.workspace_id "WORKSPACE_ID",
OR004.workspace_name "WORKSPACE_NAME",
[code]....
View 6 Replies
View Related
Jan 11, 2012
my user is trying to drop columns, but she gets below error:
SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
39726. 00000 - "unsupported add/drop column operation on compressed tables"
i just checked whether table is compressed or not, it is not compressed it seems:
select owner, table_name,COMPRESSION,COMPRESS_FOR from dba_tables
2 where owner = 'EQUIPMENT' AND TABLE_NAME = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
EQUIPMENT ETHRNT_VRTL_CNXN_HRLY_AGG_SWP DISABLED
1 row selected. i am able to set one column as UNUSED. and then i am able to see the count accordingly from below view:
select * from DBA_UNUSED_COL_TABS
where owner ='EQUIPMENT' and table_name = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
but not able to drop the unused columns. if i tried to drop a column directly from the table, that also giving above error.
View 7 Replies
View Related
