Environment is Oracle 11gR1 Enterprise Edition (non-RAC) on OEL 5.x.
The user has an assigned temporary tablespace SOME_TEMP which has autoextend on in increments of 1 MB with a maximum size of 5 GB.
User executes:
create table some_table as select * from some_view;
and error ORA-1652 is raised, "Unable to extend temporary tablespace SOME_TEMP".
This is the only user running any queries or statements in the database other than my monitoring session.
I alter the tablespace so it can increase unlimited removing the 5 GB max size. I then run
select count(*) from some_view;
The count is just under 249,000 rows and the query returns in only a few seconds.
I run the user's CTAS statement while periodically checking the size of SOME_TEMP. SOME_TEMP grows fairly quickly past 5 GB to 6, 7, 8, ..., 11 GB and still going after about 3 minutes.
I kill the CTAS statement after SOME_TEMP exceeds 11 GB ... using a view which returns less than 249,000 rows ... and no, the table is not wide enough to use this much temp space for the entire table many times over.
Previous versions of SOME_VIEW have been used to create SOME_TABLE and the CTAS typically completes in under 2 minutes without any errors.A new version of SOME_VIEW was used for the first time to recreate SOME_TABLE when ORA-1652 was raised.
The view returns a fixed number of rows fairly quickly so I think this rules out any logical, recursive errors or unwieldy Cartesian products in the view. what is causing the temporary space to quickly grow so large ?
I expect if I did not terminate the statement, the temporary space would grow until it consumed all available disk space on the system.
Now after the table got created, is there any place in the data base where the select query is stored using which the table got created? In brief, I would like to get the select query through which the table got created.
I need to recreate my table in order to change the column order. When I use the SQL Commands window and run the SQL, it runs successfully, creates the new table, but doesn't reorder the columns. Is this expected behavior?
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]....
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..
I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.
A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.
Is there another work around for this situation without having to work inside the data sources' environment?
We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.
This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?
I created a scenario- STEP1: Created a view- create or replace view view_01 as select object_name from dba_objects union all select object_name from dba_objects union all select object_name from dba_objects union all [code]....
View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.
I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.
EXEC DBMS_MVIEW.REFRESH('pro_mview','C');
But I am getting the below error.
* Error at line 1: ORA-12008: error in materialized view refresh path ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431 ORA-06512: at line 1
I am able to fetch the data from that materialized view(pro_mview).
what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?
I am using some UPS shipping software that imports shipping information based on an order number that the user enters and we are having performance problems. In the software's configuration you choose one database object (namely a table or view) to get the information from. I have created myView that selects from multiple tables, lookup tables, etc. I must use a view because the software only has the ability to add a " and <importKeyThatIHaveDefined> = <valueEnteredByUser>". For instance " and order_number = 123". It cannot join multiple tables, etc. The address_field1 info is the same for each record with the same order_number so I can just get the first row (or any row with order_number = <enteredOrderNumber> for that matter).
When I first looked at this it was created as something like this:
select order_number, line_number, order_number || '' || line_number as ol, address_field1 from order;Keep in mind that there are more than just this table and more fields selected. They are all joined correctly (for lookup values etc.).
The concatenation was determined to be the bottleneck in performance. ol was defined as the import key to be used and the users were just entering the order number plus a 1 to get ONLY the first line number. Each order (usually) has more than one line and even the line number is not necessarily unique for each order. For instance, order 123 may have line 1 twice and line 2 once.
select order_number, line_number, address_field1 from order where rownum = 1;
The software appended the " and order_number = 123" as expected but because rownum = 1 had already been executed (before the and order_number = 123" the view only returned one row even WITHOUT the order_number = 123.
I need to return only ONE row for a given order number EVEN IF there are multiple records with the same order_number and line_number. Using "where order_number = <enteredOrderNumber> and line_number = 1" is not necessarily unique so that won't work.If I can't make it work this way and can't use the rownum = 1 what options do I have?
I have a view ( from many tables) , an error view in wich i monitor errors that appear in my project. This view should be empt always but whenever a error occurs the view shows me this. I wanna put a trigger or something like that on that view to send me an e-mail whenever a line is inserted in that view. ( I don't wanna use a job for that to make a count because this will affect the entire database).
If you have one table called CUST_ACCOUNTS and a view called CUST_ACCOUNTS -- and then you want to select from the view and not the table.Is there a prefix for views or something that I can have in front of the name to specify it's the view I want data from?
Which is better to use of Views and Materialized views so as to have good performance ? Also I need to search for a particular value in the view. Any function in sql to implement this ?
View created properly, But when i select from this view this giving message missing right parenthesis. For information the select statement of the view gives multiple rows. Where is the problem.
I am working on this assignment question for class:
Write a SELECT statement that returns a single value that represents the sum of the largest unpaid invoices for each vendor (just one for each vendor). Use an inline view that returns MAX(invoice_total) grouped by vendor_id, filtering for invoices with a balance due
What I have coded so far is below SELECT i.vendor_id, To_char(SUM(invoice_total), '$9,999,999.99') AS sum_invoice_total FROM invoices i join (SELECT vendor_id,
[code]...
I am getting some results which I have attached. I'm not sure if I'm capturing the "largest unpaid invoices for each vendor" part of the question. Yielding to the knowledge the group and trying to use the proper posting etiquette. I have attached my output screen as well.
the execution steps of inline view?importance&advantage of inline view?.Also reference link where i can get more information about inline view(i.e. basics to advanced),exercises and interview questions.
I have requirement to create materialized views. The design states to use Complete refresh. Now I am using WITH ROW_ID clause.
1. Question - what are the criteria to decide between WITH PRIMARY KEY & WITH ROW_ID clauses? I referred oracle doc, but couldn't exactly get this.
2. Background - The oracle doc mentions that - Primary key materialized views are the default type of materialized view. The master table must contain an enabled primary key constraint, and the defining query of the materialized view must specify all of the primary key columns directly.
Question - I saw some existing materialized views in my project using WITH PRIMARY KEY clause, but all of the primary key columns are not part of the Select query, but still these views are working fine, how is this possible?
3. Background - The oracle doc mentions that - Rowid materialized views must be based on a single table and cannot contain any of the following:
■Distinct or aggregate functions ■GROUP BY or CONNECT BY clauses ■Subqueries ■Joins ■Set operations
Question - But I have created a Rowid materialized view selecting data from more than one table & defining query involving outer joins. This materialized view got created & getting refreshed (complete refresh). But again this is contradicting with Oracle' statement.
I've generally operated under the assumption that if I have a query that executes properly, I should be able to create a view from that same logic. However, I've had more than one occasion where this seems to NOT be the case.
Today, I have a query using ListAgg that operates just fine when run interactively, but (in SQL Developer v 3.0.04) it gives ORA-24344 when I try to create a view using the exact same statement.
