How does one manage Oracle database users? (for DBA

How does one manage Oracle database users? (for DBA
by

1 Answer

Answer :

Oracle user accounts can be locked, unlocked, forced to choose new
passwords, etc. For example, all accounts except SYS and SYSTEM will
be locked after creating an Oracle9iDB database using the DB Configuration Assistant (dbca). DBA's must unlock these accounts to
make them available to users.
Look at these examples:
ALTER USER scott ACCOUNT LOCK -- lock a user account
ALTER USER scott ACCOUNT UNLOCK; -- unlocks a locked users
account
ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new
password
831. How does one tune Oracle Wait events? (for DBA
Some wait events from V$SESSION_WAIT and V$SYSTEM_EVENT views:
Event Name: Tuning Recommendation:
db file
sequential
read
Tune SQL to do less I/O. Make sure all objects are
analyzed. Redistribute I/O across disks.
buffer busy
waits
Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to
9i)/ Analyze contention from SYS.V$BH
log buffer
spaces
Increase LOG_BUFFER parameter or move log files to
faster disks
by

Related questions

How does one manage Oracle database users? (for DBA

Description : How does one manage Oracle database users? (for DBA

Last Answer : Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the DB ... locked users account ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new password

What strategies are available for backing-up an Oracle database? (for DBA

Description : What strategies are available for backing-up an Oracle database? (for DBA

Last Answer : The following methods are valid for backing-up an Oracle database: Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file. ... , etc. If your database is in ARCGIVELOG mode, you also need to backup archived log files.

How does one change an Oracle user's password?(for DBA

Description : How does one change an Oracle user's password?(for DBA

Last Answer : Issue the following SQL command: ALTER USER IDENTIFIED BY ; From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password ... this example: SQL> password Changing password for SCOTT Old password: New password: Retype new password:

Who created all these users in my database?/ Can I drop this user? (for DBA

Description : Who created all these users in my database?/ Can I drop this user? (for DBA

Last Answer : Oracle creates a number of default database users or schemas when a new database is created. Below are a few of them: SYS/CHANGE_ON_INSTALL or INTERNAL Oracle Data Dictionary/ Catalog Created ... there should be no problem altering these users to use a different default and temporary tablespace.

How does one start the Oracle Intelligent Agent? (for DBA

Description : How does one start the Oracle Intelligent Agent? (for DBA

Last Answer : One needs to start an OIA (Oracle Intelligent Agent) process on all machines that will to be managed via OEM. For OEM 9i and above:

What is the Oracle Intelligent Agent? (for DBA

Description : What is the Oracle Intelligent Agent? (for DBA

Last Answer : The Oracle Intelligent Agent (OIA) is an autonomous process that needs to run on a remote node in the network to make the node OEM manageable. The Oracle Intelligent Agent is ... events registered in Enterprise Manager; and . Executing tasks associated with jobs submitted to Enterprise Manager.

Are there any undocumented commands in Oracle? (for DBA

Description : Are there any undocumented commands in Oracle? (for DBA

Last Answer : Sure there are, but it is hard to find them. Look at these examples: From Server Manager (Oracle7.3 and above): ORADEBUG HELP It looks like one can change memory locations with the ORADEBUG POKE ... rdbms/lib/oradbx.o; make -f oracle.mk oradbx) SQL*Plus: ALTER SESSION SET CURRENT_SCHEMA = SYS;

How does one tune Oracle Wait events? (for DBA

Description : How does one tune Oracle Wait events? (for DBA

Last Answer : Some wait events from V$SESSION_WAIT and V$SYSTEM_EVENT views: Event Name: Tuning Recommendation: db file sequential read Tune SQL to do less I/O. Make sure all objects are analyzed. Redistribute ... SYS.V$BH log buffer spaces Increase LOG_BUFFER parameter or move log files to faster disks

What tools/utilities does Oracle provide to assist with performance tuning? (for DBA

Description : What tools/utilities does Oracle provide to assist with performance tuning? (for DBA

Last Answer : Oracle provide the following tools/ utilities to assist with performance monitoring and tuning: . TKProf . UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring . Statspack . Oracle Enterprise Manager - Tuning Pack

How does one switch to another user in Oracle? (for DBA

Description : How does one switch to another user in Oracle? (for DBA

Last Answer : Users normally use the "connect" statement to connect from one database user to another. However, DBAs can switch from one user to another without a password. Of course it is not advisable to ... tiger Connected. Note: Also see the su.sql script in the Useful Scripts and Sample Programs Page.

What is OEM (Oracle Enterprise Manager)? (for DBA

Description : What is OEM (Oracle Enterprise Manager)? (for DBA

Last Answer : OEM is a set of systems management tools provided by Oracle Corporation for managing the Oracle environment. It provides tools to monitor the Oracle environment and automate tasks (both one-time and repetitive in nature) to take database administration a step closer to "Lights Out" management.

What is Oracle Label Security? (for DBA

Description : What is Oracle Label Security? (for DBA

Last Answer : Oracle Label Security (formerly called Trusted Oracle MLS RDBMS) uses the VPD (Virtual Private Database) feature of Oracle8i to implement row level security. Access to rows are restricted according ... , controlled and managed from the Policy Manager, an Enterprise Manager-based GUI utility.

What is the most important module in Oracle Financials? (for DBA

Description : What is the most important module in Oracle Financials? (for DBA

Last Answer : The General Ledger (GL) module is the basis for all other Oracle Financial modules. All other modules provide information to it. If you implement Oracle Financials, you should switch your current GL ... with it first to give your implementation team a chance to be familiar with Oracle Financials.

What is Oracle Financials? (for DBA

Description : What is Oracle Financials? (for DBA

Last Answer : Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can ... Oracle Sales Analysis: Allows for better forecasting, planning. and reporting of sales information.

Why is Oracle not using the damn index? (for DBA

Description : Why is Oracle not using the damn index? (for DBA

Last Answer : This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster ... with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.

Where can one get a list of all hidden Oracle parameters? (for DBA

Description : Where can one get a list of all hidden Oracle parameters? (for DBA

Last Answer : Oracle initialization or INIT.ORA parameters with an underscore in front are hidden or unsupported parameters. One can get a list of all hidden parameters by executing this query: select * from SYS.X$KSPPI ... 1,1)='_' order by a.ksppinm; Remember: Thou shall not play with undocumented parameters!

Can one import/export between different versions of Oracle? (for DBA

Description : Can one import/export between different versions of Oracle? (for DBA

Last Answer : Different versions of the import utility is upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. ... database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.

What third party tools can be used with Oracle EBU/ RMAN? (for DBA

Description : What third party tools can be used with Oracle EBU/ RMAN? (for DBA

Last Answer : The following Media Management Software Vendors have integrated their media management software packages with Oracle Recovery Manager and Oracle7 Enterprise Backup Utility. The Media Management ... and Recovery Sterling Software's SAMS:Alexandria - formerly from Spectralogic Sun Solstice Backup

Does Oracle write to data files in begin/hot backup mode? (for DBA

Description : Does Oracle write to data files in begin/hot backup mode? (for DBA

Last Answer : Oracle will stop updating file headers, but will continue to write data to the database files even if a tablespace is in backup mode.In backup mode, Oracle will write out complete changed blocks ... ). Because of this, one should notice increased log activity and archiving during on-line backups.

How does one backup a database using the export utility? (for DBA

Description : How does one backup a database using the export utility? (for DBA

Last Answer : Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back-up the physical data ... exports include more information about the database in the export file than user level exports.

Why and when should I backup my database? (for DBA

Description : Why and when should I backup my database? (for DBA

Last Answer : Backup and recovery is one of the most important aspects of a DBAs job. If you lose your company's data, you could very well lose your job. Hardware and software can always be ... things that will get you. Most failed recoveries are a result of organizational errors and miscommunications.

How does one create a standby database? (for DBA)

Description : How does one create a standby database? (for DBA)

Last Answer : While your production database is running, take an (image copy) backup and restore it on duplicate hardware. Note that an export will not work!!! On your standby database, issue ... the standby database, stop the recovery process and activate it: ALTER DATABASE ACTIVATE STANDBY DATABASE;

What is a Virtual Private Database? (for DBA

Description : What is a Virtual Private Database? (for DBA

Last Answer : Oracle 8i introduced the notion of a Virtual Private Database (VPD). A VPD offers Fine-Grained Access Control (FGAC) for secure separation of data. This ensures that users only have ... the DBMS_RLS (Row Level Security) package. Select from SYS.V$VPD_POLICY to see existing VPD configuration.

How can one dump internal database structures? (for DBA

Description : How can one dump internal database structures? (for DBA

Last Answer : The following (mostly undocumented) commands can be used to obtain information about internal database structures. o Dump control file contentsalter session set events 'immediate trace name CONTROLF level 10 ... ); print x alter session set events 'immediate trace name blockdump level 50360894' /

What database events can be set? (for DBA

Description : What database events can be set? (for DBA

Last Answer : The following events are frequently used by DBAs and Oracle Support to diagnose problems: " 10046 trace name context forever, level 4 Trace SQL statements and show bind variables in trace output. " 10046 ... . On Unix, you can type "oerr ora 10053" from the command prompt to get event details.

What is a database EVENT and how does one set it? (for DBA

Description : What is a database EVENT and how does one set it? (for DBA

Last Answer : Oracle trace events are useful for debugging the Oracle database server. The following two examples are simply to demonstrate syntax. Refer to later notes on this page for an explanation of what these ... changes to the INIT.ORA file will affect all sessions once the database has been restarted.

My database is down and I cannot restore. What now? (for DBA

Description : My database is down and I cannot restore. What now? (forDBA

Last Answer : Recovery without any backup is normally not supported, however, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad). This utility reads data in the ... as an absolute last resort and will most likely cost your company a lot of money!!!

My database was terminated while in BACKUP MODE, do I need to recover? (for DBA

Description : My database was terminated while in BACKUP MODE, do I need to recover? (for DBA

Last Answer : If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the ... : ALTER DATABASE END BACKUP; The above commands need to be issued when the database is mounted.

What database aspects should be monitored? (for DBA

Description : What database aspects should be monitored? (for DBA

Last Answer : One should implement a monitoring system to constantly monitor the following aspects of a database. Writing custom scripts, implementing Oracle's Enterprise Manager, or buying a third-party monitoring product ... up? . Objects getting close to their max extents . User and process limits reached

How does one backup a database using RMAN? (for DBA

Description : How does one backup a database using RMAN? (for DBA

Last Answer : The biggest advantage of RMAN is that it only backup used space in the database. Rman doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database ... directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.

How does one do on-line database backups? (for DBA

Description : How does one do on-line database backups? (for DBA

Last Answer : Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example. ALTER TABLESPACE xyz ... files while in backup mode. This will lead to excessive database archiving and even database freezes.

How does one do off-line database backups? (for DBA

Description : How does one do off-line database backups? (for DBA

Last Answer : Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When ... at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.

How does one see the uptime for a database? (for DBA

Description : How does one see the uptime for a database? (for DBA

Last Answer : Look at the following SQL query: SELECT to_char (startup_time,'DD-MON-YYYY HH24: MI: SS') "DB Startup Time" FROM sys.v_$instance; Marco Bergman provided the following alternative solution: SELECT ... from V$INSTANCE JUL, V$INSTANCE SEC where JUL.KEY like '%JULIAN%' and SEC.KEY like '%SECOND%';

Can one rename a database user (schema)? (for DBA)

Description : Can one rename a database user (schema)? (for DBA)

Last Answer : No, this is listed as Enhancement Request 158508. Workaround: Do a user-level export of user A create new user B Import system/manager fromuser=A touser=B Drop user A

What database block size should I use? (for DBA)

Description : What database block size should I use? (for DBA)

Last Answer : Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should ... 8K. This is because volume manager products use 8K blocks (and this is not configurable).

How does one create a new database? (for DBA)

Description : How does one create a new database? (for DBA)

Last Answer : One can create and modify Oracle databases using the Oracle "dbca" (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal ... ='/u02/oradata/'; ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/'; CREATE DATABASE;

What are different Oracle database objects?

Description : What are different Oracle database objects?

Last Answer : TABLES -VIEWS INDEXES -SYNONYMS -SEQUENCES -TABLESPACES etc

What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?

Description : What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?

Last Answer : Procedures and Functions,Packages and Database Triggers.

What are the different methods of backing up oracle database ?

Description : What are the different methods of backing up oracle database ?

Last Answer : Logical Backups - Cold Backups - Hot Backups (Archive log)

List the Optional Flexible Architecture (OFA) of Oracle database? or How can we organize the tablespaces in Oracle database to have maximum performance ?

Description : List the Optional Flexible Architecture (OFA) of Oracle database? or How can we organize the tablespaces in Oracle database to have maximum performance ?

Last Answer : SYSTEM - Data dictionary tables. DATA - Standard operational tables. DATA2- Static tables used for standard operations INDEXES - Indexes for Standard operational tables. INDEXES1 - Indexes of ... - Temporary purpose tablespace TEMP_USER - Temporary tablespace for users. USERS - User tablespace.

What is user Account in Oracle database?

Description : What is user Account in Oracle database?

Last Answer : A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.

What is user Account in Oracle database?

Description : What is user Account in Oracle database?

Last Answer : An user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges.

What is an Index ? How it is implemented in Oracle Database ?

Description : What is an Index ? How it is implemented in Oracle Database ?

Last Answer : An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table comman (Ver 7.0)

What are the basic element of Base configuration of an oracle Database ?

Description : What are the basic element of Base configuration of an oracle Database ?

Last Answer : It consists of one or more data files. one or more control files. two or more redo log files. The Database contains multiple users/schemas one or more rollback segments one or more tablespaces ... (Data Base Write) ARCH (ARCHiver) CKPT (Check Point) RECO Dispatcher User Process with associated PGS

What are the components of logical database structure of Oracle database?

Description : What are the components of logical database structure of Oracle database?

Last Answer : There are tablespaces and database's schema objects.

What are the components of physical database structure of Oracle database?

Description : What are the components of physical database structure of Oracle database?

Last Answer : Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

Name the three major set of files on disk that compose a database in Oracle

Description : Name the three major set of files on disk that compose a database in Oracle

Last Answer : There are three major sets of files on disk that compose a database. All the files are binary. These are ∙ Database files ∙ Control files ∙ Redo logs The most important of these ... actual data resides. The control files and the redo logs support the functioning of the architecture itself.

How many Maximum Datafiles can there be in Oracle Database?

Description : How many Maximum Datafiles can there be in Oracle Database?

Last Answer : Regardless of the setting of this paramter, maximum per database: 65533 (May be less on some operating systems) Maximum number of datafiles per tablespace: OS dependent = usually 1022 Limited also ... instance Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks.

How do you control number of Datafiles one can have in an Oracle database?

Description : How do you control number of Datafiles one can have in an Oracle database?

Last Answer : The db_files parameter is a "soft limit " parameter that controls the maximum number of physical OS files that can map to an Oracle instance. The maxdatafiles parameter is a different - "hard limit" ... value is 32. The maximum number of database files can be set with the init parameter db_files.

How does one add users to a password file? (for DBA

Description : How does one add users to a password file? (for DBA

Last Answer : One can select from the SYS.V_$PWFILE_USERS view to see which users are listed in the password file. New users can be added to the password file by granting them SYSDBA or SYSOPER privileges, or by using the orapwd utility. GRANT SYSDBA TO scott;