SQL & PL/SQL :: How To Add Tables Users
Jun 3, 2011
I have 4 tables as follows.
1.Deploybase(DeployId)
2.LicenseHistory(DeployId,UserId)
3.Users(UserId,User_Detail_id)
4.UserDetails(User_Detail_id)
Tables are joined using below joins.
Deploybase.DeployId=LicenseHistory.DeployId
LicenseHistory.User_id=Users.User_id
Users.User_Detail_id=UserDetails.User_Detail_id
When I join LicenseHistory,Users,UserDetails, all records are unique.
But the issues is: LicenseHistory table has duplicate records for DeployId column which is used to join table Deploybase. As a result when I join it to Deploybase, it gives me repeating entries which I don't want
I want Comments from LicenseHistory,Login from Users and User_name from UserDetails. There are multiple columns from 1st table i.e. Deploybase which I need.After reading on Exists operator, I made following query.
Select DeployId,comment from (select comments as comment,deploy_id as DeployId from LicenseHistory slh where exists (select deploy_id from symdb.symdb_deploybase sdb) and slh.comments is not null)
Above query gives me results from LicenseHistory table only. But I don't know how to add tables Users,UserDetails
View 1 Replies
ADVERTISEMENT
Dec 9, 2010
I am trying to drop a user but i get the following error
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
A couple of questions on this error:
- I did a search on the forum and the thread [URL] appears to have a solution to it by using
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQCALL', force =>TRUE);
What i am not sure of is which queue will the above statement drop if run as sys and there are multiple schemas with different schema names but with the same queue name?
- We recently rebuilt the database server and prior to the rebuild i have always managed to drop a user without the above error. Is it likely that some setting somewhere is changed?
View 2 Replies
View Related
Dec 11, 2012
I need to find out which users have direct access to tables, not through the roles.
Is dba_tab_privs the right table to query or table_privileges is the correct one.
What is the difference between these two.
I have gone through the documentation but I am still not clear about the difference between them.
Let me know whatever your thoughts are on this.
View 1 Replies
View Related
Sep 9, 2010
i've created a schema, which contains 30 tables. the tables are accessed & updated through a web based application. i've made public synonym for all tables for some reasons. how can prevent any one other than application users from updating the tables from Sqlplus.
View 2 Replies
View Related
May 20, 2013
i have to give 74 privileges from different users on different tables to one common user,how can i do this?
View 7 Replies
View Related
Nov 9, 2010
problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.
we have found something called table_exist_action=append in impdp.
View 2 Replies
View Related
May 18, 2011
I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
View 5 Replies
View Related
Sep 3, 2012
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies
View Related
Dec 23, 2011
HOW TO LOGOUT ALL USERS FROM ORACLE IN ONE COMMAND
View 2 Replies
View Related
Mar 2, 2013
I would like to extract the user rights from the ERP that I use with SQL/Oracle.
There are 3 databases. The first gives me all the users with the main key id_user. The second one gives me the actions with the main key id_action. The third one only gives me what the user can't do.
I would like to know all the actions that all users can do or not.
The result must be something like:
Action 1
User 1 Yes
User 2 No
Action 2
User 1 No
User 2 Yes
....
View 9 Replies
View Related
Jun 2, 2011
how to see password of users in 11g
View 11 Replies
View Related
Dec 8, 2010
I want to ask for a password from the user so they can access their account, however I want the password to have constraints on it. For example I want the user input to between 6 and 12 characters in length containing at least one number and one letter.
View 2 Replies
View Related
Feb 27, 2013
I've been issuing commands like these by using a session on my guest Oracle VM that is hosted by Windows 8.
sqlplus / as sysdba
create user PocketGuide3rdEd identified by top-secret-password ;
grant connect to PocketGuide3rdEd ;
grant create table to PocketGuide3rdEd ;
grant create view to PocketGuide3rdEd ;
grant create trigger to PocketGuide3rdEd ;
grant create sequence to PocketGuide3rdEd ;
grant unlimited tablespace to PocketGuide3rdEd ;
I've discovered that I cannot do "sqlplus / as sysdba" remotely. Is there a way I can create another account like sysdba that can issue the above commands from windows so I don't have to log into the oracle guest VM?
View 4 Replies
View Related
Oct 2, 2013
I am trying to find out if there is a definite way to find out (by querying database) which database users have been created by Oracle (either during installation or as part of patching or adding new feature) and which database users have been created by DBAs.
I have looked into the documentation but could not find anything relevant. Ideally, I would like to know if this can be done for any versions starting from 9iR2.
View 7 Replies
View Related
May 2, 2013
Are you aware of any "Best Practice Document" to grant select accesses to users on databases. These users are developers which select data out of database for the investigation and application bug fix.
From time to time user want more and more access to different tables so that they can do investigation properly.
View 2 Replies
View Related
Dec 26, 2012
The users are getting an error when connecting to the DB. I'm also not able to connect thru sqlplus . I get the message
$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 14:39:55 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-01075: you are currently logged on
ORA-04031: unable to allocate ORA-04031: unable to allocate 4064 bytes o bytes of shared memory ("","","","")
ORA-04031: unable to allocate ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select a.obj# OBJOID, a.clas...","sga heap(1,0)","kglsim hea
I need to restart the DB.
View 5 Replies
View Related
Jul 17, 2013
Seemingly a very simple problem, but I can't seem to figure it out. Table below is a table that holds UserId' s and LinkId's.
CREATE TABLE UserLink (UserId NUMBER, LinkId NUMBER );
ALTER TABLE UserLink ADD PRIMARY KEY (UserId, LinkId);
INSERT INTO UserLink VALUES (1, 1);
INSERT INTO UserLink VALUES (1, 2);
INSERT INTO UserLink VALUES (3, 1);
INSERT INTO UserLink VALUES (4, 1);
I'm looking for a query in which I can plug a List of LinkId's that outputs only users that have a relationship with ALL LinkId's provided. In this example you may assume that the List is equal to (1, 2), but you can't make any assumptions about the size of the list, other than size != 0. The query to find users that match ANY of the provided LinkId's is quite simple:
SELECT UserId FROM UserLink
WHERE LinkId IN (1,2)
The correct query should output:
USERID
------
1
And it should work for any size > 0 list of values.
View 6 Replies
View Related
Apr 23, 2010
I want to know what will be the performance of my DB before loading data and how many users can connect with my database.
View 7 Replies
View Related
Jun 18, 2012
we are refreshing data base for our application from the base load,Every time when ever there is refresh required we need to drop the users and recreate every thing from the base load. For this we need to kill the sessions and drop the users ,Putting the instance in the restricted mode and refreshing the db.Some time when ever During the killing and dropping process there are some errors like you can not drop the users which is currently connected .
set termout on
set echo on
spool Kill_sessions_drop_users.log
DECLARE
[code]...
how to get rid of this
View 6 Replies
View Related
Dec 27, 2006
We have a table with several columns (id, title, description, area).The data in the table looks like this
1 sometitle1 description1 USA
2 sometitle2 description2 Germany
3 sometitle3 description3 Japan
4 sometitle4 description4 Honduras
We have Oracle users with their usernames and password. We would like to lock every single user to a certain area.Example:
user 1 may see only records, where AREA=USA
user 2 may see only records, where AREA=Honduras
How can I do this in Oracle. I am using Oracle Enterprise 9.2
View 1 Replies
View Related
Oct 21, 2010
I have 2 tests Oracle instances (a 10g and a 11g) in which somebody added many users (almost 50k), every user having a TEST table with no rows in its schema.
I have to drop these users without recreating the instances, but DROP USER username CASCADE is taking almost 1 minute for each user. This will take me to almost a month.
View 2 Replies
View Related
Aug 6, 2011
Is possible to list all users that have any privilege on a specific table?
View 2 Replies
View Related
Feb 28, 2013
What will happen when a record in a table is getting update by one user and at the same time if another user trying to update the same record?
View 9 Replies
View Related
Sep 26, 2011
I have created a query that display the difference in the tables and column of two users. Some of the column in the new user have been added new which were not there in the old user and some columns from the old user still exits but their data_type is changed.
Now I want to select one more column in the select statement specifying whether the column is modified or has been added.
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'ABC' AND
TABLE_NAME NOT IN (SELECT table_name FROM all_tables where owner = 'ABC'
MINUS
SELECT table_name FROM all_tables where owner = 'XYZ' )
MINUS
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'XYZ'
[code]....
View 9 Replies
View Related
Nov 19, 2010
I do a schema in oracle ... an m my question is if I can create 2 o more users for access to a schema..for example i have schema Project and i need that one administrator and 2 more users with others permission.
View 3 Replies
View Related
Jun 10, 2013
I'm working on a client-server application that uses ODBC to connect Windows XP or Windows 7 PCs to an Oracle 11 database. I use a dedicated account to connect to Oracle, and I get the users Windows login name using the call shown in the title. I use the Windows login name to verify that they can access my application. I've been asked by security conscious folks in our IT department how this information is obtained
My question is: where does Oracle get the value of the users Windows login name? Is it coming from a windows registry key? Environment variable? I heard that it's possible that the ODBC driver is calling a Microsoft Win32 function like GetUserName or GetUserNameEx to retrieve the user ID.
View 1 Replies
View Related
Feb 16, 2011
I type the following command to know what users have access to the database i'm currently connected to:
SQL> select *
2 from all_users
3 /
I know need to know what users are active or inactive. Is there a PL*SQL command for that ?
View 20 Replies
View Related
May 4, 2012
During installation of a database Oracle creates several users. Also when using DBCA in a later stage to add more options Oracle can create more users.
Here's my problem: Public synonyms never get exported using exp(dp)/imp(dp) utility. Ofcourse using public synonyms isn't good practise, but sometimes handy. Unfortunately in our environment they have been used a lot in the passed.
So I want to create an SQL statement that does the trick and returns only all users that were NOT created by Oracle software.
I've tried several things, but can't seem to distinguish Oracle users from Manually created users.
View 7 Replies
View Related
Mar 27, 2013
I want to drop some users in Oracle DB using sqlplus but I am getting error:
SQL> DROP USER test CASCADE;
DROP USER test CASCADE
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected.But when I ran below command to know sessions connected I am not getting any results:
SQL> select sid,serial# from v$session where username = 'test';
no rows selected
View 3 Replies
View Related
Oct 10, 2012
i enable auditing on the database 10.2 for users,
once i query SQL> SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = <Username>
there are many many rows , my question is , are you enable to truncate it from time to time , if not ,is it effect on the performance of the database ?
View 3 Replies
View Related
