I frequently need to run queries to do things like sum space used by each schema, but excluding all of the default system users. Is there a better way to identify the default users than listing all 30 of them in a "not in" expression? For example in sql server to query user databases you just say "with database_ id > 4". Is there something simple like that in Oracle to identify all default users?
View 6 RepliesI 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.
My user tablespace has been full and just I create new tablespace like User_Tablespaces.
I want to Alter Default Tablespace and Quota define of all existing user on User_Tablespaces.
How we do in a SQL Query or PL/SQL query that can all existing users has been alter at a time in a single query.
how many default users available in oracle 9i version.
View 4 Replies View RelatedHow to import dump into specific tablespace instead of default tablespace users.
I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .
I have an agent, actor, movie and producer table, I want to list the details of all agents who represented actors casting in movies produced by "X" (X is the name of producer). I want to know the command for doing this.I have the following tables:
table agent(
agentID(pk),
fname,
sname,
city,
sex
[code]...
I have a producer fname as Xavion in the table.
I have a table with data in below format:
Table A
Id Value
1 R
2 S1
2 S2
3 T
the o/p in below format:
Col
R|S1|T
R|S2|T
The general is that multiple value for any given id must be aggregated separately.
I used following statement (user SYS as SYSDBA)
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,
decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),
decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE'),
decode(bitand(ksppiflg,4),4,'FALSE', decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),
[code].......
to list hidden parameters. However, when using it on RAC I found that only a singe instance data is displayed.
Is there anyway to get a listing of the files in a specified directory on an ftp server using webutil?
View 2 Replies View RelatedIs it possible to use a compound condition on a form listing say 15 records utilizing the F11 key?(ie. order column <10 and >5)
View 2 Replies View RelatedI was looking for a way to see if a default value for a procedure was passed NULL or it got NULL by default. [URL]
View 11 Replies View RelatedI want to insert data from table A and dump into table B.The data in Table A looks like this:
Level_IDCode Type
ETY_1A......... 100000116
ETY_1B......... 100000116
ETY_1C......... 100000116
ETY_2AA........ 100000117
ETY_2AB........ 100000117
ETY_2AC........ 100000117
ETY_2BA........ 100000117
ETY_2BB........ 100000117
ETY_2BC........ 100000117
ETY_3AAA....... 100000118
ETY_3AAG....... 100000118
ETY_3AAI....... 100000118
ETY_3AAX....... 100000118
[code]....
And I want to dump the data into Table B which will look like this:
Level_ID Code Type Parent_cd
ETY_1A.........100000116null
ETY_1B.........100000116null
ETY_1C.........100000116null
ETY_2AA........100000117A
ETY_2AB........100000117A
ETY_2AC........100000117A
ETY_2BA........100000117B
ETY_2BB........100000117B
ETY_2BC........100000117B
ETY_3AAA.......100000118AA
ETY_3AAG.......100000118AA
ETY_3AAI.......100000118AA
[code]....
I need to populate a column called Parent Cd with the values that sits at the base level of the hierarchy. So, if the code A sits at the base level then its parent is NULL and the parent for code AA, AB, and AC will be A and parent for AAA, AAG will be AA and so on.The level_IDs are upto 10 and there are close to 400,000 records.
I would like to know, how to:
1) search for a given value in a row, and
2)in that row, identify the name of the column that contains the value
Search scope is only a single row.
I have a situation where I need to check if a row in the table locked i.e (Select for Update with NO WAIT applied on it). If yes go fetch the next row .
View 4 Replies View RelatedI am new to Oracle and I inherited an existing database. I have a materialized view on a table that uses a materialized view log. I have been asked to see if it is possible to categories the rows by change type (inserted, updated or deleted) and populate another materialized view or table accordingly. If this is possible then how can it be achieved.
View 1 Replies View RelatedI have an update trigger that runs a package when fired. I only want this package to run if it is NOT fired from an Oracle Forms application. Ie. A user might update table ABC from an Oracle Form. In this instance the trigger would fire, but I don't want to run the package within the trigger. If the user logs onto PL/SQL developer and updates the table, the trigger would fire and the package must run.
I was thinking of using the field 'program' from v$session. If it is null (it's being fired from an Oracle Forms application) if it has a value (it's coming from SQL*Plus / Pl/sql Developer).
I inherited an existing database. I have a materialised view on a table that uses a materialised view log. I have been asked to see if it is possible to categorise the rows by change type (inserted, updated or deleted) and populate another materialised view or table accordingly. If this is possible then how can it be achieved.
View 12 Replies View RelatedI'm sure you are all familiar with proxy users, they've been around since 9i: orcl> create user low identified by low;
User created.
orcl> create user high identified by high;
User created.
orcl> grant dba to high;
Grant succeeded.
orcl> alter user high grant connect through low;
User altered.
orcl> connect low[high]/low
Connected.
orcl> sho user
USER is "HIGH"
orcl>
Is there any way that I can find out which of the current sessions was proxied, and through what user? I know that from within the session I can query my userenv context and find out, but I can't see how to do it otherwise. It must be possible: the audit trail records both the real user and the proxy user.
I need to increase the size of shared pool for that i need to change the size of parameter sga_target and sga_max_size these are static parameter so that I need to change in spfile first then start instance then these parameter will get change. But when I create pfile from spfile it gives error:
ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
I am on Oracle 11.2.0.3 on Linux. In my production database, I am getting this alert, in the alert log:
--the below two lines are from alert log.
Error 604
trapped in 2PC on transaction 50.73.546578. Cleaning up.
--query from a data dictionary view
SQL> select count(*) from DBA_2PC_PENDING;
COUNT(*)
----------
1
When I query the production database I see that there is one row in the DBA_2PC_PENDING view. But I dont' know how to identify which are the databases that are involved in this distributed transaction. That is my first issue - how to identify which are the two databases that are involved in the distributed transaction?
i have a problem when i try to login to oracle...i found this message error.
ora-01033 oracle initialization or shutdown in progressafter i logged on with / as sysdba...i did the following
SQL> shutdown immediate;
ORA-01507: database not mountedORACLE instance shut down.
SQL> startup mount;
[code]....
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
HOW TO LOGOUT ALL USERS FROM ORACLE IN ONE COMMAND
View 2 Replies View RelatedI want to use default value in pl/sql , i'm executing below code
SET serveroutput ON
DECLARE
l_deptno INTEGER DEFAULT 10;
v_dname VARCHAR2(200);
[Code]...
SQL> @test.sql
Enter value for deptno: 40
dname value=OPERATIONS
I tried with NULL to use its default value but it uses NULL(which does make sense)
SQL> @test.sql
Enter value for deptno:
error
How to use the default value of the passed argument?
expected O/P
dname value=ACCOUNTING
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
....
how to see password of users in 11g
View 11 Replies View RelatedI 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 RelatedI'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?
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.
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.