Oracle Common Tips and scripts

oracle| Skills | script
2005-01-05 16:42 Author: source: CSDN

"Introduction" Oracle's explicit parameters, except as defined in the Init.ora file, can be displayed in SVRMGRL with "show parameter *". But Oracle also has some parameters that start with "_". As we are very familiar with the "_offline_rollback_segments" and so on.

1. How do I view Oracle's implied parameters? Explicit parameters for Oracle, except as defined in the Init.ora file, can be displayed in SVRMGRL with "show parameter *". But Oracle also has some parameters that start with "_". 　　As we are very familiar with the "_offline_rollback_segments" and so on. 　　 　　These parameters can be found in the Sys.x$ksppi table. Statement: "Select KSPPINM from X$ksppi where substr (ksppinm,1,1) = ' _ '; 　　 　　"2. How do I see which Oracle components are installed?" 　　Enter ${oracle_home}/orainst/, run./inspdver, display the installation components and version number. 　　3. How do I see the size of shared memory occupied by Oracle? 　　 　　Use the Unix command "IPCS" to view the starting address, semaphore, and message queue for shared memory. 　　Under SVRMGRL, using "Oradebug IPC", you can see that Oracle occupies shared memory in segments and sizes. Example:svrmgr> oradebug IPC--------------Shared Memory--------------Seg Id address Size 1153 7fe000 784 1154 800000 419430400 1155 19800000 67108864 4. 　　 　　How do I view the SIDs and serial# for the current Sql*plus user? 　　 　　Under Sql*plus, run: "Select Sid, Serial#, status from V$session where Audsid=userenv (' SessionID ');" 　　 　　5. How do I view the character set of the current database? 　　 　　Under Sql*plus, run: "Select Userenv (' language ') from dual;" 　　 　　Or: "Select Userenv (' Lang ') from dual;" 　　 　　6. How can I view a user in a database and what SQL statement is running? Connect tables V$session and V$sqltext according to machine, username, or SID, serial#, can be identified. Sql*plus statement: "Select Sql_text from V$sql_text T, v$session S WHERE t.address=s.sql_address and T.hash_value=s.sql_ha Sh_value and s.machine= ' xxxxx ' or username= ' xxxxx '--View a host name, or user name/"7." 　　 　　How do I delete duplicate records in a table? Example: DELETE from table_name a where rowID > (SELECT min (ROWID) from table_name b where b.pk_column_1 = A.P 　　 　　K_column_1 and b.pk_column_2 = a.pk_column_2); 　　8. Temporarily forced to change the server character set to SYS or system login systems, Sql*plus run: "CREATE database character set Us7ascii;". The following error prompts: * Create database Character set Us7ascii error at line 1:ora-01031:insufficient privileges actually, see v$nls_p Arameters, the character set has been changed successfully. 　　But after restarting the database, the database character set changes back to the original. 　　This command can be used for data switching between temporary and different character set servers. 9. How to query the number of PCM locks allocated by each instance to order: SELECT COUNT (*) "Number of hashed PCM locks" from V$lock_element where Bitand 　　 　　(flags,4) <>0/select count (*) "Number of fine grain PCM locks" from V$lock_element where Bitand (flags,4) =0 /10. 　　 　　How do you determine what SQL optimization is currently being used? With explainPlan produces explain plan to check the value of position column id=0 in plan_table. 　　e.g. Select Decode (NVL (position,-1), -1, ' RBO ', 1, ' CBO ') from plan_table where ID=0/11. 　　 　　When doing export, can you divide the dump file into multiple? Oracle8i exp Adds a parameter FILESIZE that splits one file into multiple: Exp scott/tiger file= (order_1.dmp,order_2.dmp,order_3.dmp) filesize=1g Tables=order Other versions of Oracle can use pipelines and split splits on UNIX: Mknod pipe P split-b 2048m Pipe order & #将文件分割成, per 2GB size, to Orde R-Prefix file: #orderaa, Orderab,orderac,... And put the process in the background.