JOKE if oracle backup plan is not properly tested. "We can back-up anything. It's the restore part that is giving us trouble". 1.Truncate also truncates the indexes. You don't have to do it as a separate operation. 2.In my view a Truncate is always faster than a Drop. Also, the original table structures of the table r preserved. 3.If any tablespace is left in backup mode, then, you would see --- ACTIVE --- under the column status. Select * from v$backup; 4.SYSDBA can do more than start/stop the database. It has a lot more functionality that sysoper (Operator mode) which is normally used for basic database functions such as start/stop. SYSOPER privilege allows operations such as: Instance startup, mount & database open ; Instance shutdown, dismount & database close ; Alter database BACKUP, ARCHIVE LOG, and RECOVER. This privilege allows the user to perform basic operational tasks without the ability to look at user data. SYSDBA privilege includes all SYSOPER privileges plus full system privileges (with the ADMIN option), plus 'CREATE DATABASE' etc.. This is effectively the same set of privileges available when previously connected INTERNAL. 5.1-) You should use roles for each type of user that you would have in the db, in that way every time you have to add a user you just assign that role. 5.2-) The tablespaces that you default the users, depends in the schema of your organization, you can have a development computer and assign the developers to it, and the production database in another computer and assign the users to it, as I said it depends of your schema. 5.3-) The users are the ones to insert, update and delete data so they must be assigned the final or production tablespace. 6. Firstly, you should consider creating seperate instances for developement and live data usage. It's bad practice to have any kind of uncontrolled access to live databases such as developers (or even DBAs), no matter how good they are - sooner or later it will cause problems. To define 'types' of privilege and access rights to different users, you can use ROLES. Basically you can assign privs such as SELECT to various table to a role name 'CASUAL' (for example) and so on. Your 'users' should not create or own any database object such as tables, therefore any default tablespace you assign them will not hold data. Generally I create a tablespace called 'USERS' and set this as defualt TS for all users. The application objects and data will be available to the users via privilages via the Roles and possibly via public synonyms. 7.Views that can be read in the nomount stage are: v$parameter, v$sga, v$option, v$process, v$session, v$version and v$instance. And tempfile is latest method of defining Oracle database temporary files for Temp tablespaces. 8.If the session is killed at server, the Rollback will happen and the Session will be terminated. 8.Chained Rows If Oracle 8i is ur current version, run the utlchain1.sql script available in ur ORACLE_HOME/rdbms/admin directory. This will create the chained_rows table for u. Now analyze the affected table using the command : analyze table list chained rows into chained_rows; Now when u select from the chained_rows table, u will get the rowid of all the rows that r chained in the table as head_rowid. The best way to deal with chained rows is to export the table's data, rebuild the table with a higher pctfree and import the data back. Otherwise, copy the chained rows into a temporary work table in the following way : create table as select * from where rowid in (select head_rowid from chained_rows where table_name = '' and owner_name = ''); Then delete the chained rows from the main table in the following way : delete from where rowid in (select head_rowid from chained_rows where table_name = '' and owner_name = ''); Next, re-insert the chained rows into the table : insert into table_name select * from ; Commit ur work. Remember to disable any foreign key constraints & delete cascade constraints during the deletion stage and re-enable them again after re-insertion of the rows. This should eliminate most of ur chained rows. If your chained rows are inside an Index-Organized table, be sure to read the Oracle manual on the "Analyze" command. There are two versions of the "chained-row" table, for index-organized tables you should create the "chained-row" table with universal rowids (head_rowid has datatype urowid). The "chained-row" table with universal rowids is the one named "...1.sql", i.e. has a 1 at the end of the file name. 9. Export with Query Clause: exp username/password@nts7 tables=(linemto,specs,specmast,password) file=c:\MTO.DMP QUERY='where proj_code=''CF-804''' 10. Pinning Objects in Shared pool The library cache forms part of the shared pool area. An important part of managing the library cache efficiently is to ensure that any frequently used packages are kept in the shared pool and not aged out. Oracle provides the dbms_shared_pool package to keep (or pin) packages, procedures, triggers and cursors. The object is never flushed out of memory until either an instance shutdown or it is explicitly unpinned. Objects that are accessed frequently are the best candidates for pinning. Pin packages as user SYS. It is recommended that the following packages are pinned in memory at instance startup. SYS.STANDARD SYS.DBMS_STANDARD SYS.DBMS_DESCRIBE SYS.DBMS_UTILITY SYS.DBMS_LOCK SYS.DBMS_PIPE SYS.DBMS_OUTPUT The syntax to pin and then unpin a package is EXECUTE DBMS_SHARED_POOL.KEEP ('sys.dbms_output'); EXECUTE DBMS_SHARED_POOL.UNKEEP ('sys.dbms_output'); 1) Note that when the shared pool is flushed (ALTER SYSTEM FLUSH SHARED_POOL) kept packages are NOT flushed out. 2) Note that pinning and keeping are technically not the same. In the library cache a pin is a lock held by a particular session on one or more heaps of an object. With regard to the shared pool, a pin is a bitmap in the header of a recreatable chunk indicating whether it is eligible to be aged out or flushed from the shared pool. Library cache object heaps that are marked for keeping may not be pinned in either sense. A script to identify suitable objects is :- Col owner format a10 Col name format a25 Set lines 200 Rem rem Look for high number of loads rem select owner,name,loads,executions,kept from v$db_object_cache where type like 'PACK%' and loads > 1 order by loads / 11.How to work on other schema's objects Oracle RDBMS 7.x - 9.x In certain situations, you may want to log in to another schema to operate on that user's objects like granting privileges, but you don't know the password for that schema and you can't wait until that user is available to assist you. Consider this situation. A typical database has the following users: SYSTEM SYS MATERIALS ACCOUNTS Assume you don't know the password for both MATERIALS and ACCOUNTS, but you want to grant privileges on MATERIALS's objects to the ACCOUNTS schema immediately. You can't achieve this objective by logging into SYS or SYSTEM schema. You should have to log in to that schema for granting privileges to others. That is, you should have to be the owner to grant privileges on the objects. At the same time, you should not disturb the existing password of the MATERIALS schema. You can perform both the objectives using the following steps: 11.1. Connect as SYSTEM or SYS schema and issue the following sql command: SQL> select 'alter user '||username||' identified by values '''||password||'''' from dba_users where username='MATERIALS'; This will return a statement like the one below alter user MATERIALS identified by values 'D0DCC7D6877E8507'; Preserve this statement, as it will help you reset the original (existing) password after finishing the work. 11.2. Set the new password for the MATERIALS schema by giving the following sql command: SQL> alter user materials identified by password; Here you can set any password temporarily to complete the objectives. 11.3. Log in to the MATERIALS schema, grant the privilege on certain objects to the ACCOUNTS schema and whatever jobs you want to complete in that schema. 11.4. Connect as the SYS or SYSTEM schema and execute the preserved statement. SQL> alter user MATERIALS identified by values 'D0DCC7D6877E8507'; This will reset the password to the previous one, and the user's work won't be affected. 12.Replication: Replication is not so hard to do. Just call for Oracle expert. I needed to do replication and got nowhere FAST. After An oracle expert came, we worked for about 6 hours and I got a script that does synchronic replication between 2 dB's. Basically you have to do the following: 12.1) GLOBAL NAMES = TRUE (meaning: dblink is the same as global database name, found in v$_database). 12.2) KISS: use one username for administrator, propagator and whatever users there is in the docs. 12.3) Create replication group. 12.4) Create dblink's from each DB to the other. 12.5) Use a script like the following to create the admin requests for all your tables: set linesize 200 set heading off set termout off set pagesize 0 set feedback off spool genrep.sql SELECT 'EXECUTE DBMS_REPCAT.CREATE_MASTER_REPOBJECT(oname => ''"'|| TABLE_NAME || '"'' , gname => ''"TEST_REP_HH"'', type => ''TABLE'', sname => ''"HH"'', copy_rows => TRUE, use_existing_object => TRUE);' FROM ALL_TABLES WHERE OWNER = 'HH'; select 'EXECUTE DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(oname => ''"' || TABLE_NAME || '"'' , type => ''TABLE'', sname => ''"HH"'', min_communication => TRUE); ' from all_tables WHERE OWNER = 'HH'; SPOOL OFF 12.6) You can do replication for existing tables. See parms in the scripts. 12.7) you can do sync or async replication. Important: I would advise is include it during your logical design phase. Once you have the schema built and ready to go, adding replication afterwards really limits you. You can do it no problem, you choices just become more limited. 13. LOBS,CLOBS,NCLOBS & BFILE LOBs are used to store values which are bigger than the 4,000 character limit of varchars and raws. I think that a good criterion would be how you would transfer the data using ftp; BLOB (Binary LOB) is for anything which reaches the other end corrupted when you have forgotten to say 'binary' - images, typically, which should not be stored as CLOBs, but also say Word documents. CLOBs are for anything you would transfer as 'ASCII'. NCLOBs are chiefly for text which uses multibyte characters - If you have to store text in Chinese or Tamil (I guess Malay would be OK in CLOBs), a NCLOB is probably what you need. Do not forget BFILEs, externally stored LOBs (in plain English, references to files). For many applications, they may be more appropriate than the full-blown, Oracle-stored LOB. 14. Uninstalling Oracle from WinNT Assuming you have no valid Oracle files on the system, to deinstall you will need to do the following 14.1. If any oracle services were created, make sure they are shut down 14.2. Using Oracle explorer of similar tool delete the entire directory pointed to by oracle_home 14.3. Using Oracle explorer or similar tool delete the entire directory c:\program files\oracle and subdirectories 14.4. Using regedit or regedt32 remove the entire key hkey_local_machine - software - Oracle 14.5. Remove all services that are associated with oracle (if any). This can be done using the resource kit utility delsrv or by using regedit or regedt32 to find and remove the services. If using regedit you will be going to the hkey_local_machine - system - currentcontrolset - services and remove the oracle services. There may be still be a few files floating around on the c: drive but this should be enough to let you reinstall Oracle8i. 15. SQLLOADER Problem Description ------------------- Using SQL*Loader to load data into a database table and rows fail to load with the message: Record X: Rejected - Error on table "XXXX"."XXXX", column "XXXXXX". Field in data file exceeds maximum length The data to be loaded will be longer than 255 characters and the database column is set to larger than 255 characters. Solution Description: In the control file specify the length of the column if the columns in the database are longer than 255 characters. eg LOAD DATA INFILE * INTO TABLE "TEST"."TEST1" APPEND FIELDS TERMINATED BY ',' ("COL1", "COL2" char(4000), "COL3") BEGINDATA 333, data data data longer than 255 characters data data etc,this will work Explanation: As a default, if the column length is not specified, it will default to a maximum of 255 characters. If the database column is longer than 255 characters and the data to be loaded exceeds 255 characters, then it exceeds the maximum length. Specifying the maximum length in the control file overrides the default setting. References: Oracle8 Server Utilities Additional Search Words: sqlloader loader 16. RAID RAID - (from http://www.uni-mainz.de/~neuffer/scsi/what_is_raid.html ) What does RAID stand for ? In 1987, Patterson, Gibson and Katz at the University of California Berkeley, published a paper entitled "A Case for Redundant Arrays of Inexpensive Disks (RAID)" . This paper described various types of disk arrays, referred to by the acronym RAID. The basic idea of RAID was to combine multiple small, inexpensive disk drives into an array of disk drives which yields performance exceeding that of a Single Large Expensive Drive (SLED). Additionally, this array of drives appears to the computer as a single logical storage unit or drive. The Mean Time Between Failure (MTBF) of the array will be equal to the MTBF of an individual drive, divided by the number of drives in the array. Because of this, the MTBF of an array of drives would be too low for many application requirements. However, disk arrays can be made fault-tolerant by redundantly storing information in various ways. Five types of array architectures, RAID-1 through RAID-5, were defined by the Berkeley paper, each providing disk fault-tolerance and each offering different trade-offs in features and performance. In addition to these five redundant array architectures, it has become popular to refer to a non-redundant array of disk drives as a RAID-0 array. WAFL (from the NetApp website) The WAFL (Write Anywhere File Layout) file system and the following features deliver enterprise-class availability: Consistency points. Always a consistent file-system image on disk, even after unplanned shutdowns. Virtually eliminates the need to run time-consuming file-system checks. Snapshot technology. Snapshots are near-instantaneous, transparent, read-only, online copies of the active file systems. Up to 31 Snapshots can be maintained for each data volume. Users can quickly recover deleted or modified files without administrative assistance or restore from tape backup. The Snapshot function requires minimal disk space and causes no disruption of service. Snapshots can be backed up to other media while users are modifying the active file system to minimize business disruption. SnapRestore software. Allows any system to revert back to a specified data volume Snapshot for instant file-system recovery. Terabytes can be recovered in minutes, rather than hours, without going to tape. The software also greatly facilitates scenario testing as well as providing disaster recovery and virus protection. Easy, cost-effective clustering. Safeguards against hardware failures by automatic filer takeover. Gives users continuous access to data. SnapMirror software. Provides remote mirroring at high speeds over a LAN or WAN. The asynchronous mirroring can be used for disaster recovery, replication, backup, or testing on a nonproduction system. 17. Pinning Objects into Shared POOL You don't pin tables; you pin functions, packages, and procedures. If you want a table to stay in the buffer cache for as long as possible, you use alter table tablename cache; CACHE | NOCACHE CACHE Clause: For data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables. 18. Snapshoot too old ora-01555 error With Oracle 9i & Auto Undo Management, Oracle allocates a default min_extents=2 for the undo segments. And there is nothing one can do to change it (other than continue using the rollback segment approach). The min_extents=20 guideline came about from Oracle's internal testing (a very controlled environment) to find ways to minimize ORA-1555 error. 19. Partitions Partitions are basically for the use of Oracle. It allows you to break apart a large table into several smaller sections to help in the management of the table data and to help Oracle find the data you requested. Under ideal conditions, Oracle will only select the smallest area to search for the data you have requested. If the table is 1 tablespace and large in size, it can take an enormous amount of resources to find the data you selected. If the table is partitioned correctly, Oracle will only search the partition/s that it needs to find your data. The application code does not need to be changed. The application will request data from a table and Oracle will determine which partition to use. 20. Checking Your Current Release Number (Connect system/manager) SELECT * FROM product_component_version; PRODUCT VERSION STATUS --------------------------- --------------------------- --------------------- CORE 8.1.5.0.0 Production NLSRTL 3.4.0.0.0 Production Oracle8i Enterprise Edition 8.1.5.0.0 Production PL/SQL 8.1.5.0.0 Production TNS for 32-bit Windows: 8.1.5.0.0 Production 5 rows selected. or SQL> SELECT * FROM V$VERSION ; BANNER ---------------------------------------------------------------- Oracle8i Release 8.1.6.0.0 - Production PL/SQL Release 8.1.6.0.0 - Production CORE 8.1.6.0.0 Production TNS for 32-bit Windows: Version 8.1.6.0.0 - Production NLSRTL Version 3.4.1.0.0 - Production 21. Dropping a datafile (Consequences) > I have an "offline dropped" datafile at my database. The file was > physically > deleted at the OS level after "offline drop". > Before dropping the file , all the objects that had extents allocated in > that file (from dba_extents) were dropped. > Now , V$datafile shows this file as of size=0 and status = 'RECOVER' which > is fine. > BUT , file$ show this file as of size 8GB, which is the file size > as it used > to be before being dropped. > Moreover , fet$ shows 8GB as free extents residing at this file . > And , after all , tables get created in that file , i.e. dba_extents show > extents with FILE_ID of that file ! > When a select statement tries to access a table with extents residing in > that file , i get an error message , off course. > > So , the question is , how do i make the file's extents disappear > from fet$ > ? Answer: Once a datafile exists, it is a permanent artifact of the database. It cannot be deleted. Well, yes, it can be deleted, but this does not remove the references to it in various parts of the rdbms. As you have experienced here, upon deletion of the datafile, with all the precautions you took to ensure data integrity, the instance continues to function nominally for a time. At some point, the rdbms will attempt access into the deleted file. This is because, as I said, not all references can be removed. FET$ simply cannot be made to de-reference a datafile that has existed. I don't know the exact reasons or details. I only know that this is so, because we had the same thing happen on one of our instances recently. When a datafile is placed "OFFLINE DROP" in the rdbms, two things are done. The "OFFLINE" prevents the rdbms from writing to, or reading from the specified datafile. This is intended as means of maintaining data integrity during maintenance -- not as a step in removing a datafile. The "DROP" drops the datafile from the archive process list. Nothing more. So "OFFLINE DROP" tells the rdbms to _temporarily_ stop using the datafile (until it is again "ONLINE") and stop archiving changes to that datafile. The only way to "delete" a datafile is through tablespace reorganization. Two ways you can do this are (a) the traditional export/import method, or (2), if you have enough space on your system, tablespace migration. To start with, however, you need to restore the deleted datafile and do recovery on it, and, probably, on the tablespace, too. You can then "OFFLINE" the datafile to prevent any data from being written to it. After restoring and recovering the 8GB datafile, you will want to perform a full, cold backup . . . just in case. Then, examine your system and determine if you have enough storage available to migrate all data in the target tablespace to another tablespace which you will create for this purpose. If you do, then create a new tablespace sized as needed/appropriate, issue the appropriate commands to copy all the data, drop and recreate constraints, indices, procedures, etc., then drop the old tablespace including contents. Unless you absolutely must have the tablespace named the same as the original ts which you just dropped (or the datafiles must be named as in the original ts), you have just completed your tablespace migration. If, on the other hand, the ts name and/or datafile names have to be the same as the original tablespace, you can now recreate that tablespace, sized as needed/appropriate, and repeat the migration process into it and drop the now unnecessary tablespace. If, as seems more likely the case, you don't have the space to create a new tablespace, you will need to do a full export, drop the tablespace, recreate it sized as needed/appropriate, and do a full import. 22. High CPU Usage by Oracle This query will show current sessions CPU usage. It may indicate a particular session is hogging the CPU. select sess.username, stat.sid, name.name name, sum(stat.value)/100 valuesum_seconds from v$sesstat stat, v$statname name, v$session sess where stat.sid = sess.sid and stat.statistic# = name.statistic# and name.name like '%CPU%' group by sess.username, stat.sid, name.name; You should also use 'top' or some similar tool to identify the process. At times a session may have a 'runaway' process that is hogging the CPU. If a single dedicated server process is using a very high percentage of the CPU, it's would probably be a good idea to kill it. Be sure to identify the user or batch program first to make sure it can be killed. The output of 'ps -fuoracle' will show a dedicated session as similar to this: oracleVDRPROD (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ))) The following query will match an process to an Oracle session: select s.username, s.sid, s.serial#, p.pid ppid, s.status, s.osuser, substr(s.program,1,20) client_program, s.process client_process, substr(p.program,1,20) server_program, p.spid server_pid from v$session s, v$process p where s.username is not null -- use outer join to show sniped sessions in -- v$session that don't have an OS process and p.addr(+) = s.paddr -- uncomment to see only your own session --and userenv('SESSIONID') = s.audsid order by username, sid / 23. Renaming or Moving a datafile 1. Renaming or moving a datafile is a two step proposition. Whether or not you do it with the database running depends on the archive log mode of the database. If at all possible the OS portion should be done with the database shutdown. If your in noarchive log mode then you have no choice. If your in archive log mode then you can take the tablespace and datafile offline, move/rename the file, alter database rename file to , recover the tablespace, and bring it back online. If at all possible, take a cold backup BEFORE and AFTER. In all cases use the 'alter database backup controlfile to trace' command, just in case you end up rebuilding your control file(s). 2. Alter tablespace {name} offline; ! host copy filename to newlocation; Alter database rename file 'oldname' to 'newname'; alter tablespace {name} online; * backup your database! * if all goes well, remove the old file. You obviously need to be able to take the tablespace offline to do this, so no queries or transactions may be running while you do this. You also need to perform a backup (at least alter database backup controlfile to trace). 24. Last day of the month LAST_DAY(sysdate) returns the last day of the current month. LAST_DAY(ADD_MONTHS(sysdate,-1))+1 will result in the first day of the current month. There is probably a better way to do this but here is what I use. report_from_date = (SELECT to_char(to_date('01','DD'),'YYYYMMDD') from dual), report_through_date = (select to_char(last_day(sysdate),'YYYYMMDD') from dual), First day calculations; select last_day(add_months(sysdate,-1))+1 form dual; for current month 1st day. select last_day(add_months(sysdate,-1)) from dual ; for end of previous month. 25. Connecting in SQL plus without logging sqlplus /nolog connect internal shutdown abort exit Key is to start sqlplu s and tell it not to log in. Since internal goes away in 9, not sure what the workaround would be there. 26. Ora-01555 Error (Metalink Note) Delayed block cleanout on old committed updates. An update operation completes and commits; the updated blocks are not touched again until a long-running query begins. Delayed Block Cleanout (DBC) has never been done on the blocks. This can result in a scenario which happens only under specific circumstances in VLDB, causing ORA-01555 errors when NO updates or inserts are being committed on the same blocks a query is retrieving. All of the following must be true for an ORA-01555 to occur in this case: (i) An update completes and commits and the blocks are not touched again until... (ii) A long query begins against the previously updated blocks. (iii) During the query, a considerable amount of DML takes place, though not on the previously updated blocks which the query is currently fetching. (iv) Under condition (iii) there is so much DML relative to available rollback space that the rollback segment used in the first update wraps around, probably several times. (v) Under condition (iv), the commit SCN of the first update is cycled out of the rollback segment. (vi) Under condition (iv) the lowest SCN in the rollback segment is pushed higher than the read consistent SCN in the query. (Note: The read consistent SCN is what the query uses to construct a read consistent view. Any block which has an SCN higher than this was obviously updated after the query started and requires rollback). The above conditions imply that when a query reaches a block that has been updated but not cleaned out, the query quickly learns that the update committed, and accordingly cleans out the block. But because the update SCN is no longer in the rollback segment (condition (v)), the query doesn't know WHEN the update committed. This is important because if the commit happened before the query began, the current value in the block can be used by the query; but if the commit happened after, the old value must be fetched from the rollback segment. Now, because the rollback segment wrapped in (iv), we know that the update SCN can't be higher than the lowest SCN in the rollback segment, which gives us a nice upper bound. If we only knew that the read consistent SCN was higher than this upper bound, we would know that the update committed before the query started. But we don't know this because of condition (vi), so we can't even accurately "estimate" the update SCN. Hence, we get an ORA-01555. 27. The Differences between Windows NT COPY and Oracle OCOPY When Doing Backups: ============================================================================ When doing an online backup, should you use the Windows NT COPY command, or the Oracle OCOPY command? While doing online backups you should use OCOPY, or Oracle7 EBU, or Oracle8 (and later) RMAN. With the OCOPY command you could copy to a backup directory on the hard drive but cannot use OCOPY to copy a file to tape. The other option if you do not want to use ocopy to perform your backup as this does require a lot of disk space is EBU/RMAN that comes with Oracle. Depending on your Oracle version, the distribution includes a utility called EBU (Oracle7) or RMAN (Oracle8 and later) that can be used for online recovery as well. You will need to use a media management product to move the data from RMAN to tape. Legato Storage Manager is provided however there are other products that are supported to be used with this tool. To backup you will need to use the utility delivered by Oracle, the ocopy command. Utilities like the NT commands copy, xcopy CANNOT be used to back up. The Windows NT feature to be aware of is that NT Backup does not allow files in use to be copied, so you must use the OCOPY utility that Oracle provides to copy the open database files to another disk location. Since OCOPY cannot copy files directly to tape, you will then need to use NT Backup or copy or a similar utility to copy the files to tape, as required. OCOPY allows writing to continue while the backup is running. The NT COPY is a closed copy and the files may be marked either as "fuzzy" or "corrupt." Ocopy opens the file using CreateFile() with the FILE_SHARE_READ and FILE_SHARE_WRITE flags. This allows writing to continue while we take the backup. Inconsistencies in the backup are repaired by applying archived redo during recovery. The 'copy' command from NT doesn't use these flags since it wants to prevent writes to the file while the copy is taking place. 28. NULLS and EMPTY strings When you insert it in the table you can insert it like '' (two quotes with nothing in between) This constitutes an empty string and is not interpreted as NULL. 29. RENAME A DATAFILE PURPOSE In many situations, a datafile or logfile must be renamed inside Oracle. Whereas the contents of the file remain valid, you need to define a new physical name or location for it. For example: - You want to move a database file to a different disk for performance or maintenance reasons. - You have restored a datafile from backup, but the disk where it should normally be placed has crashed and you need to relocate it to a different disk. - You have moved or renamed a datafile at operating system level but forgot to rename it within Oracle. At startup, you get ORA-01157 and ORA-01110. If the database is up and you try to shut it down normal or immediate, you get ORA-01116 ad ORA-01110. - You have multiple databases on the same machine and you need to rename certain database files to prevent collision and confusion. SCOPE & APPLICATION This bulletin gives instructions to: I. RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN II. RENAME AND OR MOVE DATAFILE(S) WITH THE DATABASE SHUT DOWN III. RENAME AND OR MOVE A LOGFILE How to Rename or Move Datafiles and Logfiles: ============================================= NOTE: To rename or relocate datafiles in the SYSTEM tablespace you must use option II, 'Renaming or Moving a Datafile with the Database Shut Down', because you cannot take the SYSTEM tablespace offline. I. RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN ---------------------------------------------------- Datafiles can be renamed or moved while the database is open. However, the tablespace must be made READ-ONLY. This will allow users to select from the tablespace, but prevents them from doing inserts, updates, and deletes. The amount of time the tablespace is required to be read only will depend on how large the datafile(s) are and how long it takes to copy the datafile(s) to the new location. Making the tablespace read only freezes the file header, preventing updates from being made to the file header. Since this datafile is then at a read only state, it is possible to copy the file while the database is open. To do this you must follow these steps: 1. Determine how many datafiles are associated with the tablespace. SVRMGR> SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = ''; 2. Make sure that all datafiles returned have the status AVAILABLE. 3. Make the tablespace is read only. SVRMGR> ALTER TABLESPACE READ ONLY; 4. Make sure that the tablespace is defined as read only in the data dictionary. SVRMGR> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = ''; TABLESPACE_NAME STATUS ------------------------------ --------- READ ONLY 5. Copy the datafile(s) to the new location using the operating system copy command. Once the datafile(s) have been copied to the new location compare the sizes of the datafiles. Make sure that the sizes match. 6. Once the datafiles have been copied to the new location alter the tablespace offline. SVRMGR> ALTER TABLESPACE OFFLINE; * At this point the tablespace is not accessible to users. 7. Once the tablespace is offline you will need to rename the datafile(s) to the new location. This updates the entry for the datafile(s) in the controlfile. SVRMGR> ALTER DATABASE RENAME FILE '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF' TO '/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF'; * You will need to do this for all datafiles associated with this tablespace. 8. Once the alter database statement has been processed for the datafile(s) you can bring the tablespace online. SVRMGR> ALTER TABLESPACE YOUR_TABLESPACE_NAME ONLINE; 9. After you bring the tablespace back online you can make the tablespace read/write again. SVRMGR> ALTER TABLESPACE YOUR_TABLESPACE_NAME READ WRITE; 10. You can check the status of the tablespace to make sure it is read/write. You can also verify that the controlfile has been updated by doing the following: SVRMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE; This will produce a readable copy of the contents of your controlfile which will be placed in your user_dump_dest directory. 11. Remove the datafile(s) from the old location at the O/S level. II. RENAME AND OR MOVE DATAFILE(S) WITH THE DATABASE SHUT DOWN -------------------------------------------------------------- 1. If the database is up, shut it down. 2. Copy the datafile to the new name/location at operating system level. 3. Mount the database. SVRMGR> STARTUP MOUNT This command will read the control file but will not mount the datafiles. 4. Rename the file inside Oracle. SVRMGR> ALTER DATABASE RENAME FILE '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF' TO '/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF'; Do this for all the datafiles that were renamed or moved at the operating system level. 5. Open the database. SVRMGR> ALTER DATABASE OPEN; 6. Query v$dbfile to confirm that the changes made were correct. SVRMGR> SELECT * FROM V$DBFILE; 7. Remove the datafile(s) from the old location at the operating system level. III. RENAME AND OR MOVE A LOGFILE ---------------------------------- 1. Shutdown the database. 2. Copy the logfile to the new nameA/location at operating system level. 3. Mount the database. SVRMGR> STARTUP MOUNT 4. Rename the file. SVRMGR> ALTER DATABASE RENAME FILE '/FULL_PATH_OF_OLD_LOCATION/AND_REDO_LOG_NAME.LOG' TO '/FULL_PATH_OF_NEW_LOCATION/AND_REDO_LOG_NAME.LOG'; 5. Open the database. SVRMGR> ALTER DATABASE OPEN; 6. Remove the logfile(s) from the old location at the operating system level. Search Words: ============= ORA-1157 ORA-1116 ORA-1110 30. RECOVER UNUSED SPACE USED BY TABLE To recover the unused space you need to "alter table deallocate unused" 31. user OUTLN Username: OUTLN Password: outln Created: when installing Oracle8i Description: (From Oracle8i Migration Release 3 (8.1.7)) "Oracle8i adds the OUTLN user schema to support Plan Stability. The OUTLN user acts as a place to centrally manage metadata associated with stored outlines." This user has DBA role. It is used for plan stability ie. to keep the same execution plans for the same queries even if your system configuration or statistics changes. Execution plans will be the same in different Oracle releases with different optimizers. Read more in Oracle8i Designing and Tuning for Performance, Chapter 10, Using Plan Stability. 32. DBWR & Freelists One thing to consider when putting together your testing scenario is that writes to the database block by a transaction are done in memory, that is they are made to the cached database blocks. An internal locking mechanism ( a latch ) is used to control access to the cache, and it is normally held for only a short time. The data may be written to the disk before a commit, and it may not be written to disk until after a commit. ( referring to datafiles only here, not the redo log ). The 60 second sleep in your script is only simulating user think time, it's not actually blocking anything. You will need to similate several session simultaneously inserting, and you will need to know if the contention is in the table or in an index: I'm guessing it's an index or indexes. Some time spent with the Concepts manual would help you out here. Here's an excerpt from the section on database writer: Database Writer (DBWn) The database writer writes modified blocks from the database buffer cache to the datafiles. Although one database writer process (DBW0) is sufficient for most systems, you can configure additional processes (DBW1 through DBW9) to improve write performance for a system that modifies data heavily. The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes. Since Oracle uses write-ahead logging, DBWn does not need to write blocks when a transaction commits. Instead, DBWn is designed to perform batched writes with high efficiency. In the most common case, DBWn writes only when more data needs to be read into the system global area and too few database buffers are free. The least recently used data is written to the datafiles first. DBWn also performs writes for other functions such as checkpointing. Take a look at Chapter 15 on Transactions as well. Also check out Steve Adams' web site, www.ixora.com.au. Lots of interesting stuff there if you want to learn about the internals. Here's an excerpt from the section on Free lists: As mentioned previously, free list contention occurs when multiple processes using the same free list attempt to modify the data block on the head of the free list concurrently. It is shown in V$WAITSTAT against the data block class. V$WAITSTAT can also show contention for the segment header and free list blocks. This occurs where multiple transaction in the same free list group need to update their free list header records simultaneously. There are various ways of addressing these problems such as rebuilding the table with more free list groups, or increasing _bump_highwater_mark_count, or the novel idea of fixing the application. To drill down on which segments are causing data block contention, I suggested using event 10046, level 8. This creates a trace file much like to one produced by the sql_trace facility, except that for each event wait a line is printed to the trace file. In particular, each buffer busy wait is recorded together with the P1 and P2 values which are the data file and block number of the wait. So to find which blocks a process has been waiting on, you just grep the trace file for buffer busy waits lines and produce a histogram of the file and block numbers most commonly waited for. Once you have suspect file and block numbers, you can relate them to a segment by querying DBA_EXTENTS. In the case of free list contention on a table it is common to have several hot blocks just below the high water mark for the segment. If you really want to learn the internals, his book is excellent for that. It's not normally necessary IMO to delve that deep into the internals to deal with tuning problems, at least in my experience. It will certainly help you develop insight and intuition as to what is going on with your database though. 33. HOW TO MAKE EXPORT FASTER 1. Drop indexes, disable constraints, disable triggers. 2. Do not import indexes. 3. Turn off analyizing of objects. 4. Increase buffer size to about 5-10MB. More than that may not help. 5. Keep users out. 6. Turn off Archive log mode. 7. Do not use that feedback thingy. 8. Increase redo log sizes. Also check log_buffer size and increase it if is too small. 9. After import, build indexes with nologging turned on, w/ increased sort area sizes and proper TEMPORARY tablespace. 10. If still not fast enough, buy a bigger server and faster disks :) Since you stated reorganization of the database, consider using LMTs, proper storage parameters (PCTFREE, PCTUSED) for tables etc. It will minimize the need for database reorganzations. HTH.. - Kirti Despande 34. Row Wait (LOCK) Most of the time you can find out which row the user is waiting on the row_Wait_row# will tell you what row it is waiting on and row_wait_block# will tell you what block it is waiting and row_wait_file# will tell you which file it is waiting for all the above information u can get from v$session, The user who is waiting for a lock_wait in the v$session will be having this information. use the dbms_rowid package which will tell you which table and which rowid this is waiting on get the rowid and you will find out which row it is waiting on the following query will tell u which row a user is waiting on you will have to login as sys to run this query. you will get the table name along with the select statement and the table name this query can be run when the there is a lock issue SELECT owner || '.' || object_name || ' [' || object_type || ']' obj_info, dbms_rowid.rowid_create(1, row_wait_obj#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) "Rowid", a.username, a.sid, a.row_wait_file#, a.row_wait_block#, a.row_wait_row# FROM DBA_OBJECTS, (SELECT a.username, a.sid, a.row_wait_obj#, a.ROW_WAIT_FILE#, a.ROW_WAIT_BLOCK#, a.ROW_WAIT_ROW# FROM v$session a, v$lock b WHERE a.username IS NOT NULL AND a.row_wait_obj# > 0 AND a.sid = b.sid AND b.TYPE = 'TX') a WHERE object_id = a.row_wait_obj# ORDER BY 1, 2 35.