create or replace p_aggop(dno number, maxi number,mini number,avgi number,tot number,cnt number) is cursor c1 is select * from emp1 WHERE DEPTNO=dno GROUP BY DEPTNO; v_emp1 c1%rowtype; begin open c1; loop fetch c1 into v_emp1; select min(v_emp1.sal),max(v_emp1.sal),avg(v_emp1.sal),sum(v_emp1.sal),count(*) into mini,maxi,avgi,tot,cnt from v_emp1; exit when c1%notfound; Dbms_output.put_line('EXECUTED '); end loop; dbms_output.put_line('The minimum salaray in dept is :'||v_emp1.mini); dbms_output.put_line('The maximum salaray in dept is :'||v_emp1.maxi); dbms_output.put_line('The average salaray in dept is :'||v_emp1.avgi); dbms_output.put_line('The total salaray in dept is :'||v_emp1.tot); dbms_output.put_line('The total members in dept is :'||v_emp1.cnt); CLOSE C1; end;
I am creating a stored procedure to create a table during run time using 3 passed in parameters to build the table name. Oracle is giving me the ORA-00922 missing or invalid operation error message on the EXECUTE IMMEDIATE statement of my stored procedure. I am new to writing stored procedures in Oracle. I am pasting a copy of the stored procedures. I have also changed the names of the field names to be generic so I can post the code in this forum.
CREATE OR REPLACE PROCEDURE createTable(PARAMETER1 in string, PARAMETER2 in string, PARAMETER3 in string) IS
TABLE_NAME NVARCHAR2(50); QUERY_STRING LONG; BEGIN
I am trying to create two tables and I am getting the "ORA-00911: invalid character" Error. I have been trying to work it out for last past 30 minutes to no success.
I got ORA-14074 error says that if you have a MAXVALUE already you will need to either drop the partition that encompasses the MAXVALUE or split partition.So I want to use split partition option through the below statement,
alter table tablename split partition partmax at (XXX) into (partition partXXX, partition partmax);
I am concerned about the existing data in the table will it by any change gets deleted, and what about indexes, do I have to rebuild, etc.I have to do this on produciton. I cannot test this as I don't have a test environment and moreover the table is having 70 million records and we don't have time/hardware resources to recreate this by export import in another database.
creating Oracle SQL query to fetch the information using PIVOT option.We are populating audit table using triggers. For every update, there will be two rows into audit table, one row with all OLD values and another with all NEW values. Also every updated is uniquely identified by Sequence No. Example for phone audit is mentioned below :
CREATE TABLE test_audit_phone ( emplid VARCHAR2(10), seqno NUMBER, action VARCHAR2(3), office NUMBER, mobile NUMBER );
Insert some rows into table.
INSERT INTO test_audit_phone VALUES ('100',1,'OLD',1111,9999) / INSERT INTO test_audit_phone VALUES ('100',1,'NEW',2222,9999) / INSERT INTO test_audit_phone VALUES ('100',2,'OLD',2222,9999) / INSERT INTO test_audit_phone VALUES ('100',2,'NEW',2222,8888) /
Table will look like the following :
SQL> SELECT * FROM sysadm.test_audit_phone ;
EMPLID SEQNO ACT OFFICE MOBILE ---------- ---------- --- ---------- ---------- 100 1 OLD 1111 9999 100 1 NEW 2222 9999 100 2 OLD 2222 9999 100 2 NEW 2222 8888
Now we have to present data in different format. For each field, display OLD and NEW values in column format.
create or replace function get_search_condition(p_cons_name in varchar2 ) return varchar2 authid current_user is l_search_condition user_constraints.search_condition%type; begin select search_condition into l_search_condition from user_constraints where constraint_name = p_cons_name; return l_search_condition; end;
and ran it...then when I write the below command:
alter table employee drop constraint select constraint_name from user_constraints where table_name = 'EMPLOYEE' and get_search_condition(constraint_name) like '%(( ROLL_NUMBER > 0 ))%' ;
The system throws:
Error starting at line 1 in command: alter table FL_ITEMSITE drop constraint select constraint_name from user_constraints where table_name = 'FL_ITEMSITE' and get_search_condition(constraint_name) like '%(( SS_QTY > 0 ))%' Error report: SQL Error: ORA-02250: missing or invalid constraint name 02250. 00000 - "missing or invalid constraint name" *Cause: The constraint name is missing or invalid. *Action: Specify a valid identifier name for the constraint name.
i want to drop constraint without specifying its name. i want the system to fetch it dynamically.
I have this requirement to pull the ACTIVE days that a participant holds true in a given month for a specific position he/she holds.The three date ranges here are: LOA dates(leave of absence), XFER dates(TRANSFER in/out of the position),Quality compliance(QUAL start/end dates).So,
INACTIVE DAYS = [(LOA + XFER + WW) - OVERLAPPING DAYS OF (LOA+XFER+QUAL)] CREATE TABLE PAYEES ( RUN_PERIOD DATE NOT NULL, PAYEEID VARCHAR2(20 BYTE) NOT NULL, LOA_START_DATE DATE, LOA_END_DATE DATE, [code]....
I am able to pull out the no of days on a individual basis(count of days for Xfer/LOA/Qual) but I am really not sure as to how should i go about determining the overlapping days between 3 date ranges to determine the ACTIVE/INactive days.
I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?
I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?
i want to create tablespace in raw partion on windows.I have added a hard disk and added extended partition to it. Then created 4 logical partitions of 256 MB with diskpart.exe.I have assigned a drive letter to one of them as X:
However, I am having trouble in creating tablespace on it. I referred to [URL]... I get following error,
C:UsersAdministrator>sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 17 15:32:17 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter user-name: SYSTEM Enter password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing opti
I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk
when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)
Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.
Can I add range sub partition to a hash partition table. Example like this.
CREATE TABLE test ( test_id VARCHAR2(10 ) , test_TYPE VARCHAR2(5) , CREATE_DATE date ) partition by hash (test_id, test_type) Partitions 3 SUBPARTITION BY RANGE (CREATE_DATE);
When Tried, I am getting syntax error as invalid option.
When I am trying to insert record from tbl_mittal into tbl_temp table. I am facing "ORA-14400: inserted partition key does not map to any partition" error
SQL> insert into tbl_temp select * from tbl_mittal; insert into tbl_temp select * from tbl_mittal * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition
AS tbl_mittal is having hugh number of records so I am providing only few rows from tbl_mittal table as test data.
How to find the size pf a partition in a partition table?I guess we need to query views like dba_tab_partitions but I am not very sure. will running dbms_stats.gather_table_stats('schema_name,'table_name,'partition_name')
What is the Best option for SQL/sybase server Temp table to use/for data manipulation (Insert/update/delete) inside the procedure in oracle Other then global temporary table.
Since we are porting from SQL/Sybase to Oracle we don't want to Create too many global temporary table.
I am trying out the COMPRESS option along with CREATE TABLE. just wanted to understand if we need the "Advanced Compression" option enabled. Does this require extra license?
I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?
I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?
l am loading data i.e text file of huge size into oracle 9i db using sqlldr through command prompt, but my table contains already some data so while loading it shows error as
SQL*Loader-601: For INSERT option, table must be empty.
i cant truncate my table since present data is important.
SQL> select SEGMENT_NAME, SEGMENT_TYPE, BLOCKS, EXTENTS, BYTES/1024 2 from user_segments 3 where SEGMENT_TYPE='TABLE' 4 ORDER BY SEGMENT_NAMe; [code]....
The countries table is missing in user_segments data dictionary view.But I can queries the countries using select statement.
SQL> SELECT * FROM COUNTRIES;
CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 [code]...
why the country table is missing in user_segments data dictionary table.
I am trying to add partition to table without partition
with following code
ALTER TABLE ACC_LOC1_TAB ADD PARTITION testpart BY RANGE (ALT_AUTHDT) (PARTITION UPTO_2010 values less than (31-mar-2010), PARTITION APR_JUN_10 VALUES less than (30-JUN-2010), PARTITION JUL_SEP_10 VALUES less than (30-SEP-2010), PARTITION OCT_DEC_10 VALUES less than (31-DEC-2010), PARTITION JAN_MAR_11 VALUES less than (31-MAR-2011))
I am using Oracle 11.2.0.1 Oracle Database.I have a table with 10 Million records and it's a Non Partitioned Table.
1) I would like to partition the table (with partition by range ) without creating new table . I should do it in the existing table itself (not sure DBMS_ REDEFINITION is the only option ) (or) can i use alter table ...?
2) Add one partition which will have data for the unspecified range.
i have table with range partition and list sub-partition..can i add one more list sub-partition if it is not possible , i have to drop first sub-partition.
SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE FROM user_constraints p, user_constraints c WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U') AND c.constraint_type = 'R' AND p.constraint_name = c.r_constraint_name AND c.table_name = UPPER('ODS_TSAF_MES_PC');