There is a scenario: I dropped all objects of 3 users and dropped other 2 users and then I checked the space from dba_segments it reflects decreased space which is perfectly fine. Problem is that when I checked the space of physical datafile on disk it remains same. How can I restore or shrink or regained the space after dropping objects and users to maintain my storage requirements.
View 1 Replies
I have come across a requirement to generate a report of all the users in Hyperion shared services console along with their previlages and also the different licences consumed by the users like Essbase, HFM, Hyperion Planning, Workforce planning etc.... The provisioning report in Shared services is not giving these details.
View 0 Replies View Relatedwe 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
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?
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
I have a data base for the bank application development. So there might be more than one sessions of the application running on the data base as well as there might be more than one data base sessions being connected from the dbas.
There is refresh procedure to refresh the data base from the baseload scripts ,So for that first we need to disconnect the users (Kill the sessions) and then drop the users then recreate the users and populate the users with the base load data.
I have prepared the killing and dropping user script in the following manner, Most of the times it is working fine and killing the users but some times it is not able to kill the drop the users it is erroring out with the error that can not dorp the users as there are active sessions running ,
set termout on
set echo on
spool Kill_sessions_drop_users.log
DECLARE
v_alt_stmt VARCHAR2(1000) := 'ALTER SYSTEM DISCONNECT SESSION ''';
v_kill_stmt VARCHAR2(1000);
[code].......
--This will kill all the sessions except the current session
CURSOR cur_usr_dtls IS SELECT username FROM all_users WHERE username IN ('ACTIVITI','TP','TPCORR','TPTUX','TPVIEW');
v_dr_stmt VARCHAR2(1000);
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
DBMS_OUTPUT.PUT_LINE('The database has been put in the restricted mode');
FOR v_cur_ssn IN cur_session
[code]........
--Here we are making the processor to wait for 60 mins so that that the killing will happen at the OS label
DBMS_LOCK.SLEEP(60);
FOR v_cur_usr_dtls IN cur_usr_dtls LOOP
v_dr_stmt :='DROP USER '||v_cur_usr_dtls.username ||' CASCADE';
BEGIN
EXECUTE IMMEDIATE v_dr_stmt;
[code]........
I create different database users like STORE, PAYROLL, GL , SYSADMIN, COMMON to contain objects accordingly then I create another user ABC and grant DBA privileges to all users.
After that I grant SELECT/INSERT/UPDATE/DELETE privileges on each and every objects of STORE/PAYROLL/GL/SYSADMIN/COMMON users to ABC then create synonyms of STORE/PAYROLL/GL/SYSADMIN/COMMON users objects into ABC.
Actually, I want to use single user of ABC to build/execute my application but I think I am doing something wrong because whenever I tried to compile my form, its showing this error.
I am using the below command,
lv_ret := WEBUTIL_FILE_TRANSFER.AS_To_Client_with_progress(lv_clnt_file, lv_srvr_file,
'Download from Application Server in progress', 'Please wait');
to download a file to my H: drive.Here lv_ret is a boolean variable.The file is not downloaded to my H drive when there is no enough space.How to capture that error?
I am having an issue with the LOBs in our Database. Our production DB is about 71G right now - if i do export/import using the DataPump - I see the data volume becomes 40G. I am sure its because of the LOBs in my Database. Our application uses lots of LOB datatype and I think the space is not being released.
The challenge is I cant do a full export/import in my Production DB as its a 24/7 system and to do export/import I need min. 5 hrs - which my business will not permit. I can I claim the unused space here. I have used Segment Advisor - but it has only given me 6G of space gain.
Is there any script I can try to use to reclaim space in the database.
I heard that USERS tablespace should not contain any other application schema objects.
If the above statements is true , why it should not contain other schema objects ?
we have a quarter rack with 3 cell (storage) servers. Actually I don't know which exact disks we have inside.
How could I check if we have unallocated disk space in the storage server? I would like to do this command line.describe me the steps?
I want to compare two users on different databases, actaully there are two users one in user a on database a and another user b on database b they have same tables, and everytime when a table or object is created on user a (database a) i will take the table name ,procedure or any other object from user_objects based on ddl_created date and then i need to recreate the same on user b on database b, is there a way to find out tables which are not only created but also i need to check whether if there is any column added or any change in procedure or any other objects.Is there a way to generate the scripts based on list of objects selected from user_objects.
all i want is.
a)Find out the list of objects added along with creation scripts
b)find out the list of objects modified along with creation scripts.
I'm looking for a way to store an encypted numeric value in one field in a table (so that it appears encrypted even to a DBA) and to display the unencypted value in Apex forms and interactive reports for some users but not others.
View 4 Replies View Relateda newbie dba here..
select TABLESPACE_NAME,
sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space
group by TABLESPACE_NAME
/
result output is attached .txt file.
the SYSTEM & USERS table space shows only <10 mb free space. Is it a bad sign? What I should do ?
select sum(bytes/1024/1024) from dba_segments where owner='IPPS';
Does the above finding means that IPPS has use up 'this amount' of space in the database from all his objects?
My organisation is currently discussing different storage options for the database storage. Our production database is nearly 2TB and we do not want to continue with the existing NetApp storage (we use a 2 node RAC running 11.2.02 with nfs filesystem from NetApp filer).
We were looking at different options and came across Nimble Storage, they are very fast growing company aiming mid-range storage customers. The initial talks and demonstration looked very promising in terms of IO performance (they claim 40,000 - 60,000 IOPs for their CS400 series Nimble Storage array) and other options they are providing but we understand that majority of their customers are using it for VDI and other infrastructures.
They have demonstrated us using if for Oracle database with ASM storage over iSCSI LUNs. We are yet to do the POCs and benchmarking.
Has anyone come across Nimble Storage for running Oracle databases?
I have one table as per the attachment, with id(Primary key),level,name and Srno.Now I want to maintain Sr. if user transfers level 2 of one block (level 1) to another block.is it possible through any oracle function.
View 8 Replies View RelatedI do need to mantain a table with the las 13 of a month, for example:
ID Month_ID MONTH_MAT
1 201206 201106
2 201206 201107
3 201206 201108
4 201206 201109
5 201206 201110
[Code]....
Kind of a year-to-date table but this one is 13 months one.
I have been trying to create the code from scratch
I have thought of using a Function:
Query: SELECT TRUNC(ADD_MONTHS(SYSDATE, -13),'MM') FROM dual;
Result: 01-MAY-11
How can i stretch field dynamically as per the requirement.
i use horizontal elasticity and also format trigger on the field but cant find the expected output.... How can i achieve the same...
I have a requirement like below
I have 3 tables (Prospect,customer,user) and I have a contact_dtls table .
In prospect table Prospect_id is the primary like this customer_id in customer table and user_id in the user table
in the contact_dtls table I have a column contact_id and other cols.The contact_id can be from prospect_id,customer_id and user_id in other words we can tell the prospect can be a contact or the customer can be a contact and the user also can be a contact also.
Now the question is "is it possible to maintain primary key and foreign key relationship with this design that means the prospect_id,customer_id and user_id would be the foreign keys to the contact_dtls table.
We need to maintain the day 0 value of special character "ÿÿ" for application. If i use the special character directly in sql file , it is getting inserted as junk character "?"
If i use workaround by using chr(255) function i am getting below behavior.
select chr(255) from dual
---------------------------
ÿ
However below query gives null value.
select chr(255)||chr(255) from dual
I have a form with fields like, UserName, User ID Email Address. This is used to create new users & edit existing user information. I wanted to make the Email field mandatory. So i made the email field as 'Required' via Proerty Pallette. So when ever i create new users after this change, i get a message saying 'Field must be required' when i try to skip this email field.
However when i edit existing user information and try to save the data with no email address i am able to save. So, where when i try to save a user info with no email address, i should get a warning saying 'email address is required'. How to go about it and if i wil have to create any trigger.
I have a requirement to develop a form from scratch level.The form contain search criteria and a search button.On pressing the search button it will display the search result just below the search criteria.The search result contain two button, EDIT and DELETE button in each row.
View 3 Replies View RelatedI want to create a trigger with the requirement of achieving Primary key functionality.
I have a "EMP" table. the table already contains a duplicate data on "EMPNO" column. i want to restrict entering duplicate data further into table for that i want to create a trigger.
where can i find different triggering events of DML(like update, delete etc...)and DDL(database and schema level).
I want to replace double space with single space and also remove junk characters from the data. How can I do that?
CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
I am using Oracle developer Suite 10g, and i want to customize the position of ALERT message on the screen as per requirement.
View 4 Replies View RelatedI got table with column of XML codes like this:
select xmltype ('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
<parameter name="result"><value>success</value></parameter>
<parameter name="showBirthday"><value>false</value></parameter>
<parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
</parameters>'
) val
from dual;
And I need to select records from table where xml code complies with requirement:
name="result" equals success.
name="result"><value>success</value>
What is the good practice to maintain RMAN catalog ?
RMAN backup and recovery is fine but to delete old backups it takes a lot of time.
Even if I delete backups manually,RMAN takes a lot of time to delete expired backup command.
What can be done to increase the speed of delete obsolete backup and delete expired backup operations ?
I was considering a solution to maintain a replicated copy of a database in a remote office. However we are using SE One edition of oracle, so native support for dataguard is not available. There definitely should be some scripting solutions for this task, but I can't find any to date.
View 7 Replies View RelatedJust now sysaux resized to 600m from 250m >>
Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE.
Usually we take AWR weekly once. Some times we did ADDM report and ASH.
CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024) from dba_data_files where tablespace_name = 'SYSAUX';
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024) AUT MAXBYTES/(1024*1024)
SYSAUX /u01/app/oracle/oradata/test/sysaux01.dbf 600 YES 32767.9844
CODEsql> @SCRIPT.SQ
TABLESPACE TOTAL_SPACE(MB) USED_SPACE(MB) FREE_SPACE(MB) % Used % Free
SYSAUX 600 248 352 41.33 58.67
1. What's the best SOLUTION ?
2. Can i shrink sysaux tablespace ?
3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ?
4. What could be the reason for growth? Is there any way to free the space from sysaux table space?
