How to view the implicit parameters of Oracle?
Oracle's explicit parameters, in addition to defined in the init.ora file, you can display "Show Parameter *" in SVRMGRL. But Oracle has some parameters to start with "_". As we are very familiar "_offline_rollback_segments", etc..
These parameters can be isolated in the Sys.x $ KSPPI table.
Statement: "SELECT KSPPINM from x $ ksppi where substr (ksppinm, 1, 1) = '_';"
2. How to view which Oracle components installed?
Enter $ {Oracle_Home} / ORAINST /, run ./inspdver, display the installation component and version number.
3. How do I view the size of the shared memory occupied by ORACLE?
You can use the UNIX command "IPCS" to view the starting address, semaphore, message queue of shared memory.
Under SVRMGRL, use ORADEBUG IPC, you can see the segments and sizes of Oracle occupy the shared memory.
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?
Under SQL * Plus, run:
"SELECT SID, Serial #, Status from V $ session
WHERE AUDSID = Userenv ('sessionID'); "
5. How do I check the character set of the current database?
Under SQL * Plus, run:
"SELECT Userenv ('Language') from Dual;"
or:
"SELECT Userenv ('lang') from dual;"
6. How do I view a user in the database, what SQL statement is running?
According to Machine, Username or SID, Serial #, connect table V $ session and V $ sqltext can be found.
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 usrname = 'xxxxx' - View a host name, or username
/ "
7. How to delete a repetitive record in the 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. Manual temporary enforcement server character set
Sign in system, SQL * Plus runs: "Create Database Character Set US7ASCII;".
Have the following error tips:
* Create Database Character Set US7ASCII
Error At Line 1:
ORA-01031: Insufficient Privileges
In fact, look at V $ NLS_Parameters, the character set has changed success. But after restarting the database, the database character set has changed back to the original.
This command can be used to temporarily use data switching between different character set servers.
9. How to query the number of PCM locks allocated by each 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 to determine what SQL optimization is currently under use?
Use the Explain Plan to generate an Explain Plan, check the value of the POSITION column of id = 0 in Plan_Table.
E.G.
Select decode (nVL (position, -1), - 1, 'RBO', 1, 'CBO') from plan_table where id = 0
/
11. Can you divide the DUMP file into multiple when doing Export?
Oracle8i EXP adds a parameter FileSize to divide 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 be split by pipelines and split in UNIX:
MKNOD PIPE P
Split -b 2048m Pipe Order & # divides files into, each 2GB size, files prefixed as orde:
# ORDERAA, ORDERAB, ORDERAC, ... and put this process in the background.
Exp Scott / Tiger File = Pipe Tables = ORDER
