SQL & PL/SQL :: Possible To Hold Data From Select Statement Without Temp Table Or MView?
Apr 27, 2010
Is it possible to hold the data from select statement without temp table or materialized view or view in oracle?
because my DBA does not give access to create temp table.but we are selecting the records from 3 different sql statement.
Example: inserting in temp table
a) insert into temp select empno,ename,sal from emp where sal>4000
b) insert into temp select empno,ename,sal from emp where dept=40
c) insert into temp select empno,ename,sal from emp where comm is null
while trying to refresh an materialized view.. oracle throws cannot extend temp table space error.. while starting to refresh mivew temp table space is empty but once refresh started temp tablespace is growing and throws cannot extend temp tablespace error,,,size of temp tablesapce is 200GB..when i monter the session it does an sort event of an table(ammt_pol_ag_comm).. only 4% of this sort event is completing after that it throws error bu occupying the entire 200 GB tabespace.. MView script below..
CREATE materialized VIEW ammv_agent_pol_persis_emas NoLogging Parallel 10 Build Immediate Refresh on demand With Primary Key AS
I run a query, takes 20 minutes or so, I traced it and can see no more then 20-30 mb of temp space required in the plan.
I developed it for use in a materialized view, however when I create the mview with the sql, the temp space required grows until it maxxes out. I increased the existing 10gb to 50gb but still maxxed out. Took the SQL out, reran it, ran in 20 minutes barely scratching the temp, I ran a "create table as <select>" and same behaviour as the SQL, barely touched the temp as per the plan. So the temp space blolwing is unique to the mview create.
Im working with mviews years on several sites and have never seen this.
I need to import some data from .csv files. There is one file each day, so I want them to be automatically imported into the DB. This is the format it comes in:
so filename is the actual name of the .csv file that this row came from. And reading id is date, num1, num2, meterid combined. And the remaining fields coming from temp_table
i'm trying to use a collection in a select statement as a table, but i've got an error which i don't understand.
This exemple is very simple. I'm trying to validate the solution. The object will contain the result of a query from a big table, and i have 4 request to make on it. I don't want to select 4 times the big table to get the result, but i need the whole result to make my requests (intersect, minus and union)
CREATE OR REPLACE TYPE zy_w AS OBJECT( CODE_INFORMATIONVARCHAR2(4),
[Code]....
**************** Rapport d'erreur : ORA-06550: Ligne 12, colonne 3 : PL/SQL: ORA-00947: nombre de valeurs insuffisant ORA-06550: Ligne 5, colonne 3 : PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s: %s" *Cause: Usually a PL/SQL compilation error. *Action:
this is the correct syntax for updating a table with a select statement included. Table created easily and the alter table ran fine, but the update is running quite a log time.
My ultimate goal is to populate the "children" field with a count of children for each household id.
create table NON_GBC_Members nologging as select distinct hcp.household_master_ID from mrtcustomer.household_child_profile hcp where hcp.child_birth_dt between '31-OCT-2000' and '30-OCT-2011' group by hcp.household_master_id minus
Yesterday only I have Installed Oracle 11G & created DataBase - JafferDB And from Oracle SQL Developer, I have created a connection called - JafferCon and SID also given..and the role is - SYSDBA And I excute the below statement
insert into MyTable1 Values ('AAA1', 'BBB1', 'CCC1') insert into MyTable1 Values ('AAA2', 'BBB2', 'CCC2')
Then I checked by Select statement, it has shown the values....No Problem.... But, as a test, I deleted the connection and created a new connection tio the same DataBase with different name and when I checked by Select Statement....., it has not shown the values....?
In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.
But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger : -------
IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN INSERT INTO TEST.W_THRESHOLD_LOG (THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE) VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW); END IF; -------
In the above code if the hardcoded value 'Customer1' need to be picked from another table, i.e .
SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID
how can I rewrite my query to the above value from the select into my insert statement..?
I wanted to practice dataguard in oracle. I feel that I can practice realistically if I have 2 different servers to do switch overs, building new physical standby database from scratch and shipping the archiving logs from primary to physical standby database, etc.,
Can I use VMWare software to install 2 different servers and then practice dataguard?
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') 2 || ' User '||s1.username || '@' || s1.machine 3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text ||' is blocking the SQL statement on '|| s2.username || '@' 4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> ' 6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 , 7 v$session s2,v$sql sqlt1, v$sql sqlt2 8 WHERE s1.sid =l1.sid 9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1 10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2; [code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
select * from test_1 IDNameTotal ----------- 1A100 2B100 3C100 4D100
test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2. select * from test_2 ID ---- 1,2,3
My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.
SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)
create table test_1 (id number, name varchar2(100), total number) create table test_2(id varchar2(100)) insert into test_1 values (1,'A',100) insert into test_1 values (2,'B',100) insert into test_1 values (3,'C',100) insert into test_1 values (4,'D',100)
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.
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.
I have a table with multiple column. A column named address have value of multiple lines. Now i want to select the address only of the first line. How does it possible.
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
this huge report that uses inline views. I keep getting the following error message when running the script through toad. I was thinking about using the USE_HASH hints. The sql optimizer we use is very buggy in Toad. I'm using oracle database version 10.2.0.3.
I have a TEMP tablespace with autoextend on next 10M and maxsize 5120M, now my tablespace is 99.98% full. Am getting ORA-1652: unable to extend temp segment by 128 in tablespace temp error, can i use the method to increase the maxsize value to 10240M.
i work in an application that should make the replication from a publusher table to a remote subscribe table, using snapshot,and trigger, replication data of update works perfectly (update,insert,delete), but when i try to add or dropp a clumn in the publisher table, repplication fail, i know that my method d'ont replicate ddl statment like create or alter table, so i would like the better way to do the replication of the ddl statment without loosing tha data in the subscribe table, i'm working with oracle XE,
I am new to oracle designer, forms. The requirement is to select a csv file in a form ,read the file and load selected columns from a csv file into a table.
I am using CLIENT_TEXT_IO. I want to know how to extract the data from selected columns from csv file and insert into a table if the lenth of the columns are of variable length.
Another condition is that if there are duplicate rows based on orderid then take the maximum order seq nbr.Do I need to use temp table for this logic?
I wanna have a form which has a list box which is including of all table names, by selecting the required table by user all column name and its related data should be retrieve.I have a cursor which give me all field names related to the table by I do not know how can I retrieve table data:
1. How to add item through block base on number of column which selected table has.
2. How to set value of table column in these mentioned column which added in the block.
BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Solaris: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production
I have loaded into an Oracle table defined as XMLTYPE. I'm okay with PL-SQL and stored procedures, where I will ultimately do this processing once I get a handle on XML DB querying from an XMLTYPE table.
Based on reading the oracle documentation about XML DB, and more importantly, reading dozens of posts to this forum,I have been successful in loading the XML files into a SQL XMLTYPE table and doing simple queries against that table to retrieve some of its data so that I can then insert that data into other target oracle relational tables.
how to select parent/child data from XMLTYPE tables. I am able to follow the forum examples and can replicate the methods shown on the many example XML contents shown on this forum, but not against the XML that I have to process. I am wondering if my struggle is caused by my lack of knowledge, or by ill-formed XML content supplied to me by the educational vendor.The XML content has structured the XML content nodes in such a way that I do not seem to be able to apply the parent/child sql methods.I have been able to use for other XML examples I have tested against.
My XML file shown below represents High School Transcript data, for which I need to be able to parse out into my own oracle relational tables for that student, his personal info, and his course info, etc. i.e., for our example, which courses he has taken for which High School grade levels. The vendor-supplied XML seems to put the Courses and the High School grade level in "parallel nodes," instead of parent/child nodes, so I am struggling to be able to use SQL to differentiate which course the student took in NinthGrade versus TenthGrade.
-- WHat I would like to determine from a select statement:
LASTNAME GradeLevel COURSETITLE ============================= Smith NinthGrade PHYS ED 101 Smith TenthGrade CALCULUS 201 Smith TenthGrade ZOOLOGY 202
(The data has been simpliied and masked, but is true to the content and is queryable).
select * from V$VERSION
BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Solaris: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production [code]....
