User Without Tablespace
Feb 14, 2011
I have come across a small incident in oracle. I have user that exists on the database and is open but user's tablespace is not listed under v$tablespace.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.4.0 - 64bit Production
PL/SQL Release 9.0.1.4.0 - Production
CORE 9.0.1.2.0 Production
TNS for Solaris: Version 9.0.1.4.0 - Production
NLSRTL Version 9.0.1.4.0 - Production
SQL> select USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username ='METERING';
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ -------------------------------- ------------------------------ ------------------------------
XYZ OPEN TS_DATA1 TS_TEMP
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
TS_TEMP
STATSPACK_DATA
RBS
INDX
DATA
6 rows selected.
View 6 Replies
ADVERTISEMENT
Jul 25, 2013
> conn sys as sysdba;
enter password:TIGER
>create user ram identified by ram;
>grant create session to ram;
>conn scott/tiger
>create table ram.emp2
as select * from emp;
>ora-error:- 01520 NO PRIVILEGES ON TABLESPACE 'USERS'
this is the query and i want to create emp2 table for user ram while i am connected to scott,
ealier i was able to do this but now oracle shows error 01950,
then i connected to sys again and ran this command >alter user scott quota unlimited on scott;
but it also not worked
View 2 Replies
View Related
May 13, 2013
just a short generally Oracle question with (i think) many solutions:
How is it possible to show the rights of a user on a tablespace?
View 13 Replies
View Related
Apr 13, 2012
i have created a new tablespace and assigned it to new user. What are the minimum no. of grants i should give, so that the user can made any kind of changes to all the objects within the tablespace and cant access other tablespace.
View 3 Replies
View Related
Feb 27, 2012
I have a database in which there are lots of programs running by default user or programs started by some oracle users.
Now, I know that the USER tablespace is growing at very high speed - But I dont know which user/program is causing this growth of USER tablespace.
how to identify the program/user who is responsible for this growth of USERS tablespace.
View 1 Replies
View Related
Oct 27, 2010
I need query to retrive the default tablespace of the current user, when the user is not a dba, i.e. user doesn't have access to dba_users table.
View 2 Replies
View Related
Oct 1, 2012
I have deleted a user with CASCADE option.
After deletion, the size of tablespace should be reduced.
But the tablespace size is same as before deletion of user.
Currently , that USER is deleted and not present in the database . Still space is not released after user deletion.
View 4 Replies
View Related
Aug 12, 2011
i have been trying to create a user with full access on default Tablespace so that i can access all the objects without any restrictions for the new user.
View 7 Replies
View Related
Jan 18, 2012
How 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 .
View 2 Replies
View Related
May 27, 2011
i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?
creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces
View 9 Replies
View Related
Jan 26, 2011
My database version is
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
My os version is
Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed
Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64
GNU/Linux
My database is OLP system.
My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?
Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.
View 7 Replies
View Related
Jan 6, 2012
I have one database which is recently upgraded from oracle 8.1.5 to oracle 10.2.0.4.The database is having around 300 tablespace and total size of the database is 1.5 TB.
The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.
This database is a mission critical database and very less downtime can be allowed.
View 6 Replies
View Related
Apr 25, 2011
There is a requirement in my database that I want to restrict the user from directly running queries on database from third party tools such as pl/sql developer and toad.
There is a utility in SQL product_user_profile through which this can be done but it is only restricted if you run the query through sql plus. If I want to restrict and (give suppose select,insert) to a user for directly running queries through PL/SQL.
View 1 Replies
View Related
Nov 15, 2010
I created an externally authenticated user in database. And can login without password with below syntax.
SQL> connect / @TESTDB
Connected.
SQL> show user;
USER is "SCOTT"
This scott user has a proxy permission to another DBuser PROXY_USER. Previously I used to login using below syntax.
connect scott[proxy_user]/password_for_scott@TESTDB
So now, what syntax should be used for this "ExternallY Authenticated" user to login as a proxy user?
View 10 Replies
View Related
May 28, 2011
how can i assign permissions to one user to add,delete,edit data and other user should be able to perform all functions or selected functions
View 12 Replies
View Related
Mar 31, 2010
I would like to know if 'user creation definition' is exported in user mode export if export is done with DBA role..If it is Not, does it mean we always need to precreate the user before we import the dump created using 'user mode export'?
View 2 Replies
View Related
Mar 11, 2011
We 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";
)
View 3 Replies
View Related
Oct 20, 2012
- we have user id parameter.can we update the parameter(:parameter.p_userid) before firing "WHEN NEW FORM INSTANCE TRIGGER"(when new form instance trigger contain code for tree node), for login another canvas as per user rights?
- i have created a login form in one canvas.
- also tree node Hierarchy form created on another canvas. now we want to login through login screen, after login only those forms should show in tree Hierarchy which users have rights. for this purpose we want to pass the parameter of userid before connecting to tree node form through the following query
SELECT COUNT (*)
INTO v_count
FROM usersinfo
WHERE usersname = :USERBLOCK.usernames AND passwords = :USERBLOCK.passwords ;
[code]...
Every thing is working but when we click on button in login form at that time parameter.p_userid will return null. because above code is define on button click in login screen, i cannot understand where i need to define the above code .
View 1 Replies
View Related
Aug 13, 2012
User session has expired. I have to enable user without changing the password of the user.
View 1 Replies
View Related
Dec 21, 2010
We have the database user called c88888 and is locked yesterday. I came to this with the following query.
select * from dba_users where username like 'C88888';
Due to invalid password the user was locked. Can we track who hit the database with the wrong password?
View 5 Replies
View Related
Apr 16, 2013
I Created One Trigger as Follows
CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT
ON table1
FOR EACH ROW
[code]......
Here , I Want To Insert The Data From My User To Test User . In This Situation When I Execute The Above Trigger It Shows The Error PL/SQL: ORA-00942: table or view does not exist
View 3 Replies
View Related
Feb 9, 2011
I have two users say A and B. I have all the tables,views,indexes, types,procedures,packages etc. User B wants to access all the objects from user A.
View 4 Replies
View Related
Nov 9, 2011
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"
View 12 Replies
View Related
Dec 11, 2012
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]........
View 6 Replies
View Related
Sep 12, 2011
User A contains tables, views, LOB's, types, procedures, triggers, sequences, indexes, synonyms. User B wants to have read-only privilege on the objects of User A.
I can provide select on privilege on tables and views. How about providing read-only privileges to other objects?
View 1 Replies
View Related
Apr 23, 2011
I want to perform something like -
Conn xyz/passwordxyz
grant create procedure to 'xyz';
I want to give permissions to my current user 'xyz' , I am able to give permissions to user using system user but Is there any way to grant permissions to user 'xyz' while I am using 'xyz' user.
View 2 Replies
View Related
Mar 25, 2013
I have two databases (say DATABASE 1 with user USER1 with table A and DATABASE2 with user USER1 with table A). I want to select the table A in USER1 DATABASE2 from DATABASE1 USER1.
View 5 Replies
View Related
Aug 23, 2011
I have enabled Auditing in my oracle Database but I am not able to see any database for the operations of sys user or any other user in my "SYS.AUD$" and "SYS.FGA_LOG$" tables.
Value for the parameter "AUDIT_TRAIL" is set to "db,extended".
I am working as "SYS" user and I have shutdown and again startup the database but neither there was any information in both the tables nor I can see any files at the destination specified by "AUDIT_FILE_DEST".
View 10 Replies
View Related
Dec 6, 2010
when a dba user'AA' try to access other user'BB' object it gives error pls-00201 identifier 'BB.function_name' must be declared. However the procedure of BB user are being accessible.
View 17 Replies
View Related
Sep 22, 2011
how to copy tables from one user to another user using pl/sql
View 18 Replies
View Related