How to read OracleErrorstackOutput
How to read Oracle Errorstack Output the Wirte By tanelpoder, translation by me. When a critical error occurs in oracle such as: ora-600, the Errorstack dump is automatically dumped by oracle. When you see an error in the alert.log, a trace file will be generated, and the trace will start with “ksedmp: internal or fatal error”, errors such as this (ORA-7445 or ORA-600 with some parameters). “kesdmp” means Kernel Service Error Dump, so the following line is the errorstack dump. the Errorstack dump can also be invoked manually using Oradebug errorstack 3 (after setting the target process with Oradebug setospid). Oradebug Errorstack for when a session seems Hang( But there is no reasonable wait event in v$session_wait) or it consumes more resources than normal and you want to know the specific variable value of the current sql. the The Errorstack trace file has a lot of information, which is more or less useful for our troubleshooting. But the large amount of data and binary dumps may confuse you, here I will show you some structures that you can understand. In this article we will explore the following topics: 1. From the errorstack trace file, it is found that the SQL…
Oracle statistics statement by day
Oracle statistics statement by day the –Create table Test CTEATE TABLE TEST( ID NUMBER NOT NULL, MODIFIEDTIME DATE NOT NULL ) –Statistics by day SELECT TO_CHAR(T.MODIFIEDTIME,’YYYY-MM-DD’) TIME,COUNT(*) COUNT FROM TEST T www.2cto.com –You can add query conditions here WHERE TO_CHAR(T.MODIFIEDTIME,'YYYY') = TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(T.MODIFIEDTIME,'YYYY-MM-DD') –Group by date ORDER BY TO_CHAR(T.MODIFIEDTIME, & # 39; YYYY-MM-DD & # 39;) ASC NULLS LAST — Sort by date the — Note: MODIFIEDTIME is the time field in the table TEST, the time type –The above code can be run directly in the database –If there is a quantity field in the table, if you want to count the quantity by day, you can change COUNT(*) to SUM(1) function the Author guoxuepeng123
The solution to the garbled Chinese characters after Oracle executes the SQL script under the Linux system
The solution to the garbled Chinese characters after Oracle executes the SQL script under the Linux system the First confirm the character set of Oracle, and execute the statement after logging in to Oracle with sqlplus: the [sql] select userenv('language') from dual; Return value example: AMERICAN_AMERICA.ZHS16GBK Modify the NLS_LANG environment variable of Linux, and modify the Linux user specified by Oracle the “.base_profile” file, add as follows: www.2cto.com [sql] export NLS_LANG=”AMERICAN_AMERICA.ZHS16GBK” Use the Linux command “source ~/.base_profile” or restart to make the environment variable settings take effect; The Linux command to view environment variables is “echo $NLS_LANG”. the the Author zzs0829
A Brief Analysis of Database Cursors (Oracle)
A Brief Analysis of Database Cursors (Oracle) the Cursor concept The cursor is a memory work area of SQL, which is defined by the system or the user in the form of variables. The function of the cursor is to temporarily store the data blocks extracted from the database. Why use a cursor? The data of the database is stored in the disk, and the cursor transfers the data from the disk to the computer memory for processing, and finally displays the processing results or finally writes them back to the database, which can improve the efficiency of data processing, because frequent disk data Swapping reduces efficiency. There are two types of cursors: implicit cursors and explicit cursors. Implicit cursor: For SELECT…INTO… statements (only one row of records can be extracted from the database at a time) and DML (data manipulation language, that is, INSERT statements, UPDATE statements and DELETE statements), the system will use implicit cursors . Display cursor: For a SELECT statement with more than one record in the result set, the programmer needs to define a display cursor. the implicit cursor Four properties of implicit cursors %FOUNT The value true indicates that the single-line query statement or…
altersystemflush “oracle cache”
alter system flush “oracle cache” the The following three statements are designed to refresh the oracle cache. Here is a summary. the 1) alter system flush global context The illustration below: www.2cto.com For a multi-tier architecture, as shown in the figure above: the application server and the data block server communicate through the connection pool, and the information about the connection pool is kept in the SGA. This statement clears the connection information. the 2) alter system flush shared_pool Will clear all the SQL execution plans previously saved in the library cache and data dictionary cache, but will not clear the recently executed entries cached in the shared sql area or the shared pl/sql area. Refreshing the shared pool can help merge small chunks, release a few shared pool resources, and temporarily solve the fragmentation problem in shared_pool. However, this practice is generally not recommended. The reasons are as follows: Flush Shared Pool will cause the currently unused cursor to be cleared out of the shared pool. If these SQLs need to be executed later, the database will undergo a lot of hard parsing, the system will experience severe CPU contention, and the database will generate intense Latch…
Problem solved – the symbol “(” appears when one of the following is required)
Problem solved – the symbol “(” appears when one of the following is required) the When creating a stored procedure today, I encountered a problem, as follows: www.2cto.com PROCEDURE QIUYUE.PRO_INSERT_DYNA_TABLE compile error ERROR: PLS-00103: The symbol “(” occurred when one of the following was expected: := . ) , @ % default character The symbol “:=” is replaced with “(” to continue. row: 3 Text: id in number(3) the Below is my stored procedure: [sql] create or replace procedure pro_insert_dyna_table ( www.2cto.com id in number(3), name in varchar2 ) is str_sql varchar2(500); begin str_sql :='insert into pro_create_dyna_table values(:1,:2)'; execute immediate str_sql using id,name; end pro_insert_dyna_table; the It is said to be a “(” problem, but I took a closer look, and it is still fine. Suddenly, I encountered such a problem when executing the PL program block in the morning: [sql] declare sum number; begin execute immediate 'select count(*) from stu' into sum; dbms_output.put_line(sum); –Wrong cursor position end; the ORA-06550: line 5, column 25: PLS-00103: Symbol “)” occurs when one of the following is required: ( www.2cto.com the It’s still the problem of brackets. I have been working on it for a long time in the morning, because I feel…
Error: The system has not been taken off maintenance mode completely solution
Error: The system has not been taken off maintenance mode completely solution the After patching, restart EBS Server, and report Error when logging in with IE The system has not been taken off maintenance mode completely. Please contact your System Administrator. www.2cto.com the From the error point of view, it seems that the Maintenance mode is not closed, and then use adadmin to close the Maintenance mode, but after disabling the Maintenance mode, the same error is still reported. the The following SQL also shows that the server is not in Maintenance mode now: select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual; Output:NORMAL www.2cto.com (‘MAINT’ means Maintenance mode is on, ‘NORMAL’ means Maintenance mode is off) Restart the server again, refresh IE, the same error still occurs. the The final solution: close IE, and then open EBS, the error disappears, it may be the problem of IE cache. the the Author pan_tian
Solve the problem that OracleDBConsoleorcl cannot start
Solve the problem that OracleDBConsoleorcl cannot start the Execute emctl.bat manually, then start the console, execute emctl.bat istart dbconsole, report an error, ORACLE_SID is not defined, open emctl.bat to see, here is the place to define environment variables, which have been set: the if not defined REMOTE_EMDROOT (set ORACLE_HOME=Ec:oracleproduct10.2.0db_1) the if not defined REMOTE_EMDROOT (set EMDROOT=%ORACLE_HOME%) www.2cto.com if defined REMOTE_EMDROOT (set ORACLE_HOME=%REMOTE_EMDROOT%) the if defined REMOTE_EMDROOT (set EMDROOT=%ORACLE_HOME%) the if defined REMOTE_EMDROOT (set LOCAL_EMDROOT=c:oracleproduct10.2.0db_1) … There is another setting in the back: the If not defined DBCONSOLE_SERVICE_NAME (set DBCONSOLE_SERVICE_NAME=OracleDBConsole%ORACLE_SID%), ORACLE_SID is used, but the previous settings are not seen, so we can only try to manually set ORACLE_SID, so we added a setting: www.2cto.com if defined REMOTE_EMDROOT (set ORACLE_SID=orcl) orcl is my database instance name Start OracleDBConsoleorcl in the service, ok, no error is reported; After restarting the computer, no error was reported, and the OracleDBConsoleorcl service was also started. emctl istart dbconsole emctl istop dbconsole emctl istatus dbconsole the the Author leaves fall and flowers bloom
Check the execution plan of SQL without using third-party tools
Check the execution plan of SQL without using third-party tools the SQL> connect sys as sysdba –Create the table used by the execution plan SQL> @?\rdbms\admin\utlxplan The Autotrace permission can be granted to each user through the following methods. If you need to limit the Autotrace permission, you can change the authorization to the public to the authorization to a specific user. www.2cto.com 1. Assign all permissions to the plan_table table to the public role SQL> grant all on plan_table to public; 2. Create a role plustrace SQL> create role plustrace; 3. Authorize to the plustrace role SQL> grant select on v_$sesstat to plustrace; SQL> grant select on v_$statname to plustrace; SQL> grant select on v_$session to plustrace; the 4. Authorize the role plstrace to DBA SQL> grant plusrace to dba with admin option; 5. DBA grants authority to public SQL> grant plusrace to public; In this way, users can set the following options in sqlplus SET AUTOTRACE OFF —————- Do not generate AUTOTRACE reports, this is the default mode SET AUTOTRACE ON EXPLAIN —— AUTOTRACE only displays the optimizer execution path report SET AUTOTRACE ON STATISTICS — show only execution statistics SET AUTOTRACE ON —————– Include execution plan and…
Locate important (resource-consuming) SQL statements
Locate important (resource-consuming) SQL statements the 1. statspack– capture the 15-minute report when the business on your library is the busiest, and look at the top sql inside 1 top Find the pid that consumes more resources the 2 Determine whether it is an oracle application process or a background process the 3 Locate according to v$session, v$process, v$sqlarea the Top session with oem www.2cto.com the SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls the FROM V$SQLAREA the WHERE buffer_gets > 10000000 OR disk_reads > 1000000 ORDER BY buffer_gets + 100 * disk_reads DESC the Author Guo Xuepeng
