Oracle Common tips and scripts

oracle| Tips | Scripting Author: Anonymous Source: InterNet Add Time: 2004-11-10 1. How do I view implicit parameters for Oracle?


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 SID and serial# of 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?

According to machine, username or SID, serial#, Connection table v$session and V$sqltext, 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_hash_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.pk_column_1
and b.pk_column_2 = a.pk_column_2);


8. Temporary forced change of server character set by hand

Log in to SYS or system, Sql*plus run: "CREATE database character set Us7ascii;".
There are the following error prompts:
* CREATE DATABASE Character Set Us7ascii
ERROR at line 1:
Ora-01031:insufficient Privileges
In fact, looking at V$nls_parameters, 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 per instance

Use the following command:

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?

Generate explain plan with explain plan to check the id=0 position column values in the 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?

In Oracle8i, exp adds a parameter filesize that divides a 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 under Unix:

Mknod Pipe P

Split-b 2048m Pipe Order & #将文件分割成, for each 2GB size, the file is prefixed by the:

#orderaa, Orderab,orderac,... And put the process in the background.

EXP Scott/tiger file=pipe Tables=order