SQL & PL/SQL :: Grouping Sets / Dictionary Views

Aug 20, 2010

I'm having a problem with grouping sets over dictionary views.

10g output:
SQL> select
2 -- 10g results
3 segment_name,
4 round(sum(bytes/1024/1024),2) mb
5 from dba_segments

[Code]...

ERROR at line 10: ORA-03001: unimplemented feature

Elapsed: 00:00:00.12

The query is fine over a non-dictionary table however (My actual code isnt against dual, but this makes it generic, the error is consistent)

11g output
SQL> select
2 -- 11g results
3 segment_name,
4 round(sum(bytes/1024/1024),2) mb
5 from dba_segments

[Code]....

ERROR at line 8: ORA-00904: : invalid identifier

Different error and that syntax, as far as I can tell, is sound - error message be damned.

An example of a query working on both versions is

select
employee_id,
sum(salary)
from
employees
group by grouping sets ((employee_id),null)
;

Am I missing something? Does grouping sets not work over the dictionary views?

Edit: Added version tags over the code to make it easier to read

View 4 Replies


ADVERTISEMENT

SQL - Using Row Number Over Partition By - With Grouping Sets

Jan 2, 2013

I am trying to write a single SELECT statement that groups at 2 levels of aggregation (using grouping sets) and assigns row numbers (to rank each item) that are partitioned at the correct level for each grouping set. I have the grouping sets figured out but I can't find a way to make Partition By match each level of aggregation.

What I am looking for (in a single SELECT statement) is logically equivalent to:

SELECTweek
,region
,NULL as country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, region ORDER BY SUM(qty) DESC) as rownumFROM base
GROUP BY week
,region
,item
UNION ALL

SELECTweek
,NULL as region
,country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, country ORDER BY SUM(qty) DESC) as rownumFROM base
GROUP BY week
,country
,item

I hoped that I could do something like this:

SELECTweek
,region
,country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, GROUPING SETS (region, country) ORDER BY SUM(qty) DESC) as rownumFROM base
GROUP BY week
,GROUPING SETS (region, country)
,item

But it looks like I am not allowed to partition by grouping sets -- I get the error ORA-00907: missing right parenthesis. I didn't expect it to work but I am not sure how else to partition by multiple levels.

let me know if I could have tagged my code or met other forum standards better.

View 1 Replies View Related

List Only Date Columns In Data Dictionary / Views

Sep 6, 2012

Oracle version : 11.2.0.3

Some DBA_views are huge with lots of columns ; a DESC command's output becomes less readable ; example DESC command's output of V$SESSION view.

Lets say I want to see only the date columns in a DBA_view or V$view ; How can I do that in a DESC like output or similair ?

View 2 Replies View Related

Server Administration :: Possible To Write Triggers On Data Dictionary Tables And Views

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

Server Administration :: Graphical Analysis Of Dynamic Performance Views (V$ Views)?

Nov 5, 2012

is there some open source or free tool which can graphical display V$ Views. Can TOAD do that in a good maner?

in UNIX there is the "sar" command, but a Java tool "ksar" for displaying the statistics in user friendly fashion.

View 2 Replies View Related

Refresh Materialized Views Based On Remote Views?

Aug 4, 2011

I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.

Materialized view is created by

CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';

I am wondering how the refersh happens or how do I specify the refresh clause.REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.

View 1 Replies View Related

PL/SQL :: Get List Of Materialized Views / Views Using Column Of Table

Oct 17, 2013

I am removing sal column from table tab_emp; i want to check whether any materialized view or view using this column by  querying using data dictionary :- if i use like condition against query column of all_mviews it is throwing error sicne it is long data type. is there a way to search it without creating any function and use it in a query.

View 3 Replies View Related

Convert Some Existing Materialized Views (fast Refresh) To Partition Materialized Views

Jul 7, 2010

I have to convert some existing materialized views (fast refresh) to partition materialized views.

Database version is oracle 10.1.0.4. I have decided to use on prebuilt table option to do the partitioning as it minimizes the time to transfer from the master site.

1) stop replication
1) create interim tables with similar structure as the materialized views
2) transfer all data from the materialized views to the interim tables
4) script out the materialized views structure and add in on prebuilt table option in the scripts
5) drop the materialized views
6) rename the interim tables with the same name as the materialized views
7) run the scripts to create the materialized views with on prebuilt table option
8) refresh the newly created materialized views -> it should take a short time since I am using on prebuilt table option

But I am facing one major issue. That is if I drop the materialized views, the materialized view logs of the master tables are purged. When the materialized views are refreshed fast, there are some data missing. the data that are purged out when the materialized view are dropped.

Do you happen to know other ways that existing materialized views can be converted to partitioned materialized views? Do you have any workaround to prevent the materialized view logs from being purged?

View 3 Replies View Related

PL/SQL :: Compare 2 Result Sets?

Nov 13, 2013

I have two queries which count the number of lines of the tables of the same schema in two databases . How can I compare the two result set. 

View 7 Replies View Related

SQL & PL/SQL :: Multilevel Collection Comparison Using Sets?

Sep 1, 2010

I need to populate a table based on the results of comparing sets of data. I decided to do this using MULTISET EXCEPT, but having created the structure, do not know whether it is actually possible, and if so, what syntax to use.

I have created:

CREATE OR REPLACE
TYPE NUMBER_TBL IS TABLE OF NUMBER;
/

CREATE OR REPLACE
TYPE PACKAGE_OPTION_GROUP_OBJ AS OBJECT( ID NUMBER, benefits NUMBER_TBL )
/

CREATE OR REPLACE
TYPE PACKAGE_OPTION_GROUP_TBL AS table of PACKAGE_OPTION_GROUP_OBJ
/

I have populated the following:

selectedTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();

defaultTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();

groupTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();

comparisonTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();

Once populated they contain the following data:
***selectedTable***
GROUP_ID : 121
BENEFITS : 161
---------------------------
GROUP_ID : 123
BENEFITS : 165
BENEFITS : 167
---------------------------

***defaultTable***
GROUP_ID : 121
BENEFITS : 161
---------------------------
GROUP_ID : 122
BENEFITS : 162
BENEFITS : 163
---------------------------

***groupTable***
GROUP_ID : 121
BENEFITS : 161
---------------------------
GROUP_ID : 123
BENEFITS : 165
BENEFITS : 166
---------------------------

I first need to compare the ID field only of selectedTable with defaultTable to find where they differ, something like

comparisonTable := selectedTable MULTISET EXCEPT(ID) defaultTable

I am expecting the result to be something like:
***comparisonTable***
GROUP_ID : 123
BENEFITS : 165
BENEFITS : 167
---------------------------

However, I don't know if this is possible and if so, what the syntax would be.

At a later stage, I will need to compare the benefits between selectedTable and groupTable, where the GroupID's match, which is why I have the tables structured in this way.

View 6 Replies View Related

PL/SQL :: Compare 2 Result Sets In Procedure

Apr 17, 2013

I have two queries. One selects 5 values (all number datatypes), the second query selects 2 values (both number datatypes).If a value from query 2 exists in the resultset of query 1 - go make table updates using the second value in the select statment of query 2. If there is not a match, update a table with values from query 1.

Currently, I have two cursor for loops, which are nested. This works but of course is horribly slow. What is a better way to process this data?The number of rows varies between the two resultsets.

Oracle 11.2.0.3.2

View 7 Replies View Related

SQL & PL/SQL :: Dataset From Two Sets Of Tables Based On Condition

Nov 26, 2010

have two queries that will return same columns from two different set of tables ( column mapping has been taken care of). The return type is out ref cursor. (P_SUPPLY_REORDER )

Query 1-SO
-----------------------------------
select
so.SMO_NO,
so.SPLY_ORD_DT,
so.fk_CUST_ID as CUST_ID,
so.CUST_PO_NO,
so.ATTENTION_NAME,
[code].....

Query-2 Xcom
--------------------------------------
select
null as sMO_NO,
xso.created_date as SPLY_ORD_DT,
xso.fk_cust_id as cust_id,
cust.cust_po_no as cust_PO_NO
,(sta.SHIP_TO_ATTN_FIRST_NAME||''||sta.SHIP_TO_ATTN_LAST_NAME) as attention_name,
xsol.CARTONS_ORDERED as SPLY_ORD_QTY,
[code].......

Now the requirement is
One of four conditions are possible for each Supply Reorder Number:

. Both table queries return no records- Populate all the P_SUPPLY_REORDER output fields with nulls
. SUPPLY_ORDER returns a record, but XCOM_ORDER_HEADER returns no records
- Populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
. SUPPLY_ORDER returns no records, but XCOM_ORDER_HEADER returns one record
- Populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.
. SUPPLY_ORDER returns a record, and XCOM_ORDER_HEADER returns a record; find out the latest order by comapring max(SPLY_ORD_DT)
from SUPPLY_ORDER with max(CREATED_DATE) from XCOM_ORDER_HEADER.
- If the latest order is in SUPPLY_ORDER, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If order dates are equal from both join results, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If the latest order is in XCOM_ORDER_HEADER, then populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.

Question is how can we switch over the queries to pull respective dataset based on these conditions ( checking that which table join is going to return a row and then based upon latest order if both tables return a row) and all this logic as part of single SQL statement that is returned as OUT Ref Cursor.

View 7 Replies View Related

Storing Multiple Currencies And Character Sets?

Aug 19, 2008

i basically need to setup a schema for globalization. i need to handle brittish dollar signs, american dollar signs. etc.what is a global character setting i can use to handle almost everything?

my problem right now is that when i store a brittian dollar sign it converts it to a box. i need this preserved so i can select it and use it later.will the WE8ISO8859P1 character setting work? where is a good place to view all of the types of character settings? and what they are for. etc. and also how do you change such things? not for the session, but for a schema wide

here are my current sessions

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA

[code]...

View 5 Replies View Related

RMAN :: Find Backup Sets Are Encrypted Or Not?

Feb 9, 2013

is there any way to find is rman backup sets are encrypted or not.

View 1 Replies View Related

RMAN :: Backup Sets Versus Image Copies

Feb 5, 2013

I am trying to understand What is the advantage of RMAN backup image copies over backup sets? ( image copies to the disk under FRA location )

Database version: 11.2.0.3

View 2 Replies View Related

SQL & PL/SQL :: Delete Last N Sets Of Rows Of A Table With Same Dates In Sort Field

Mar 15, 2013

I know how to select the last N sets of rows, using DENSE_RANK - where multiple rows have the same timestamp but I want to only select those rows which do NOT have the top 2 unique timestamps.

i.e.:

SELECT *
FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK, HISTORYID, USER_ID, myTimestamp, STATUS, FROM TXN_HIST)
WHERE DENSE_RANK > 2 order by myTimestamp DESC, HISTORYID, USER_ID;

But how do I DELETE these same rows?

View 3 Replies View Related

SQL & PL/SQL :: Different Number Of Result Sets While Adding Further Columns In Select Clause

May 4, 2010

The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.

Before adding the columns 4,5,6,the result set count was 11.

SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....

View 8 Replies View Related

Dictionary Managed Tablespaces

Jul 7, 2010

I've got one database which was Initially upgraded from Oracle 8i to 10.2.04 running on windows. Most of the tabespaces are Dictionary managed. Do you think moving them to locally managed tablespace would give me better performance?

if Yes, what approach I should apply to move them to locally managed? I would like to do this with minimum/no downtime.

View 6 Replies View Related

SQL & PL/SQL :: Data Dictionary View Not Available

Nov 1, 2011

Question 1) I have read the following statement in a PL/SQL book.

Quote:To check whether an existing procedure is compiled for native execution or not, you can query the following data dictionary views:

[USER | ALL | DBA]_STORED_SETTINGS
[USER | ALL | DBA ]_PLSQL_OBJECTS

However, I when i query the view USER_PLSQL_OBJECTS I get the following error message:

Quote:ORA-00942: table or view does not exist

Question 2) I have read the PLSQL_WARNING can be set to DEFERRED at the system level. However, I am unable to defer it. tell me how to apply defer caluse to following statement:

Quote:ALTER SYSTEM SET PLSQL_WARNINGS ='DISABLE:ALL'

View 10 Replies View Related

SQL & PL/SQL :: Can Modify Oracle Data Dictionary

Feb 6, 2011

can i modify oracle data dictionary

View 6 Replies View Related

SQL & PL/SQL :: DML Sentences Role In Data Dictionary?

May 21, 2012

Changing Data dictionary by DML Sentences (e.g Insert, Delete, Update,...), Will be possible? Or just by DDL Sentences?

View -1 Replies View Related

SQL & PL/SQL :: Sort Name Field In Dictionary Order?

Dec 12, 2010

I have a name field like below; i need a query to display the name field in alphabetical order like in dictionary.

Emp Name
--------
Sam
John
Noel
Alen
Saaem

output would be
---------------
Alen
John
Noel
Saaem
Sam

View 5 Replies View Related

PL/SQL :: Dictionary For Materialized View Partitions

Sep 2, 2012

I created a materilaized view with partitions and i need to add partitions dynamicaaly using stored procedure for that i need to check whther the partiions with the same name existing.where can i see the partition names for a materilaized view is there any table like "USER_TAB_PARTITIONS"?.if the same query exists in the forum

View 3 Replies View Related

SQL & PL/SQL :: Grouping Of Data

Aug 19, 2013

I have a requirement to get the records group wise.Ex: For each departments, i need to get the employee details as a coma seperated.It means that the output must have the department name in first column and the second column must contain all the employees in that particular department (As a coma seperated).

View 3 Replies View Related

SQL & PL/SQL :: Generating A Key For Grouping?

Mar 27, 2013

In the below data, a container is moving from one city to another. 1,2 ,3 can be any number which i want to generate and use as keys to group the cities. Eg: AUH, JEB, CIW belong to the same key=2; SIN, IKT belong to a new group 4. The City where difference between the Seq# is greater than 1 (eg between S8W and AUH), a new group starts.

Conotainer #CitySeqI want this
-------------------------------------------
Container1S8W5251
Container1S8W5261
Container1AUH5362
Container1AUH5372
Container1JEB5382
Container1JEB5392

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Grouping By Date

Oct 14, 2011

I have a table like this

Name Hours date
a810/11/2011
a 510/12/2011
a610/13/2011
a710/14/2011
a710/15/2011
a810/16/2011
a710/17/2011
a810/18/2011
a810/19/2011
a710/20/2011
a710/21/2011

If i want the sum of hours for 3 days range ,how should i do it.

E.g. say

name hrs startdate enddate

a 19 10/11/2011 10/13/2011
a 22 10/14/201110/16/2011
a 23 10/17/2011 10/19/2011

View 11 Replies View Related

PL/SQL :: Grouping Of Data

Feb 10, 2013

How can I get the grp_id for unique combination of manager and department, grp_id should be created on asc order of manager_id.
In this example manager_id 100 is minimum, so it should be grp 1 and all the employees with that manager_id should be in grp_id 1, for manager_id 114 grp_id should be 2.

If, there is manager_id 117, it should create grp_id 3.

To get grp_num ,I can use row_number() over (partition by department_id,manager_id order by employee_id) grp_num

I am looking for an update statement for this issue.

Oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

CREATE TABLE HR.EMPLOYEES_2
(
  EMPLOYEE_ID         NUMBER(6),
  FIRST_NAME          VARCHAR2(20 BYTE),
  LAST_NAME           VARCHAR2(25 BYTE),
  EMAIL               VARCHAR2(25 BYTE),
  PHONE_NUMBER        VARCHAR2(20 BYTE),

[Code]....

Expected result
----------------
EMPLOYEE_ID    SALARY    MANAGER_ID    DEPARTMENT_ID    GRP_NUM    GRP_ID

114              11000        100          30              1              1
115              3100         100          30              2              1
116              2900         114          30              1              2
117              2800         114          30              2              2
118              2600         114          30              3              2
119              2500         114          30              4              2

View 8 Replies View Related

Data Dictionary - Find All References Of A Table?

Sep 14, 2011

I wanna to DROP a table called EMPLOYEES, but when I execute the DROP TABLE EMPLOYEES, I get a ERROR saying that I cant do it because this table do reference to another table(s).

I tried to use the DBA_CONS_COLUMNS and DBA_CONSTRAINTS data tables, but its not enough to find it.

How can I find these references?

View 4 Replies View Related

Forms :: Grant Select Any Dictionary To User

Oct 22, 2011

I have a procedure in my schema. I have created a sys context name xyz for passing the date in that i am passing the sysdate to that context which is used in a view.i have used/called that procedure in form.

My problem is that when i am giving Grant select any dictionary to the user then form is compiled otherwise form is giving error procedure name must be declared. But for security reasons i don't to give select any dictionary to that user.

View 3 Replies View Related

SQL & PL/SQL :: Nowait Requested But Had To Wait To Lock Dictionary

Aug 5, 2010

I get the Error like this frequently

ERROR at line 1:
ORA-20001: ORA-04022: nowait requested, but had to wait to lock dictionary
object
ORA-06512: at "MDWDBA.MDW_PKG", line 917
ORA-04022: nowait requested, but had to wait to lock dictionary object
ORA-06512: at line 1

why this occurs? and how to Resolve it?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved