Różności …

http://www.orafaq.com/wiki/SQL*Loader_FAQ

AUDIT SELECT, INSERT, UPDATE, DELETE ON HR.P_LISTA;

COMMIT;

AUDIT SELECT, INSERT, UPDATE, DELETE ON HR.P_LISTA BY ACCESS;

NOAUDIT SELECT, INSERT, UPDATE, DELETE ON HR.P_LISTA;

AUDIT INSERT, UPDATE, DELETE ON HR.P_LISTA BY ACCESS;

The user TSUTTON had auditing set up BY SESSION and we see that the ACTION_NAME appears as ‘SESSION REC’ in each case, which shows that auditing is being done at the session level. The user BWILLIAMS had auditing set at the access level, and here we see a listing of every insert and delete, along with the timestamp of the action, even though several inserts were performed on the same table in the same session.

Źródła:

http://www.dbspecialists.com/december-2003.html

http://download.oracle.com/docs/cd/B10500_01/server.920/a96521/audit.htm

Błąd pojawił się przy generowaniu raportu.

This ORA-04030 can be caused by a shortage of RAM on a dedicated (non shared server) environment, a too small PGA, and by not setting kernel parameters large enough to allow enough RAM.  The ORA-04030 is also common when running an import.  See import ORA-04030 error tips.

Also see MOSC Note 233869.1 titled “Diagnosing and Resolving ORA-4030 errors”.

Essentially the ORA-04030 is clear, and “out of process memory” error, whereby Oracle cannot get the RAM needed to complete the operations.  Depending on your release of Oracle, you have several options:

• Increase pga_aggregate_target
• Decrease sort_area_size and/or hash_area_size
• Move to multi-threaded server (a.k.a. MTS or shared servers)

Aktualna wartość pga_aggregate_target to 1073741824 (1 GB), sort_area_size: 1048576 (1 MB)

sql>create pfile from spfile
plik powstaje w c:\oracle\ora92\database\INITpersonel.ora
zmieniam pga_aggregate_target na 1610612736 (1,5 GB)
sql>shutdown immediate
sql>create spfile from pfile
sql>startup

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.
Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Oracle OLTP features.

Nonescalating row-level locking

Multiversion read consistency - Oracle provides statement-level and transaction-level data consistency withoutrequiring read locks. Oracle uses rollback segments toreproduce data as it existed at the time the query started.

Shared SQL - The parsing of a SQL statement is fairly CPU-intensive. Oracle caches parsedand optimized SQL statements in the shared SQL area within the shared pool.

Bind Variables and Shared SQL
Oracle’s shared SQL is a key feature for building high-performanceapplications. In an OLTP application, similar SQL statements may be used repeatedly,but each SQL statement submitted will have different selection criteria contained in theWHERE clause to identify the different sets of rows on which to operate. Oracle canshare SQL statements, but the statements must be absolutely identical.To take advantage of this feature for statements that are identical except for specificvalues in a WHERE clause, you can use bind variables in your SQL statements. Thevalues substituted for the bind variables in the SQL statement may be different, but thestatement itself is the same.
Youcan write the application to use bind variables

UPDATE emp SET salary = salary * (1 + :v_incr)
WHERE empno = :v_empno;

Stored outlines - The route a SQL statement takes during executionis critical for high performance. Once application developers and DBAshave tuned a SQL statement for maximum efficiency, they can force the Oracleoptimizer to use the same execution plan regardless of environmental changes.

Multi-Threaded Server/shared server – allow Oracle to support larger user populations.

Database Resource Manager - The DRM allocates CPU and parallelism resources to consumer groups based on resource plans. For example, you can allocate 80 percent of the CPU resources to order-entry users, with the remaining 20 percent allocated to users asking for reports. If the order-entry users aren’t using all the allocated resources, the reporting users can use more than their allotted percentage. With the DRM, you can dynamically alter the details of the plan without shutting down the instance. Oracle9i also added to the Database Resource Manager the ability to proactively estimate the amount of CPU that an operation will require. If an operation looks as if it will exceed the maximum CPU time specified for a resource group, the operation will not be executed, which can prevent inappropriately large operations from even starting.
The DRM now allows a DBA to specify the number of active sessions availableto a consumer group.

Real Application Clusters (235)

The I/O performed by most OLTP systems tends to beapproximately 70–80 percent read and 20–30 percent write.

Business intelligence data warehousing systems and OLTP systems could accessmuch of the same data, but these types of systems also typically have differentrequirements in terms of CPU, memory, and data layout, which makes supporting amixed workload less than optimal for both types of processing.

Real ApplicationClusters, with dynamic service provisioning since Oracle Database 10g, makes it possibleto allocate individual nodes for individual workloads.

Oracle7 introduced stored procedures written in PL/SQL, Oracle’s proprietary languagefor writing application logic. These procedures are stored in the database andexecuted by clients issuing remote procedure calls (RPCs) as opposed to executingSQL statements. Instead of issuing multiple SQL calls, occasionally with intermediatelogic to accomplish a task, the client issues one procedure call, passing in therequired parameters. The database executes all the required SQL and logic using theparameters it receives.

If the database server failswhile executing some transaction, the application server can resubmit the transactionto a surviving database server, as control of the transaction lies with theapplication server.

dbverify
dbv file=USER01.DBF blocksize=8192
select status from v$datafile;
select status from dba_tablespaces;
recover datafile 9;
przeniesienie pliku
shutdown immediate;
cp plik do nowej lokalizacji
startup mount
alter database rename file ‘sfasfaf’ to ‘fasdfas ‘
alter database open
alter tablespace USERS add datafile ‘f:fsdfs\dsafas.dbf’ size=1024M autoextend=0n;