Common Oracle skills and scripts

Source: CSDN

1. How to view the implicit parameters of ORACLE?

In addition to the explicit parameters defined in the INIT. ORA file, you can use "show parameter *" in svrmgrl to display ORACLE explicit parameters. However, some ORACLE parameters start. For example, we are very familiar with "_ offline_rollback_segments.

These parameters can be found in the sys. x $ ksppi table.

Statement: "select ksppinm from x $ ksppi where substr (ksppinm, 1, 1) = _;"

2. How to check which ORACLE components are installed?

Enter $ {ORACLE_HOME}/orainst/and run./inspdver. The installation component and version number are displayed.

3. How can I view the size of the shared memory occupied by ORACLE?

You can use the UNIX Command "ipcs" to view the starting address, semaphore, and message queue of the shared memory.

Using "oradebug ipc" in svrmgrl, we can see the segments and sizes of the shared memory occupied by ORACLE.

Example:

SVRMGR> oradebug ipc

-------------- Shared memory --------------

Seg Id Address Size

1153 7fe000 784

1154 800000 419430400

1155 19800000 67108864

4. How to view the sid and serial # Of the current SQL * PLUS user #?

Run the following command under SQL * PLUS:

"Select sid, serial #, status from v $ session

Where audsid = userenv (sessionid );"

5. How to view the character set of the current database?

Run the following command under SQL * PLUS:

"Select userenv (language) from dual ;"

Or:

"Select userenv (lang) from dual ;"

6. How can I view the SQL statements that a user in the database is running?

Connect the table V $ SESSION and V $ SQLTEXT Based on MACHINE, USERNAME, SID, and SERIAL.

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 to delete duplicate records in a table?

Example:

DELETE

FROM table_name

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. Manually temporarily force changing the server Character Set

Log on to the system using sys or system, and run SQL * plus: "create database character set us7ascii ;".

The following error message is displayed:

* Create database character set US7ASCII

ERROR at line 1:

ORA-01031: insufficient privileges

In fact, check v $ nls_parameters. the character set has been changed successfully. However, after the database is restarted, the character set of the database changes back to the original one.

This command can be used for temporary data switching between servers of different character sets.

9. How to query the number of PCM locks allocated to each instance

Run 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 can I determine which SQL optimization method is being used?

Use explain plan to generate an explain plan and 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. Can I split the DUMP file into multiple EXPORT files?

In ORACLE8I, EXP adds a parameter FILESIZE, which can be divided into multiple files:

Exp scott/tiger file = (ORDER_1.DMP, ORDER_2.DMP, ORDER_3.DMP) FILESIZE = 1g tables = ORDER;

In other versions of ORACLE, pipelines and split can be used for separation in UNIX:

Mknod pipe p

Split-B 2048 m pipe order & # split the file into 2 GB files prefixed with order:

# Orderaa, orderab, orderac,... and put the process in the background.