SQL & PL/SQL :: Permissions On Given Leaf Node Then All Parent / Grand Nodes Should Return?

Feb 4, 2012

writing an SQL query for the following scenario : have table ACCOUNTS that stores results in parent child relationship.

ACCT_ID |ACCT_NAME |ACCT_PARENT |ACCT_TYPE
1 |Group1 | 0 | 1
2 |Group2 | 1 | 1
3 |Group3 | 2 | 1
4 |Account1 | 3 | 0
5 |Account2 | 3 | 0
6 |Account3 | 2 | 0

Here ACCT_TYPE=0 denotes leaf node.Another table USER_ACCOUNT stores user permissions on the leaf nodes from ACCOUNTS table.

USER_ID |ACCT_ID| PROPERTY
1 | 4| Yes
2 | 5 | No
3 | 6| Yes

This shows user=1 has permission on Account=4 , user=2 does not have access priviledge on Account_id=5.Now my requirement is to write a SQL query such that If user has permissions on a given leaf node then all the parent / grand parent nodes should also be returned.
Eg:
SELECT * FROM (.......) where USER_ID=2;

should give result something as below :

USER_ID | ACCT_ID |ACCT_NAME | PROPERTY
1 | 4 | Account1 |Yes
1 | 3 | Group3 |Yes
1 | 2 | Group2 |Yes
1 | 1 | Group1 |Yes

with this ASAP as this is quite an urgent issue that I need to fix.

View 15 Replies


ADVERTISEMENT

SQL & PL/SQL :: Getting Parent And Leaf Child Along With ID

Jun 14, 2012

I've the following data and i need the parent and leaf most child,including their id's

Create table par_chld (id,p_id,c_id) as
(select 1,900,501 from dual union all
select 2,900,502 from dual union all
select 3,900,503 from dual union all
select 4,100,900 from dual union all
select 5,200,900 from dual union all
select 6,300,400 from dual union all
select 7,101,500 from dual union all
select 8,102,500 from dual union all
select 9,103,500 from dual union all
select 10,201,600 from dual union all
select 11,201,601 from dual union all
select 12,201,602 from dual )

In the above data
p_id =100 has c_id 900 , and this 900 acts as a parent which has child 501 , 502 ,503

And the output should be in the following format , Where i don't need the middle level data. I need parent and its leaf child along with the id's of parent and child

Expected output

id_p p_id id_c c_id

4 100 1 501
4 100 2 502
4 100 3 503
5 200 1 501
5 200 2 502
5 200 3 503
6 300 6 400
7 101 7 500
8 102 8 500
9 103 9 500
10 201 10 600
11 201 11 601
12 201 12 602

I tried the following query where i got the p_id,id_c,c_id columns , but unable to get the column id_p

select connect_by_root p_id p_id ,id id_c,c_id
from par_chld
where connect_by_isleaf =1
start with p_id not in (select c_id
from par_chld
where c_id is not null)
connect by nocycle p_id =prior c_id

getting the id_p column as expected above.

View 2 Replies View Related

Duplicate RAC Database With ASM And 2 Nodes To A Single Node?

Apr 30, 2010

i'm trying to duplicate my RAC database with ASM and 2 nodes to a single node, non ASM with rman duplicate database for standby with active database on windows. The problem is i dont know how to remove RAC configuration when duplicating the database.

Here is the procedure:

On standby i've:
- installed database software,
- configured listener and tnsnames,
- added instance service (oradim -new -sid STANDBY -intpwd PASSWORD -startmode M),
- configured initora (with only db_name = STANDBY)
- configured orapwd file
- startup nomount

on RAC node1:

rman target sys/PASSWORD@RAC
connected to target database
RMAN> connect auxiliary sys/password@STANDBY
connected to auxiliary database: STANDBY (not mounted)

now for the duplicate command:

RMAN> duplicate target database
2> for standby
3> from active database
4> spfile
5> parameter_value_convert = '+DATA/RAC','D:\oracle\11.2.0\DATAFILES\DATA\STANDBY'
6> set db_unique_name='STANDBY'
7> set log_file_name_convert = '+DATA/RAC','D:\oracle\11.2.0\DATAFILES\DATA\STANDBY'
8> set db_file_name_convert = '+DATA/RAC','D:\oracle\11.2.0\DATAFILES\DATA\STANDBY';

i have tried adding this parameter - set cluster_database='false' but no luck.

View 7 Replies View Related

SQL & PL/SQL :: Select Query To Return Parent Rows?

Apr 1, 2012

I need to get the super set(parent) record from a relational table. Following is the structure of the table,

create table relation (data_field1 number(20),data_field2 number(20),data_field3 number(20),

primary key (data_field1,data_field2,data_field3) );
insert into relation (data_field1, data_field2, data_field3) values (-10,123,334);
insert into relation (data_field1, data_field2, data_field3) values (200,123,334);
insert into relation (data_field1, data_field2, data_field3) values (300,123,334);
insert into relation (data_field1, data_field2, data_field3) values (400,123,334);

[code].....

Here '-10' will be considered as parent(super set) for all other values. If i have records like below,

-10 200 300
100 200 300
123 200 300
521 201 300

Output should only contain two rows. A super set row and other distinct row(second column value is not equivalent to super set).

View 14 Replies View Related

Real Application Clusters :: Removing 2 Nodes From A Cluster Of 4 Nodes?

Nov 6, 2012

[URL] I arrived at step 2 (Step 2: Delete Nodes from the Cluster) point 4 and 5.

I executed this correctly (on 2 nodes):

For a nonshared home:

To delete a node from a cluster:

./runInstaller -updateNodeList ORACLE_HOME=Oracle_home CLUSTER_NODES="" –local

Output:

[oracle@server03 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES="" -local
Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB.   Actual 20127 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
'UpdateNodeList' was successful.then I executed:

To deinstall the Oracle home from the node you are deleting, run the following command from the Oracle_home/oui/bin directory:

./runInstaller -deinstall -silent "REMOVE_HOMES={Oracle_home}" -local

Output:
[oracle@server03 bin]$ ./runInstaller -deinstall -silent "REMOVE_HOMES=$ORACLE_HOME" -local
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 20127 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-11-06_12-05-36PM. Please wait ...[oracle@server03 bin]$ Oracle Universal Installer, Version 11.1.0.7.0 Production
Copyright (C) 1999, 2008, Oracle. All rights reserved.

[code]....

When I arrive to the point 5 I see one resource ora.DPCTTest.db ONLINE on one server (server02 or server03 that are the nodes I want to remove).
If I execute:

crs_relocate ora.db_name.db

I obtain:
[oracle@server03 bin]$ ./crs_relocate  ora.DPCTTest.db
Attempting to stop `ora.DPCTTest.db` on member `server02`
Stop of `ora.DPCTTest.db` on member `server02` succeeded.
Attempting to start `ora.DPCTTest.db` on member `server03`

[code]....

View 5 Replies View Related

JDeveloper, Java & XML :: Extracting Sub-nodes From XML With Multiple Nodes?

Oct 27, 2011

how to extract sub nodes from multi nodes in this file.

<?xml version="1.0" encoding="utf-8"?>
<Orders>
<LeasingCoNum>123456</LeasingCoNum>
<DateTime>10/05/2011 10:00:00 AM</DateTime>
<NewOrders>
<Order>

[code]....

View 9 Replies View Related

PL/SQL :: How To Update Parent And Child Tables While Updating Parent Table

Jun 13, 2012

I have a parent table EMPLOYEE which includes columns (sysid, serviceno,employeename...) sysid is Primary key, serviceno is Unique key and I have child table DEPENDENT includes columns (sysid,employee_sysid,name,dob...) here again SYSID is primary key for DEPENDENTS table, employee_sysid is Foreign key of EMPLOYEE table.

Now I want to change SYSID (using sequence) in EMPLOYEE table which need to be update in DEPENDENTS table as well

Note: I have 10000 records in EMPLOYEE table as well as I have 5 more child tables which need to update new SYSID.

View 5 Replies View Related

PL/SQL :: Rollup Grand Total?

Aug 8, 2013

I am trying to use rollup for a total, but it doesn't work because what I'm totaling is too conditional.  I even tried adding on the totals to the end using a union and a specialized function, but for some reason, the order by stops working when I do the union (all field aliases and types are the same).I know the structure of the query itself probably isn't the best, how I can get the grand total, The subtotals from the rollup work fine, though.

The order by query runs fine against both parts of the union when they are run individually, but once I make it part of the union - the first part of the order by is an unknown field and the second is not a SELECTed list expression.  I've commented out the union here.  The statement works fine without it except for the grand total being all 0s.  I kind of understand why it's all 0s, but not how to get around that.This is running in Oracle 10g2. I am trying to get totals for decisions by each of the four component values: ex_land, ex_imp,tx_land,tx_imp and then subtotals for each component values. 

That works for the following query, but I cannot get a grand total through the rollup.

CREATE  TABLE "FL_APP"   (    "APP_ID"                  NUMBER(*,0),    "FL_STAT_CODE"           VARCHAR2(20 BYTE),    CONSTRAINT "FL_APP PK" PRIMARY KEY ("APP_ID") ENABLE,    CONSTRAINT "FL_APP_FL_STAT_DIM_FK1" FOREIGN KEY ("FL_STAT_CODE")    REFERENCES "FL"."FL_STAT_DIM" ("FL_STAT_CODE") ON  DELETE    CASCADE ENABLE  ) ;CREATE  TABLE "FL_APP_PROP"  (    "APP_ID"            NUMBER(*,0),   "FL_DECISION_CODE" VARCHAR2(10

[code]....

View 8 Replies View Related

SQL & PL/SQL :: Adding Grand Total In Result

Jun 21, 2010

i just want to add at the end of each area_cd the total number of asset.. what do you think i should add in my query????

View 32 Replies View Related

SQL & PL/SQL :: Add Word Grand Total Under Tester Column After Roll Up

Sep 17, 2010

Below is the statement I am working with. When I get the result the under the Tester column it is a empty space after the total has been done. How can I add the word "Grand Total" under the tester column after the rollup.

SELECT BG_detected_by Tester, CONCAT(Round(count(bg_target_rcyc) / count(bg_status)* 100), '%') Percentage_Compeleted,count(BG_Target_RCYC) Total_Target_Cycle,count(bg_status) Total_Defects
From Bug
where BG_STATUS = 'Open'
GROUP by Rollup(BG_DETECTED_BY,BG_STATUS)
having (grouping(bg_detected_by) = grouping(bg_status))

Tester Percentage_completed Target Defects
empty space --->Grand Total

View 1 Replies View Related

RAC/ASM Clusterware Installation :: Query On Adding New Node To Existing 2 Node Cluster

Jun 15, 2012

current environment

Oracle VM 2 Node RAC cluster
Oracle 11G 11.2.0.3.0
Oracle 11g rel 2 GI

on current 2 node cluster we have GI and RAC db configured Nodes: vmorarac1,vmorarac2

we shutdown vmorarac1 to clone it to vmorarac5

on new node I have changed the hostname to vmorarac5 In /etc/sysconfig/network-scripts/ifcfg-eth0 change ip to new ip and same for /etc/sysconfig/network-scripts/ifcfg-eth1 152.144.199.210,152.144.199.211

mad echanges to /etc/hosts on vmorarac1/2 for 2 new IP address assigned to vmorarac5

152.144.199.171 vmorarac1.pbi.global.pvt vmorarac1
192.168.2.30 vmorarac1-priv.pbi.global.pvt vmorarac1-priv
152.144.199.184 vmorarac1-vip.pbi.global.pvt vmorarac1-vip

[Code]....

Query is is that all ok to reboot the new node to reflect changes as below and change the hostname to vmorarac2

152.144.199.210 vmorarac2.pbi.global.pvt vmorarac2
192.168.2.32 vmorarac2-priv.pbi.global.pvt vmorarac2-priv
152.144.199.211 vmorarac2-vip.pbi.global.pvt vmorarac2-vip

hostname change in /etc/sysconfig/network

On new node vmorarac5, there is alreay software for RAC DB and GUI cloned from vmorarac1

addnode.sh is used to add existing node, but as part of pre-requisite configuratoin is there any config step missing

View 3 Replies View Related

Data Guard :: How To Find Apply Node In RAC Two Node Standby DB

Aug 16, 2013

I believe, in a multiple RAC Physical Standby nodes only one node applies the archivelogs to the standby database. How you can find which node is the APPLY NODE using a SQL query?

I know you can veiw this information by querying the standby configuration using DGMGRL but how would you find out this information from a remote client using SQLPlus/PLSQL?

View 2 Replies View Related

RAC & Failsafe :: Migrating Single Node Database To New 2-node Environment

Jan 24, 2011

We have oracle database 10g(10.2.0.4) running on AIX 5.1. we have installed 2-node RAC on another server AIX 6.1 . Now we want to migrate our single node database to new 2-node rac environment. how we can move/migrate/cluster-aware our single node database to 2-node rac?

View 3 Replies View Related

Application Express :: How To Show Column Headers Before Grand Total

May 23, 2013

In a classic report, I'm using the Sum functionality and breaks by First Column to get subtotals and report total. I Repeat Headers on Break which works great for the subtotals but I would like for the report to display the column headers above the report total for easier reading. If the last grouping has a lot of data, the user needs to scroll up to read the column headers when looking at the Grand Total.

Is there a way to Repeat Headers prior to report total?

View 3 Replies View Related

RAC/ASM Clusterware Installation :: VIP Node Application Failing In Second Node

Jul 18, 2013

I am installing Oracle 10g Clusterware in RHEL 5 Server. When i run root.sh in the second node the last step Running vipca(silent) for configuring nodeapps failed. Then i run VIPCA manually in second node and configured vip configuration successfully,but when i checked the post-checks for cluster services the Checking existence of VIP node application,Checking existence of ONS node application and Checking existence of GSD node application in second node failed. I am able to ping both the servers one another with vip name successfully and the CSS,CRS and EVM appears healthy in both nodes. 1. Does the vip configuration is proper? If no what is causing this error and how to rectify it? Find the output below

[root@Rhel52 bin]# ./crsctl check crsCSS appears healthyCRS appears healthyEVM appears healthy[root@Rhel52 bin]#[root@Rhel52 bin]# clear[root@Rhel52 bin]#[root@Rhel52 bin]# ./crs_stat -tName           Type           Target    State              Host------------------------------------------------------------------------------------ora.rhel5.gsd  application     ONLINE    ONLINE      rhel5ora.rhel5.ons  application     ONLINE    ONLINE      rhel5ora.rhel5.vip  application      ONLINE    ONLINE       rhel5ora.rhel52.gsd application    ONLINE    ONLINE     rhel52ora.rhel52.ons application    ONLINE    ONLINE     rhel52ora.rhel52.vip application     ONLINE     ONLINE      rhel52 [root@Rhel52 bin]#./runcluvfy.sh stage -post crsinst -n 

[code]....

View 0 Replies View Related

RAC & Failsafe :: Single Node RAC - Configure Only One Server Instead Of Two Node?

Sep 13, 2012

I would like get more details about single node RAC.. What is single Node RAC?..

1). just configure only one server instead of two node... same RAC installation.. cluster installation and shared disk configuration in single server itself.. in future we can add node to this ...
2). in 11gR2 having new feature Single Node RAC.. Multiple instances running on same node and configure using Cluster...

is there any advantage using single node rac(other than online upgrade to full RAC).

View 3 Replies View Related

Application Express :: 4.2 Item - Validation Return Boolean And Return Error Text Are Switched

Oct 17, 2012

In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.

Is there a simple statement that can be used to fix this in the apex dictionary?

View 1 Replies View Related

Grant Permissions On Oracle 11.2.0.3?

Apr 20, 2012

wanted to grant dbms_scheduler permission to the system user in oracle 11.2.0.3,but it is showing insufficient privileges, but my System has Sysdba rights.

SQL> SELECT * FROM v$pwfile_users WHERE sysdba='TRUE';
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

[code]....

Revoke succeeded.

SQL> conn system
Enter password:
Connected.

[code]...

while i ran this query
"
SQL> select * from session_privs;"

for system user i have 202 rows of different privileges,but the same query for sysdba has 208 rows...

View 6 Replies View Related

Add Third Node To Existing Two Node Cluster

Feb 3, 2012

I'm trying to add the third node to my existing two node cluster. In fact i succeded adding the node but i have the following problem:

-when i start node 1 and 2, everything is ok, but on node 3 startup one of the other nodes fails
-this happens in any combination: start 1 and 3, everything is ok. when i start node 2, node 3 fails

The asm configuration (disks, permissions, etc) is the same on all three nodes.find below the asm and crsd log details from the failure time:

alert_ASM.log
NOTE: enlarging ACD for group 1/0x124851d1 (DATA)
WARNNING: cache read a corrupted block group=DATA dsk=4 blk=1 from disk 4
+NOTE: a corrupted block from group DATA was dumped to /u01/app/oracle/diag/asm/+asm/+ASM3/trace/+ASM3_rbal_4109.trc+
WARNNING: cache read(retry) a corrupted block group=DATA dsk=4 blk=1 from disk 4
[code]...

View 3 Replies View Related

Opening Permissions To Trace Files?

Nov 15, 2010

un-documented parameter _trace_files_public / I want to set this to true so my app team can review trace files. better way to proceed to open read permissions for non oracle users.

View 2 Replies View Related

SQL & PL/SQL :: Grant Table Permissions To Another User

Jul 11, 2012

I have two user a and b. I have to Grant select update insert Delete Permission to all tables of user b To User a. how can it possible?

View 5 Replies View Related

PL/SQL :: How To Give A User Administrative Permissions

Oct 15, 2012

Suppose I using "scott" user, I need to give administrative permissions to scott. How can I give??

View 6 Replies View Related

Security Role And Auto Permissions

May 13, 2013

I have created and role in my database and assign privileges as per following query.

Select 'Grant select on'||' user.'||object_name||' '||'to MyRole ;' from all_objects
where object_type in ('TABLE','VIEW')
and owner='username';

After granting role to new user everything work fine.I want to know a way to sync role with any newly created object.

Should I create a job that may execute above sql store results in a file and then execute to ensure all privileges are up to date for role or there any other optimal way exist ?

View 4 Replies View Related

SQL & PL/SQL :: Granting Permissions For Create Type / Procedure?

May 18, 2011

I have a question regarding how to grant create type and create procedure roles correctly.Currently if I do this:

create user vackar_temp identified by "123456789";
grant create type to vackar_temp ;

Then as vackar_temp:

create type temp_col as table of number;

I get the following error:

ORA-01031: insufficient privileges

I've also tried:

grant resource to vackar_temp ;

But that doesn't work either If I run this:

select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,

[code]...

I can see that the user does have the create type role:

User, his roles and privileges
------------------------------
VACKAR_TEMP
CREATE TYPE
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE

[code]...

View 8 Replies View Related

View A Stored Procedure Without Granting Execute Permissions?

Jul 13, 2012

Is there a way to grant rights to view a stored procedure without granting execute permissions?

View 2 Replies View Related

RMAN :: Output File Permissions With ASM / Grid Infrastructure

Jul 10, 2013

I've noticed that the backup files produced by RMAN (Database is 11.2.0.2.0 Enterprise Edition) )on a RHEL5.5. server which is running ASM have group permissions assigned to ASMADMIN (so the file permissions are assigned to ORACLE:ASMADMIN) as opposed to OINSTALL or any other group.

While this is not an issue (it's not currently impacting us in any way) I'm curious as to how and why this happens. I suspect it is related to the fact that the oracle executable has these permissions also but I can't find any material to confirm my suspicion.

View 2 Replies View Related

Export/Import/SQL Loader :: Grant Permissions Not Importing From 10g To 11g?

Jun 6, 2012

i am using datapump to import database from 10g to 11g . all the tables and users everything got transferred but some grant permissions (create session) on users ,not importing to 11g. but same process imports grant if if do datapump to another 10g db .

do i need to import grants separately for 11g .

View 18 Replies View Related

Schema Permissions - Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

I have an automated process which runs on an Oracle 8i database server as user abc.This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 1 Replies View Related

From Oracle 10g Single Instance To 11g R2 Two Nodes RAC

Aug 17, 2011

Initial situation: Oracle 10g database single instance on Windows 2008 (datafile within NTFS partition)
Final situation: same database upgraded to Oracle 11g r2 on a two nodes RAC on Windows 2008 (datafile within ASM)

In your opinion, is this the best way to achieve the job ?

1) on the two nodes install grid 11gr2, asm, rdbms 11g r2 (SE)
2) export full from 10g
3) create a new empty 11g r2 database in the RAC 11g r2 infrastructure with the same tablespace layout of 10g database
4) import full from 10g to 11g r2

View 1 Replies View Related

RAC & Failsafe :: DNS Response Time For Nodes

Feb 24, 2011

I'm trying to install 2 nodes oracle RAC 11gR2 on SLES11. I configured DNS for public,virtual, private and scan IPs. when I check with nslookup, everything seems ok. but when I run runcluvfy.sh, it says that:

" PRVF-5636 : The DNS response time for an unreachable node exceeded 15000 ms on following nodes: rac1,rac2"

How can I decrease the response time?

View 4 Replies View Related







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