Question:

I would like to increase the size of the archive logs which should hopefully decrease the number of logs switched per minute

Answer :

The best way is to create new logs with the larger size and then drop the old ones after you switch from the old ones to the new logs

``create logs 5-8

``switch to log 5

``drop logs 1-4

````That way you do not have to have any downtime etc.

Question:

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.

Question:

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;

Question:

How 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

Question:

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');

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)

Question:

How 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.

Question:

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.

Question:

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.

Question:

We have de-installed the oracle8-NT4. When we are trying to install the Oracle8 by Custom Installation, we are unable to create our own SID name, which was the previous SID name?

We removed the oracle registry thru regedit.exe also. If i go thru the control panel-Services, still i can see all oracle services in disabled status. which i want to remove it permanently. Then reinstall the oracle8-custom installation.

Answer :

You can use the oradim utility to do this.

oradimxx -delete -sid sidname -srvc servicename.

I would also recommend rebooting the server to be sure that all the references to the sid are cleaned up

Exit to : Oracle Hints and Tips