SQL & PL/SQL :: Automate Locking / Unlocking Of User Accounts

Nov 1, 2011

I have been plagued by people logging into my database and making changes when a clone is in process.. Having said that ,I am looking to lock accounts and unlock them when I am done.

I envision my code looking something like this:

sqlplus -s / <<END
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF;
set heading off;
spool /tmp/lockusers.sql
select 'alter user ' || username || ' account lock;' from dba_users where username not in (....) and not locked?;
spool off;
END;

sqlplus -s / <<END
@/tmp/lockusers.sql
END;

When it comes time to unlock the accounts I want to be able to unlock those accounts I previously locked and not all of them. Is there a query, I can use that can tell me when the accounts were locked or some other way about going about this so I dont unlock accounts that were locked prior to my lock script running?

View 9 Replies


ADVERTISEMENT

Expiring All User Accounts In Profile

May 16, 2012

I am trying to expire all user accounts belonging to a particular profile my_profile.

The first option was to utilise the utlpwdmg.sql script to update the PASSWORD_LIFE_TIME for my_profile, this worked in Oracle (11g) but caused issues with the change password feature of several applications linking to the database - this option then had to be abandoned.

The next option is to therefore to select all users in my_profile and expire the accounts, what I require is a statement to combine:

- Select USERNAME from DBA_USERS where PROFILE='my_profile';
&
- Alter USER my_user PASSWORD EXPIRE;

So that all users in my_profile have their passwords expired, not just one user my_user.

View 2 Replies View Related

Security :: Lock User Accounts In Oracle Database

Jul 12, 2011

I have a doubt in locking user account in oracle database.I was told to drop some unusable users in database which my clients dont require them.I verified the dependent objects of those users but not sure if these users can be dropped.

Instead can i make those user accounts locked so that they cannot connect to database? Would there be any impact on database performance if i lock those user accounts?

Is locking an account and dropping users is similar in anyway?

View 12 Replies View Related

Express Edition (XE) :: Display A List Of All User And Admin Accounts

Jan 28, 2013

I've installed XE and I want to know what accounts are active. If I log in to SQL*Plus as sys as SYSDBA how can I display a list of all user and admin accounts? I want to know who can log in.

View 3 Replies View Related

Automate Multi User Concurrent Data Loads

Nov 15, 2011

We are designing a three tiered system (client, application/web server, database server) that will allow clients through a web interface to select a text file from the operating system and load that file into a intermediate table (import database table). Many users will do this concurrently and data will load into a single table. The text files come in monthly for about 100 firms. No user is able to insert or update the data of another users data (there is a check out system). Their are about 30 to 40 users that will be using the system doing various functions but it is possible for 10 to 20 users to import data at one time. The files can have anywhere from 2000 to 25000 records at a record length of 398.I am concerned about having a good design strategy as well as decent performance.

Problems with each of the Oracle loaders.

1) External tables - Can not read data text files on the application server(which is where they want the text files to go) secondly you cannot create a instance of a external table. Multiple users will be using the external table to point to different text files and loading at the same time.

2) Sqlloader - is mainly a OS level tool and I am not sure how I could programatically point it to a different text file each time a user wants to load. The client will have to have the ability through code to point sqlloader to the correct file name.

I had a creative approach and was wondering if this would work. I would like to use external tables just like a connection pool. I would propose first a scheduled OS job to move files to the database server. I would create about 20 external tables with 20 different directory objects. Using a stored procedure for the user to call and pass in file name and audit info as needed. I would use a Load lock pool table (my invention) to load the name or a code for the external table in use. The procedure loads this code into my load lock pool table when a external table is in use and deletes the name when the load is completed. The procedure would check through a series of if statements whether a particular external table was in use. If in use (exist in load lock pool table) I would check the next available external table until a external table not in use is encountered. Now potentially 20 users at one time but not likely would be laoding into the same table at one time.My questions

1) Could Oracle handle this strategy? What do I need to consider performance wise with the possibility of so many users loading into a single table at one time?

2) Do any of you maybe have another strategy to do this?

View 8 Replies View Related

PL/SQL :: See All Accounts With Product_code

Oct 22, 2012

I have table with Account, Transaction_date and Product_code columns.

a.)I want to see all the accounts with Product_code = 3120173, i know this is straight forward.

b.) I want to see all the accounts with Product_code = 3120346 but their most recent Product_code should be Product_ code= 3120173. So basically i need to perform a check before i pull Accounts that are on Product_code = 3120346 to see if their most recent(Transaction_date) Product_code= 3120173.

View 2 Replies View Related

SQL & PL/SQL :: Updating Accounts And Displaying Changes?

Aug 13, 2012

I'm trying to write procedures to make updating account owners and the like easier for a group of DBA's.

What I want to do, is create a procedure that displays the changes live.

e.g. If I changed the owner of 5 users from owner 100 to owner 200 it will display:

User test1 owner changed from 100 to 200
User test2 owner changed from 100 to 200
User test3 owner changed from 100 to 200
User test4 owner changed from 100 to 200
User test5 owner changed from 100 to 200

I can not get a loop to work to save my life. Here's what I have to update the account so far...

PROCEDURE UPDATE_OWNER (OWNER NUMBER, NEW_OWNER NUMBER) IS
BEGIN

UPDATE ACCOUNT_TRACKING
SET ACCOUNT_OWNER=NEW_OWNER WHERE ACCOUNT_OWNER = OWNER
AND ACCOUNT_TYPE !='P';
DBMS_OUTPUT.PUT_LINE ('Account Owner '||OWNER||' Changed to '||NEW_OWNER);
END UPDATE_OWNER;

View 4 Replies View Related

SQL & PL/SQL :: Trigger For Updating Two Accounts After Money Transfer

Feb 21, 2012

I get a "ORA-00922 missing or invalid option" after compiling the following trigger:

create or replace
trigger aifer_transfer
after insert on transfer
for each row
[code]......

I want the trigger to affect the transferring account (from_account) with minus the "amount" being transferred from it, and the account transferred to (to_account) with plus the "amount" being transferred to it. My tables looks as following:

TRANSFER
----------------------------------------- -------- ------------
ROWNR NOT NULL NUMBER(9)
PNO NOT NULL VARCHAR2(11)
FROM_ACCOUNTNO NOT NULL NUMBER(8)
TO_ACCOUNTNO NOT NULL NUMBER(8)
AMOUNT NUMBER(10,2)
DATE NOT NULL DATE

ACCOUNT Null? Type
----------------------------------------- -------- ------------
ACCOUNTNO NOT NULL NUMBER(8)
KTNR NOT NULL NUMBER(6)
REGDATE NOT NULL DATE
BALANCE NUMBER(10,2)

View 5 Replies View Related

How To Automate A Data Into Excel

Dec 1, 2011

how to automate a data from oracle into excel...i have a table "emp" in oracle database now i need colums of emp ex:firstname ,last name, id from that table into excel.

so i need a script which when you schedule it it should create a excel file in particular postion,i was told we have to crete a directory from sql and using utl_file then we have to write a script and then schedule that.out look in excel should be

firstname lastname id
sam douglas 1
john - 2

View 1 Replies View Related

Automate Discoverer 10g R2 With No Interaction?

May 20, 2011

I'm trying to create an install script to install Discoverer 10g R2 with its needed patch and opatches applied without any user interaction. I've already created the necesary response files and a batch file to sequence it. The installer should work when the it is placed on a server with the main folder shared and it does so flawlessly.

the user sees a dos window which is kindly stating that he has to wait for the primary installer to finish before hitting enter to start the patch installer.The problem I'm having is that, on slow networks, it takes a while for the primary installer of discoverer 10g to show up a window and of course the user isn't always patient enough to wait for it and hits enter before the primary installer is showing itself causing the patch installer to start before discoverer is completely installed.

Is there a way to avoid this? Or am I wrong in using a batch file to sequence this install? second problem is the needed interaction while applying opatches, can this be automated as well?

here is the contents of my batch file:

net use x: /delete
net use X: \\servername\Oracle_cd\disco10gr2 /persistent:no
@ECHO off
cls
:start

[code]....

View 12 Replies View Related

SQL & PL/SQL :: Automate Inserts From One Schema To Another

Dec 18, 2012

We have two databases one localdb with user rakdb and another one remotely remotedb with user rakdb .We need to be in sync with data in one table called om_item, where the users are inserting data on daily basis and the user sends us the insert script everday to run it on local databse to insert the new records in local database.I managed to create a file which records all the inserts into one text file in one directory.Can we have a scheduler to pick this text file from the specified folder and send mail using utl_mail.

CREATE TABLE ITEM (IT_CODE VARCHAR2(12),IT_NAME VARCHAR2(20));
INSERT INTO ITEM VALUES ('A','AAA');
CREATE OR REPLACE DIRECTORY MY_DIR AS 'C:TEMP';
CREATE OR REPLACE PROCEDURE it_status

[Code]..

Procedure created.

EXEC it_status
HOST TYPE c: empaaaa.txt
INSERT INTO ITEM (IT_CODE, ITEM_NAME) VALUES ('A','AAA');
COMMIT;

View 4 Replies View Related

Client Tools :: How To Automate The Scripts

Sep 25, 2012

I have a proc created which dynamically creates scripts to be executed, e.g. using DBMS_OUTPUT.PUT_LINE it creates the following scripts to be executed:

EXEC D_Q_TST1('PARAM1','PARAM2','PARAM3');
EXEC D_Q_TST2('PARAM1','PARAM2','PARAM3');
EXEC D_Q_TST3('PARAM1','PARAM2','PARAM3');
EXEC D_Q_TST4('PARAM1','PARAM2','PARAM3');
EXEC D_Q_TST5('PARAM1','PARAM2','PARAM3','PARAM4','PARAM5');

Now, what I am really looking for is to explore options where we can spool the results into a file and run another proc to execute all of these proc through it.

let me know if my request is not clear.

View 10 Replies View Related

SQL & PL/SQL :: Automate The Process Of Updating Sequence?

Jan 18, 2012

I have migrated database from postgresql to oracle...All sequences are migrated with their default values...(Start with 1) I already have 213 entries in a table and I want to begin using this for 214th entry ( replace with "start with 214")

How can I automate the process of updating "Start with" value of sequence with the max no of entry on my table every time I migrate data....

I have created a trigger that will automatically insert the next number from the sequence into the id column.

create trigger test_trigger
before insert on test
for each row
begin
select test_seq.nextval into :new.id from dual;
end;
/

View 2 Replies View Related

Backup & Recovery :: How To Automate TDPOSYNC Utility Using PL/SQL

Apr 16, 2012

I have to automate TDPOSYNC utility, it is a IBM tool for oracle backup.I tried except utility of UNIX in shell script, but due to some reason same utility i could not get on production server.Not i asked to use PL/SQL to automate the same.I am facing some problem

1. How to call TDPOSYNC commands from pl/sql

2. How to pass run time input parameter to the TDPOSYNC like user/password, date rage etc.

View 1 Replies View Related

Automate Import From Production To Test Server

Nov 15, 2011

I would like to know that how can i automate the export from production to test server. I need direction to create process to import data from production (server A) to test server (server B).

View 6 Replies View Related

PL/SQL :: How To Automate Task Of Dumping Table Data Into CSV File

Apr 25, 2013

I have written a below procedure to dump the table data to .csv file.But the problem is i have 20 tables which is holding 75 studies data. Means every table will have 75 studies related data.what i supposed to do is i need to export the data from 20 tables for each study. but this procedures requires me to run the procedure 75(studies)*20(tables) times. is there any technique instead of i manually giving the table name and study name , will it take from any text file where we defined 75 studies in that. or easy there any better way .

create or replace procedure dump_table_to_csv1(p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2)
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
[code]........

View 7 Replies View Related

Security :: How To Automate Wallet Open Operation In Standby Instance

Dec 14, 2012

My database is 11.1.0.7 and 11.2.0.3 with TDE tablespace encryption, ASM db storage. The wallet needs to be opened for MRP to work in physical standby database. I already have the solution for the primary instances to automate wallet open (e.g. using startup trigger for 11.1.0.7). However, I cannot find solution to automate wallet open operation in standby instances (to issue ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY ""').

Manual operation everytime standby instance is started is not feasible.

View 1 Replies View Related

Row Locking In Production

Jan 10, 2013

I am facing the row lock issue in production. I have been trying to resolve the issue but i coud'nt. I traced out by using different queries which sql query is locking which but everything looks good.

And i also checked for connections open and close everything is in good place but unable to resolve the issue. we are running a batch file which runs in every night some of the records are processing and if any one record is failed it is blocking another records.My oracle version is oracle 10.2.0

View 1 Replies View Related

SQL & PL/SQL :: How To Know Who Is Locking Table

Apr 12, 2010

I want to Who is locking the table by an SQL statement

View 32 Replies View Related

SQL & PL/SQL :: Locking And Foreign Key Indexes

Sep 1, 2013

Say we have an employee(id_emp) table with a primary key on id_emp. We have also some history tables emp_stuff with columns say (id_emp, dat_event, some_stuff) with primary key id_emp, dat_event.

This means that we have a unique index on (id_emp,dat_event). We also have a foreign key id_emp that references employee(id_emp). When we update id_emp on employee, we still have a lock on emp_stuff. According to this (end of the page) :

Quote:So, in short, with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:

Update the parent table primary key
Delete from the parent table
Merge into the parent table

So is id_emp in emp_stuff considered as indexed (through the unique index of the primary key) or do we have to add an explicit index
like this CREATE INDEX emp_stuff ON emp_stuff(id_emp) to avoide child table locks?

View 33 Replies View Related

Forms :: Record Locking?

Oct 21, 2010

I have developed a form, containing 5 tab pages and also referencing to 5 tables.tab page a of table a some fields are required and some or not.when i open form and start entering record, and then i change mine and dont enter and want to navigate to some other page, it simply allows me to leave it blank and move to some other tab page.

tab page b of table bsome fields are required and some or not.when i open this tab page and one i click in the first field and then do not enter anything in it and want to leave it , it does not allow me to leave blank.what could be the reason. why one tab page allow me to leave blank and navigate and why the other does not?first field of both tab pages are required.

View 2 Replies View Related

Way To Resolve Database Locking

Aug 27, 2013

11.2.0.1  I am still resolving the locking issues in our database Often the delete is blocked by some transaction. The operations said that it is intermittent. Last night they were able to delete the 1M transactions without the locking.But the past days lock often occurred, and they have to bounced the database to release the locks.

 I there a way I can compare what happened last night why the batch was able to delete smoothly , and compare it against the other night where the delete was blocked?By using ASH, AWR, ADDM? I also run now this procedure, to identify the blocking sql statement: 

SQL> exec  dbms_workload_repository.add_colored_sql(sql_id => 'fn3qv2dhsu3nb');        

PL/SQL procedure successfully completed.Can I now run AWR,ASH,ADDM, to identify/capture the sql being run?

View 21 Replies View Related

PL/SQL :: Row Locking During Update Statement?

Aug 9, 2013

Using Oracle 11gr2. If I call the following update statement from session A, session A will lock the row until a commit/rollback statement is issued from session A. If session B calls the same update statement and same row, session B will have to wait until the lock is released in session A. However, the application developers are speaking in terms of threads. Could it be possible that the update statement is called within the same session by multiple requests? If yes, could the case statement be evaluated without the row being locked which could lead to false results? Trying to keep this post brief. tableA has columnA (primary key, number) and columnB (number) 

{update tableAset columnB = case when columnB = 3 then   4  when columnB = 4 then   5   else  columnB    endwhere columnA = 6;}

Could 2 requests (almost at the exact same time) in the same session evaluate columnB as 3. The desired result would be the first request sets the column to 4 and the second request sets the column to 5.

View 4 Replies View Related

Server Administration :: Script To Automate Startup / Shutdown Of Multiple Instance In One Database

Jun 24, 2010

Find an appropriate script to automate Oracle DBs in one server? This db server have 6 instances. We always done the starting up and shutting down manually, although we have a reference script that does this but in Oracle v7.3.4. We do want to include the automatic start/stop of dbconsole for accessing it via OEM.

View 1 Replies View Related

Locking Users By A Table Field Value

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

Archive Processes Locking In Oracle

Nov 9, 2010

I am having a data guard setup done in oracle 10g(10.2.0.4.0) WINDOWS Platform. some of the archive process are locked in primary db side.

PROCESS STATUS LOG_SEQUENCE STAT
-------- ---------- ------------ ----
0 ACTIVE 0 IDLE
1 ACTIVE 804462 IDLE
2 ACTIVE 0 IDLE
3 ACTIVE 0 IDLE
4 ACTIVE 0 IDLE
5 ACTIVE 800011 BUSY
6 ACTIVE 0 IDLE
7 ACTIVE 800009 BUSY
8 ACTIVE 0 IDLE
9 ACTIVE 802335 BUSY
10 ACTIVE 0 IDLE

Shall we kill the locking archive processes? killing archive processes will cause any problem?

View 4 Replies View Related

Performance Tuning :: Locking Of Two Sessions?

Apr 27, 2012

how to avoid oracle deadlock with two sessions. Here is my example, We use Select query with For Update in order to get the unique number from a table. When one user has accessed this query, while the other user tries, System gives a Performace slow and when checked it is due to locking of this select query with For update.

We don't want to use For update WAIT or FOR UPDATE NOWAIT since these will result in missing of number.

View 3 Replies View Related

11g Child Table Update Locking?

May 26, 2013

I've a table TXN1 transaction and has FKs to 3 different tables Account, customer, country and currency. ALL FKs are indexed (bitmap). I am updating TXN1 of amount column about 10,000 rows. (SID 1) As expected, it has taken lock type 3 SX on TXN1. But it has taken on lock type 4 (share) on Account, customer and country. Committing every 10k rows.

At the same time sid 2 is inserting into another TXN2 table which has FK to the same dimensions account, customer and currency. Only FK on ac_id is bitmap indexed. The inserts have taken SX lock(type 3) on tXN2 table (expected). But it is trying to take SX type 3 lock on account, customer, currency tables. typ3 lock taken on CCY but waiting on CST. But It is blocked by sid 1. It has resulted into Enque-TM contention and resulted into anywhere 60-300 secs wait time..

I understand update/delete in parent table results into locking of SX of child tables and need the FKs to be indexed to avoid etc.

1. Why is SID1 taking shared lock on the parent tables Account,customer,country and currency tables? The update statement is not updating any of those FK columns nor referring them in where clause(if it matters!). Is it to ensure that the parent rows are not deleted?

2. Why is SID2 taking SX lock on the dimension tables? Why is it not taking RS lock type 2 on parent rows? Why is SID1 taking shared lock type 4, but not 2?

View 13 Replies View Related

SQL & PL/SQL :: Locking Views (rows Of Data While Updating)

Nov 10, 2011

I have a set of rows based on a complex view from multiple table.

I will be updating some of its columns from front-end . Is there any possible ways to lock those rows of data while updating and no other users can update it;

View 5 Replies View Related

Get Table RowID Of Session That Is Locking In RAC Database

Jun 11, 2012

I am a developer and not a DBA and I need to find th correct query to find the exact rowid of the record locked on a table. This is for a RAC database and locked record can be from the web form in oracle application server. When I try to get the correct row id, I get the following error:

ORA-01410 - Invalid row id For the criteria, the output is Dbms_Rowid.rowid_create(1, -1, 36, 7845, 0), why I get a -1 for the ROW_WAIT_OBJ#?

Additional Information: The lock type is DML and the lock mode is: Row Exclusive, the table is locked and the program is web oracle forms executing.

I am executing the query in Oracle Database 11g Enterprise Edition Release 11.1.0.7.0

How to accomplish getting the correct rowid? Below is the selection criteria I have:

select vs.inst_id,
vs.audsid audsid,
locks.sid sid,

[Code]....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved