Oracle Apps Basics

Day today activities of DBA

How can I see if my database is running in Archive Log Mode

Answer: Startup Svrmgrl and issue the command archive log list. That will show you if you are running in an archive or noarchive log mode and also various details about log switches etc.

How to Increase the size of a tablespace

Answer: There are 2 ways to increase the size of tablespace.

1. Add an Extra Data file to the tablespace

alter tablespace users add datafile '/u01/oradata/orcl/users02.dbf' size 25m;

2. Resize the Datafile that is currently supporting the tablespace. Some versions of Oracle does not like this, and brings the instance down, so do not use this unless you are really sure.

alter database datafile '/u01/oradata/orcl/users01.dbf' resize 50M;

ow can I verify my Init.ora Parameters when the database is running

Answer: Connect to server manager and Use the command show parameter

to show all parameters that start with db, issue the following command

svrmgrl> show parameter db

How do I find my current Session Id

Answer: You can get it from the userenv('sessionid'). Here is how you can use it.

select osuser, username,sid,serial#
from v$session
where audsid = userenv('sessionid');

Can some one tell me how to change the db_name of the database without recreating the db

Answer: http://www.dbasupport.com/dbres/Technical_Docs/Database_Administration/General_Tasks/ ow can I find whether datafiles are in Auto Extend mode or not?

Answer: Query the DBA_DATA_FILES view and see the AUTOEXTENSIBLE column.

Example:
select FILE_NAME, AUTOEXTENSIBLE from dba_data_files;

If AUTOEXTENSIBLE is YES then your file is in auto extend mode. Then Find the corresponding MAXBYTES and INCREMENT_BY columns from the same view.
MAXBYTES is the maximum size that the file can grow and the INCREMENT_BY is the minimum size of the increments added to the file when it extends.

How can I drop a database?

Answer: To drop a database, remove its datafiles, redo log files, and all other associated files (control files, parameter files, archived log files).

To view the names of the database's datafiles and redo log files, query the data dictionary views V$DATAFILE and V$LOGFILE.

Why does a user get an error of not having quota after upgrading from Oracle7 to Oracle 8

Answer: IN ORACLE 7.3 ONCE USER IS GRANTED RESOURCE ROLE ,HE CAN ENJOY UNLIMITED SPACE ON ALL TABLESPACES. WHERE AS IN
8 ON WARDS U GOT TO SPECIALLY SAY
QUOTA UNLIMITED ON <>. How to maximize and efectively use of Audit Trail ? How to manage the growth of audit trail tables ?

Answer: These are extremely general questions and hard to answer.
Maximizing and effectively using Audit Trail - About the only general rules I know of are

1. Know why you're auditing. If you don't have a specific reason for auditing, don't do it! It slows down overall system performance.

2. Once you know why you're auditing, select auditing options that are as specific to your purpose as possible. For instance, if you're concerned about insertions into one table, DON'T audit on all insertion statements. Just audit insertion statements in the specific table. This minimizes the amount of audit record writing.

Controlling Audit Trail Size - The more critical this is to you, the more frequently you need to go in and clean up the audit trail. This involves either deleting records that are of no interest or moving the records to another table if you want to keep them. If you review all the records and determine that there is nothing of interest, truncate the table. If you find you are frequently truncating the table, you might want to reconsider whether or not you really need to be auditing.

Finally, bear in mind that Oracle auditing is event-based, not value-based. This may change your decision on whether or not to turn auditing on.

How do I find the name of the database I am connected to?

Answer: The Database name can be found through many views . The view everyone as access to is the GLOBAL_NAME.

SELECT GLOBAL_NAME FROM GLOBAL_NAME;

submitted by: faq Are ther any procedures instead of analyzing objects individually?

Answer: As of Oracle 8, it is possible to utilize a technique instead of analyzing schema objects individually. Analyzing a table, cluster, or index can affect current shared SQL statements, which are statements currently in the shared pool. Whenever an object is analyzed to update or delete statistics, all shared SQL statements that reference the analyzed object are flushed from memory so that the next execution of the statement can take advantage of the new statistics. The following procedures are now available following procedures:

DBMS_UTILITY.ANALYZE_SCHEMA() - This procedure takes two arguments - the name of a schema and an analysis method ('COMPUTE', 'ESTIMATE', or 'DELETE'). It gathers statistics on all of the objects in the schema.

DBMS_DDL. ANALYZE_OBJECTS() - This procedure takes four arguments - the type of an object ('CLUSTER', 'TABLE', or 'INDEX'), the schema of the object, the name of the object, and an analysis method ('COMPUTE', 'ESTIMATE', or 'DELETE'). It gathers statistics on the object.

Need to apply Patch Set 7.3.4.5 to production instance.
Can someone give me the proper steps to do it using oracle installer

Answer: Patch set application Notes is included in the patch set and please refer to them since they differ based on the OS and patchlevel you are upgrading from

submitted by: Nancy

How do I connect to a default user, irrespective of the loginname and pwd given at the logon screen?

Answer: In the File glogin.sql add the last line as connect username/password@databse.
The connection will be directed to the above mentioned databse everytime

Question: Oracle complained of a bad block in table. How can I retrieve the data other than the data that is in the bad block

Answer: First thing you need to do is find the bad block ids. To do this you can run dbverify. Lets assume our block id is 1234

Now find the segment name by running this query
select segment_name, segment_type, block_id, blocks
from dba_extents
where (1234 between block_id and (block_id + blocks - 1))
/

Once you have the bad segment name, its better to create a temporary table with good data if the segment is a table. If the segment is an index, drop the index and recreate it.

create table good_table
as
select * from bad_table
where rowid not in
( select /*+index(bad_table, any_index)*/ rowid
from bad_table
where substr( rowid, 1, 8 ) = 1234)

This post was written by: Franklin Manuel

Franklin Manuel is a professional blogger, web designer and front end web developer. Follow him on Twitter