1. How to view the implicit parameters of ORACLE? Explicit parameters of ORACLE can be displayed by using showparameter * In svrmgrl in addition to those defined in the INIT. ORA file. 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.
1. How to view the implicit parameters of ORACLE? Explicit parameters of ORACLE can be displayed by using "showparameter *" in svrmgrl in addition to those defined in the INIT. ORA file. 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.
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 current character set?
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 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.
Exp scott/tiger file = pipe tables = order
