i want to create a group xyz and add some users to xyz group and want to grant/revoke permissions to xyz. So that all the users present in that group will have the same permissions as of the xyz group. so that instead of giving the permissions to users individually i can give it at a time.
View 20 RepliesI am new to PL/SQL and how to create a trigger to compute the population of the school from the groups of students and store back in population. It also needs to check that there is a min of 10 students to a school.
CREATE OR REPLACE TYPE group_type AS OBJECT
(
group_nameVARCHAR2(20),
tutor_idNUMBER(5),
[code]...
I am able to assign a user to a user group using the User Admin in Apex.I don't know how I would be able to assign a role (that I know how to define that for an individual user).The only thing I can see is a name for User Group and a Description! My requirement is to define a group of people to be assigned to one group/role, so that every change to that role can be automatically be applied to each user in that group
View 1 Replies View RelatedI need to create a user on oracle aix to never expire. I did the following
create user identified by password
grand dba to user
alter user password_life_time unlimited
is this all i need to do?
I have been asked to improve the speed of our extract feeds to another team. It was using an excel file as csv created with C# reading the csv into a global temporary table and a procedure to insert rows from global table to main table one at a time.
Around 1 million rows That was taking around 25 min. After trying sqlldr and an intermediate table I managed to reduce timing to 15 sec with sqlldr and Direct Path Load. I tried all that in one of our own DEV databases. Now we want to try the approach in one of DEV databases belong to downstream group (the one we feed data). They are rather reluctant to give me access to their DEV database! I have asked them to create a new login and grant object creation (DDL) to this user.They keep saying that they are prepared to give me a role but NOT schema owner. What is the best way of enabling me to create tables somewhere and test loading times with sqlldr.
I need to do full DDL (table/proc creation) and DML on a certain schema. So my question: What is the best way of doing so.. Do I need a schema created for user to do this workI they already have a general purpose schema, can they create a role to have full DDL: rights in that schema?
I have a strange problem when creating a view in user from another user
I have a user called "Cash_tst"
its syntax creation is
-- Create the user
create user CASH_TST
identified by ""
default tablespace CASH
temporary tablespace TEMP
profile DEFAULT
quota unlimited on cash;
-- Grant/Revoke object privileges
grant connect to CASH_TST;
grant dba to CASH_TST;
grant resource to CASH_TST;
-- Grant/Revoke system privileges
grant create any view to CASH_TST;
grant unlimited tablespace to CASH_TST;
I want to create a view
CREATE VIEW TAMER
AS SELECT *
FROM [b]AROFL[/b].RA_CUSTOMER_TRX_LINES_ALL_BEFO
"AROFL" is another user on the same database
when try to create the view "tamer" i got message of
"insufficent privilege"
although i granted "create any view" to the user "cash_tst"
I am using 10.2.4.0 of oracle. I am having one requirement, in which i have to divide the set of records into certain groups , so that they can be executed partly but not in one run.
So in the 'SELECT' clause itself i want to assigns particular value (may be 1 )to first 50000 records then another value(may be 2) to next 10000, like wise. And again the total count of records will also varry time to time , if the total count of record set is less than 10000 , then it should only assign '1' to all the records. i will set the group values (1,2,3...) as another column itself.
I've a situation where I've very less redo logs generated. Let us say 10MB. Which solution will be better ?
1. Create one redo log group about 12 MB in size.
2. Create two redo log groups about 5 MB each in size as recommended by Oracle.
Even though solution 1 is also appropriate for me because I've less redo generated than the redo log group size. My whole redo will fit in this and I can raise checkpoint forcefully after certain period of time let us say every 3 seconds.
In one of our DB I found scenario one is implemented. So I want to know pros and cons of both of these practices.
we want to truncate a oracle Table in the Oracle DB. After the truncate the fact table will be loaded again. After the new load in the fact table we want to tell the times ten db to refresh the cache table. The cache Table is a user owned read-only cache group with no autorefresh. We want to tell times ten in a PL/SQL Block from Oracle DB that starts the refresh from the cache group in times ten. The refresh should not be a autorefresh because the refresh should only start if the fact table will new loaded after the truncate.
View 1 Replies View RelatedI created a form that would allow a user to create another user.i tried creating a user in forms 6i using the following code on the when button pressed:
BEGIN
forms_ddl('create user'||:user_name||'identified by'||:pasword);
commit_form;
END;
pasword and user_name are captured in the form.
I am trying to create a new user who will be able to do a delete from all of the tables that only I MYSELF created. I created my user and granted access but realized I may not have done it right..
is it simply SQL>grant delete on <TABLESPACE> to <new user>; ? or do i need to specify the grant the delete on my tablespace to new user?
We are using oracle forms 6i as front end and oracle 10g as back end.When we are creating a new user, the password should be stored in back end table in encrypted format.
View 4 Replies View RelatedThat is I have created the User Defined Data Type as following. CREATE OR REPLACE TYPE Bit_Type AS OBJECT(Bit NUMBER(1,0));
After completing this creation of new UDT, I am trying to create the table with this UDT as follows, CREATE OR REPLACE TABLE Sample_Bit ( RegID Bit_Type);
I received an Error Message like:
SQL Error: ORA-22913: must specify table name for nested table column or attribute
22913. 00000 - "must specify table name for nested table column or attribute"
*Cause: The storage clause is not specified for a nested table column or attribute.
*Action: Specify the nested table storage clause for the nested table column or attribute.
I am creating a New User by using our customized Oracle Form for Creating a New User.
I am ting the Bellow errors:
-604-ORA-00604 : error occurred at recursive SQL level 1
ORA-200001 : failed to create user,please contact dba for support.
ORA-06512: at line : 16
I am using ORacle Forms 10g as a Front End and Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production as Back end.
i have a field in one table called item_name based on which i am creating new item name as duplicate with new given length, for eg there is one item_code which has length of 12000mm i am going and replacing the string using instr function and replace function based on criteria that where i will find mm it will extract a portion and replace the new length as 6300mm.But its failing because some time user is creating the original item code without mm, is there a way.
Original:'H194x150x6x9x8x12000mm, 29.9 kg/m, ASTM A992M'
New : 'H194x150x6x9x8x6300mm, 29.9 kg/m, ASTM A992M'
comes some times he is entering mm and sometimes he is entering without mm as
Original:'H194x150x6x9x8x12000, 29.9 kg/m, ASTM A992M'
New : 'H194x150x6x9x8x12000, 29.9 kg/m, 6300'
I need to create a forgot password page in which there will be two text box named user name and email id and user will provide his usename and email id in those text box. so apex will check that the provided name and email id are there in the data base and if email id corresponds to right user name then a mail will be send to that email Id containing auto generated password.
View 1 Replies View RelatedWe are trying to implement the following security to our database.
As of now, the access rights are same for all the windows users logging into the Oracle application with the same Oracle user Id.
But now, we want to improve our security by granting different levels of rights to the users based on their Network Id even though they use the same Oracle User Id to log into the application.
We are not looking for the users to be identified externally.
(CREATE USER "OPS$ORACLE-BASE.COMTIM_HALL" IDENTIFIED EXTERNALLY;
GRANT CONNECT TO "OPS$ORACLE-BASE.COMTIM_HALL";
)
we are trying ti implement BMC monitoring tool for our oracle 11g2 RAC/solaris10 environment. for the purpose we create a user called bmcuser in OS level, this user connecting database to monitor, this user occupy shared memory as oracle. Explain why this user taking that much shared memory just to connect database using sqlplus.
oracle taking 55% bmcuser taking 44%,
SQL> show parameters memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 3232M
memory_target big integer 3232M
shared_memory_address integer 0
SQL> show parameters sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3232M
sga_target big integer 0
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
6449 bmcuser 47M 44M sleep 23 10 0:57:31 0.2% PatrolAgent/1
[code]........
pga & uga usage sessionwise
SID ORA_USR OS_USR S_PROG S.MACHINE S.PGA_MEM S.PGA_MEM_MAX S.UGA_M S.UGA_M_MAX
252 (null) oracle oracle@racdev1.zakathouse.org (ARC3) racdev1. 31054584 32168696 181056 181056
227 (null) oracle oracle@racdev1.zakathouse.org (ARC2) racdev1. 31054584 32168696 181056 181056
177 (null) oracle oracle@racdev1.zakathouse.org (ARC0) racdev1. 31054584 32168696 181056 181056
576 (null) oracle oracle@racdev1.zakathouse.org (LGWR) racdev1. 16693544 21281064 181056 181056
326 (null) oracle oracle@racdev1.zakathouse.org (LMS0) racdev1. 13753080 13753080 181056 181056
376 (null) oracle oracle@racdev1.zakathouse.org (LMS2) racdev1. 13556472 13556472 181056 181056
[code]........
We have a new implementation which will be using ASM with RAID. The data area needs to be 3TB, and the recovery area, to be used for archive logs and RMAN backups, needs to be 1TB.
The configuration i'm thinking about now is:
+DATA diskgroup: 5*600G disks, using RAID 1+0 (this will include control files)
+FRA diskgroup: 2*500G disks, using RAID 1
+LOG diskgroup: 2*1G disks, using RAID 1 (this is only for redo logs)
So here are my questions:
1. Am I right in supposing that we would get the best performance on the +FRA and +LOG diskgroups by not using RAID 1+0?
2. It has already been agreed to use RAID 1+0 for the +DATA diskgroup, but I can't see the added benefit of this. ASM will already stripe the data, so surely RAID 0 will just stripe it again (i.e double striping the data). Would it not be the better just to mirror the data at a hardware level?
Data at a table with single column unique indexed is like
1,3,5,6,8,10,98,45,23,56,34,12...... there are about 100 million records.
Out of these values we need to create fixed(5 in each) groups, from - to.
example
1-8
10-56
98-..
I have 5 MViews that I want to refresh in two occasions, every Sundays and at the 1st of the month. I created a Refresh Group for the weekly and that work fine. But when I tried to created the second Refresh Group for the monthly I get a "materialized view is already in a refresh group".
You can only have a materialized view in one refresh group? What options to I have to refresh it in different intervals?
We recently migrated a database from 9i to 10g (overdue we know!!) and discovered that dbms_mview.refresh default behavior was turned upside down - meaning that 10g didn't first truncate the MV to refresh it. We're trying to unwind a lot of legacy issues, but it also turns out that we also have 100 REFRESH GROUPs and 100 MATERIALIZED VIEWs. That means a 1 to 1 relationship between RGs vs MVs. There is one MV defined to each RG.
These are my questions:
1) Does a 1 to 1 relationship between RGs and MVs make sense to anybody? The original implementors are gone and we can't fathom the reason for this.
2) Is there any reason why I shouldn't convert these 100 groups to plain and simple 100 MVs? I don't want the delete/insert refresh behavior of dbms_refresh.refresh and I do want the truncate behavior of dbms_mview.refresh ATOMIC=FALSE for refreshing a standard MVIEW
I have a table of N records with: Name SeqNo ID Col4 ... ColX
where Name and ID are non-unique, and SeqNo is a monotonic non-consecutive sequence 0 .. N that is unique within ID..I'd like to generate the following 'groups': For each record where SeqNo = 0, sorted by Name, create the 'group where ID is the same, ordered by SeqNo irrespective of the
values of any of the other columns. For instance, if the table contained:
NameSeqNoIDCol4 ... ColX
RPL2975...
TLM0444...
AAB2801...
AZZ0801...
ABA3444...
KTT4975...
ABA1801...
YHG0975...
DEF1444...
I'd like to generate:
NameSeqNoIDCol4 ... ColX
AZZ0801...
ABA1801...
AAB2801...
TLM0444...
DEF1444...
ABA3444...
YHG0975...
RPL2975...
KTT4975...
I got my desired results by brute-forcing via four sub-queries:
Sub-query 1 - Generate the sorted Names with SeqNo = 0
Sub-query 2 - Expand above with the additional columns,
maintaining original order
Sub-query 3 - For each of the records from sub-query 2,
generate the 'dependents' having the same ID
and SeqNo != 0
Sub-query 4 - Expand above with the additional columns,
maintaining original order of sub-query 1
Main query - Create UNION of 2 and 4, sorting by original
order and SeqNo
if there were not a simpler approach - after all, this must be a fairly common issue when generating BOMs.
the thread title was a bit confusing, couldn't come up with anything short to describe the question. What I am looking for is a query which will put records into groups based on matching values in one of two columns. So if two records have a matching value in column 1 or column 2 they are in the same group. See the example bellow and expected output for a "better" explanation:
--setup
CREATE TABLE foo
(foo_id NUMBER NOT NULL PRIMARY KEY,
record_number NUMBER,
record_value VARCHAR2(1));
[Code]...
--expected output
group# foo_id record_number record_value
1 1 1 A
1 2 1 B
1 3 2 B
1 4 2 C
2 5 3 D
3 6 4 E
3 7 5 E
My initial thought is that is feels a little bit like the sequential seat problem but not quite close enough. I know it could be done iteratively with PL/SQL but I am thinking there must be a way to do it in SQL I am not seeing yet.
I have a table as follows:
customers
------------------------------------------------------------------------------------------
custid credit amt month
--------------------------------------------------------------------------------------------
001 C 2000 Jan-2012
001 D 5000 Feb-2012
001 C 3000 Mar-2012
001 C 3000 Apr-2012
001 D 7000 May-2012
I Have to write a single query to calculate the sum of credit and sum of debit value separately.
We have a big problem in the underlying devices that ASM disk groups depends on. We have SAN disks (EMC DMX) presented to us as /dev/sda, /dev/sdb, etc.These disks have actually multipath setup. For example, - /dev/emcpowera has two different paths as:
1./dev/sda
2./dev/sdp
We were using the direct path /dev/sda to format the disk (as fdisk /dev/sda), and then used oracleasm to create disks (oracleasm createdisk ASMDISKDATA0 /dev/sda1). Then, ASM disk groups were created with those lables (volumes), and then database was created using the ASM disk groups.
Now our platform folks are telling us that we should use the multipath /dev/emcpowera instead of direct path /dev/sda as the direct path is not guaranteed across reboot.
So the questions are:
1.Is there a way to re-link the disk group to asm disk to the multipath devices (/dev/emcpowera1 instead of /dev/sda1)?
2.Is this even an issue for ASM? If the /dev/sda fails after reboot, can Oracle ASM automatically discover the other path /dev/sdp to the physical EMC disk?
Is it possible to use TimesTen to create cache groups that have different table structures from original Oracle tables based on relational conditions?
View 3 Replies View Relatedi have implemeted grid control for monitoring database. we have different database environment like PROD , STAGE, TEST, BETA.
Now my requirement is, i need to configure PROD & STAGE alert for one group (group1@oracle.com) means, what ever alerts generated by grid should sent notification to group1@oracle.com and TEST AND BETA database alerts should to group2@oracle.com.
Now i am following the oracle manual to configure notifications. unfortunately i could not find to complete my requirement.
[URL]
did any one implemented alerts to send different groups.
below requirement..
We have certain records like SQL, PL/SQL, Reports, Forms, OAF etc in a table. We wanted to capture rating for each of these criteria. So we want a form to be displayed dynamically..
SQL 1 2 3 4 (1,2,3,4 represent radio buttons)
PL/SQL 1 2 3 4
Reports 1 2 3 4
Forms 1 2 3 4
OAF 1 2 3 4
If we add another row, XMLP in that table additional radio button should be displayed automatically..
If i create a table in Oracle 10g it givea me the follwoing Error.. what couls be the error...
ORA-00911: invalid character
create table shopper
(
cShopperId varchar2(30) primary key,
cPassword char(20) not null,
vFirstName varchar2(20) not null,
[code]....
