Różności …

The first place you’ll likely begin looking for resource bottlenecks is in the Oracle database software using Oracle Enterprise Manager.

Oracle’s dynamic performance views provide insight into bottlenecks within your Oracle database. Prior to the introduction of Oracle’s Automatic Workload Repository (AWR), the Automatic Database Diagnostics Monitor (ADDM), and Oracle Enterprise Manager Grid Control in Oracle Database 10g, querying the performance views often was the first step database administrators performed in determining bottlenecks. All of these performance views have names that begin with V$, and, from Oracle9i on, there are also global views (for all nodes in a Real Application Clusters or RAC database) that begin with GV$.

V$SYSTEM_EVENT – Provides aggregated, system wide information about the resources for which the whole instance is waiting
V$SESSION_EVENT - Provides cumulative list of events waited for in each session
V$SESSION_WAIT -  Provides detailed, session-specific information about the resources for which individual sessions are currently waiting or last waited for
V$SESSION – Provides session information for each current session including event currently or last waited for

You may find that your problem has a simple source, such as a lower-than-expected database buffer cache hit ratio. Since the cache is not working at its optimal level, you could simply increase the initialization parameter DB_BLOCK_BUFFERS to increase the size of the cache and possibly improve the hit ratio. You can monitor the performance of the buffer cache hit ratio in V$METRICNAME.

The Automatic Workload Repository (AWR) captures and stores information about resource utilization by Oracle workloads. By default, statistics are captured every 30 minutes and are stored for 7 days. These statistics are accessible through views, but Enterprise Manager provides a much simpler-to-use interface.

Oracle’s Automatic Database Diagnostic Monitor ADDM automatically identifies and reports on resource bottlenecks, such as CPU contention, locking issues, or poor performance from specific SQL statements. For tuning your applications, you’ll likely look to the SQL Advisor – it combines the functionality of the SQL Tuning Advisor, the SQL Access Advisor, and the new Partition Advisor. The SQL Advisor leverages information on CPU and I/O consumption captured in the AWR and identifies high impact SQL statements indicated by the ADDM to make recommendations.

Memeory Advizor - For optimal setting of MEMORY_TARGET for automatic memory management in Oracle Database 11g.and optimal setting of SGA_TARGET for shared memory management.
Segment Advizor – for storage management and space allocation.
Undo Advisor –  for managing transactions.
Mean Time to Recovery (MTTR) –  optimize the setup of Oracle, including log files.

The performance of your Oracle database is based on how it uses the machine resources that are available. These machine resources include processing power or CPU, memory, disk I/O, and network bandwidth.

The slowest access is to disk and, as a result, the most common database performance issues are I/O related.

Since the introduction of Enterprise Manager 10g, a performance analyzer called Automatic PerformanceMonitoring (APM) has been included.

The number of blocks in one multiblock I/O is determined by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT.

The main destinations of the I/O operations Oracle performs are the following:

• Redo logs

• Data contained in tables

• Indexes on the tables

• The data dictionary, which goes in the SYSTEM tablespace

• Sort activity, which goes in the TEMP tablespace of the user performing the sort

• Rollback information, which is spread across the datafiles of the tablespace containing the database’s rollback segments

• Archived redo logs, which go to the archived log destination (assuming the database is in ARCHIVELOG mode)

Use disk-striping technologies to spread I/O evenly across multiple spindles.
One of the most powerful ways to reduce performance bottlenecks due to disk I/O is the use of RAID disk arrays. (Redundant Array of Inexpensive (or Independent) Disks.

Use tablespaces to clearly segregate and target different types of I/O - Separate table I/O from index I/O by placing these structures in different tablespaces. Youcan then place the datafiles for these tablespaces on various disks to provide better performance for concurrent access.

Place redo logs and redo log mirrors on the two least-busy devices.

Distribute “system overhead” evenly over the available drives. For example, if the application generates a lot of data changes versus data reads, the I/O to the rollback segments may increase due to higher writes for changes and higher reads for consistent read functionality. Sort activity can also affect disk I/O (TEMP – tablespace). Oracle constantly queries and updates the data dictionary stored in the SYSTEM tablespace, and this information is cached in the shared pool section of the SGA.

Use a different device for archiving and redo log files. (183)

Every database has a pseudorole named PUBLIC that includes every user.
Base tables and data dictionary views are stored in the SYS schema. SYSTEM schema tables are used for administrative information and by various Oracle tools and options.
Po połączeniu “as sysdba” można wykonać: startup, shutdown, alter database mount, alter database open, alter database backup control file,  alter database archivelog, alter database recover, create database, drop database, create spfile, restricted session.
Sysoper nie może zrobić: create i drop database.

The CONNECT INTERNAL syntax supported in earlier releases of Oracle is no longer available. When operating system authentication is used, administrative users must be named in the OSDBA or OSOPER defined groups. For password file authentication, the file is created with the ORAPWD utility. Users are added by SYS or by those having SYSDBA privileges.

A policy is a way to extend your security framework. You can specify additional requirements in a policy that are checked whenever a user attempts to activate a role. Policies are written in PL/SQL and can be used, for example, to limit access to a particular IP address or to particular hours of the day.

FGAC - fine-grained access control. Security policies implemented as PL/SQL functions can be associated with tables or views enabling creation of a virtual private database (VPD).You can associate a security policy with a particular view or table by using the builtin PL/SQL package DBMS_RLS, which also allows youto refresh, enable, or disable a security policy.

Oracle Database 10g and newer database releases feature a VPD that is even more fine-grained, enabling enforced rewrites when a query references a specific column.

The Oracle Label Security Option eliminates the need to write VPD PL/SQL programs to enforce row-level label security.

Policies are created and applied, sensitivity labels are defined, and user labels are set and authorized through a policy manager tool accessible through EM.

Application developers can place a SET ROLE command at the beginning of an application to enable the appropriate role and disable others only while the application is running. Similarly, you can invoke a DBMS_SESSION.SET_ROLE procedure from PL/SQL.

Another way application security is sometimes accomplished is by encapsulating privileges in stored procedures. Instead of granting direct access to the various tables
for an application, youcan create stored procedures that provide access to the tables and grant access to the stored procedures instead of the tables. For example, instead of granting INSERT privileges for the EMPLOYEE table, youmight create and grant access to a stored procedure called HIRE_EMPLOYEE that accepts as parameters all the data for a new employee.

When you run a stored procedure normally, the procedure has the access rights that were granted to the owner of the procedure; that owner is the schema in which the procedure resides. If a particular schema has access to a particular database object, all stored procedures that reside in that schema have the same rights as the schema. When any user calls one of those stored procedures, that user has the same access rights to the underlying data objects that the procedure does.

If youattach the keyword AUTHID CURRENT_USER to a stored procedure when it is compiled, security restrictions will be enforced based on the username of the user invoking the procedure, rather than the schema that owns the stored procedure.

Global authentication allows you to maintain a single authentication list for multiple distributed databases.

In typical three-tier implementations, the Oracle Application Server runs some of the application logic, serves as an interface between the clients and database servers, and provides much of the Oracle Identity Management (OIM) infrastructure.

The Oracle Advanced Security Option (ASO), is used in distributed environments linked via Oracle Net in which there are concerns regarding secure access and transmission of data. This option specifically provides data encryption during transmission.

Transparent Data Encryption - the database does the work of encrypting and decrypting data automatically. Data sent to the database is encrypted by Oracle, and data requested from the database is decrypted. No additional code is required in an application. Oracle Database 11g allows youto encrypt entire tablespaces.

Compliance
The Oracle Database Vault Option - restricts DBAs and other highly privileged users from accessing application data to which they should not have access. Key parameters defined in the Oracle Database Vault Option are called factors. Factors include things such as specific application programs, locations, or times of day.

Rules can also be used to define database realms, which consist of a subset of the schemas and roles that an administrator can administer. (173)

Zalecenia:

Multiplexing online redo logs by having multiple log members per group on different disks and controllers

Running the database in ARCHIVELOG mode so that redo log files are archived before they are reused

Archiving redo logs to multiple locations

Maintaining multiple copies of the control file(s)

Backing up physical datafiles frequently—ideally, storing multiple copies in multiple locations

Information Lifecycle Management (ILM) -is most frequently used to move data among various devices that are most appropriate for hosting that data, such as different classifications of disk. (160)

SQL AdvisorStatistics containing active session history are now gathered and populate the Automatic Workload Repository (AWR).

The Automatic Database Diagnostic Monitor(ADDM) automatically tracks changes in database performance leveraging the datain the AWR.

Server-generated alerts occur “just-in-time” and appear in EnterpriseManager.

ADDM is one of several advisors present in Oracle and accessible using EnterpriseManager today. Other performance related advisors include:

Triggers

There are three types of events that can cause a trigger to fire:

• A database UPDATE

• A database INSERT

• A database DELETE

There are three times when a trigger can fire:

• Before the execution of the triggering event

• After the execution of the triggering event

• Instead of the triggering event

Triggers are defined and stored separately from the tables that use them. Oracle8i and beyond also support Java as a procedural language, so you can create Java triggers with those versions. Youcan write a trigger directly in PL/SQL or Java, or a trigger can call an existing stored procedure written in either language.

Query Optimization

When a SQL query is submitted to an Oracle database, Oracle must decide how to access the data. The process of making this decision is called query optimization. This retrieval is known as the execution path.

For instance, even with a query that involves only a single table, Oracle can take either of these approaches:

• Use an index to find the ROWIDs of the requested rows and then retrieve those rows from the table.
• Scan the table to find and retrieve the rows; this is referred to as a full table scan.

Although it’s usually much faster to retrieve data using an index, the process of getting the values from the index involves an additional I/O step in processing the query. Using the index values to select the desired rows involves less I/O and is therefore more efficient than retrieving all the data from the table and then imposing the selection conditions. Another factor in determining the optimal query execution plan is whether there is an ORDER BY condition in the query that can be automatically implemented by the presorted index. Alternatively, if the table is small enough, the optimizer may decide to simply read all the blocks of the table and bypass the index since it estimates the cost of the index I/O plus the table I/O to be higher than just the table I/O.

Prior to Oracle Database 10g, you could choose between two different Oracle query optimizers, a rule-based optimizer and a cost-based optimizer. With Oracle Database 10g, the rule-based optimizer is desupported. As the name implies, the cost-based optimizer does more than simply look at a set of optimization rules; instead, it selects the execution path that requires the least number of logical I/O operations. To properly evaluate the cost of any particular execution plan, the cost-based optimizer uses statistics about the composition of the relevant data structures. These statistics are automatically gathered by default since the Oracle Database 10g release into the Automatic Workload Repository (AWR).

The accuracy of the cost-based optimizer depends on the accuracy of the statistics it uses, so updating statistics has always been a must. Formerly, you would have used the SQL statement ANALYZE to compute or estimate these statistics. When managing an older release, many database administrators also used a built-in PL/SQL package, DBMS_STATS, that contains a number of procedures that helped automate the process of collecting statistics.

Oracle gives youa way to influence the decisions of the optimizer with a technique called hints. A hint is nothing more than a comment with a specific format inside a SQL statement.
Here, the hint forces the optimizer to use the EMP_IDX index for the EMP table:
SELECT /*+ INDEX(EMP_IDX) */ LASTNAME, FIRSTNAME, PHONE FROM EMP

Starting with Oracle8i, youcou ld create a stored outline that stored the attributes used by the optimizer to create an execution plan.

With the release of Oracle Database 11g, Oracle suggests that you move your stored outlines to SQL plan baselines.

The SQL*Analyzer tool is designed to give youthe ability to recognize potential problems caused by optimizer upgrades.

Oracle Database 11g also includes a feature called Database Replay. This feature captures workloads from production systems and allows them to be run on test systems. With this capability, you can test actual production scenarios against new configurations or versions of the database, and Database Replay will spot areas of potential performance problems on the changed platform.

The  SQL EXPLAIN PLAN statement. When you use EXPLAIN PLAN, followed by the keyword FOR and the SQL statement whose execution plan you want to view, the Oracle cost-based optimizer returns a description of the execution plan it will use for the SQL statement and inserts this description into a database table.

The optimizer writes all of this information to a table in the database. By default, the optimizer uses a table called PLAN_TABLE; make sure the table exists before you use EXPLAIN PLAN. (The utlxplan.sql script included with your Oracle database creates the default PLAN_TABLE table.)

There are other times when youwant to look at the plans for a group of SQL statements. For these situations, you can set up a trace for the statements you want to examine and then use the second utility, TKPROF, to give you the results of the trace in a more readable format in a separate file. At other times, youmight also use Oracle’s SQL Trace facility to generate a file containing the SQL generated when using TKPROF in tuning applications.

You must use the EXPLAIN keyword when you start TKPROF, as this will instruct the utility to execute an EXPLAIN PLAN statement for each SQL statement in the trace file. Youcan also specify how the results delivered by TKPROF are sorted. For instance, youcan have the SQL statements sorted on the basis of the physical I/Os they used; the elapsed time spent on parsing, executing, or fetching the rows; or the total number of rows affected. The TKPROF utility uses a trace file as its raw material. The trace process can significantly affect the performance of an application, so you should turn it on only when you have some specific diagnostic work to do.

You can also view the execution plan through Enterprise Manager for the SQL statements that use the most resources. Tuning your SQL statements isn’t a trivial task, but with the EXPLAIN PLAN and TKPROF utilities you can get to the bottom of the decisions made by the cost-based optimizer.

SQL ADVISORS

Oracle Database 10g added a tool called the SQL Tuning Advisor. This tool performs advanced optimization analysis on selected SQL statements, using workloads that have been automatically collected into the Automatic Workload Repository or that you have specified yourself. Once the optimization is done, the SQL Tuning Advisor makes recommendations, which could include updating statistics, adding indexes, or creating a SQL profile. This profile is stored in the database and is used as the optimization plan for future executions of the statement, which allows you to “fix” errant SQL plans without having to touch the underlying SQL.

The tool is often used along with the SQL Access Advisor since that tool provides advice on materialized views and indexes. Oracle Database 11g introduces a SQL Advisor tool that combines functions of the SQL Tuning Advisor and the SQL Access Advisor (and now includes a new Partition Advisor). The Partition Advisor component advises on how to partition tables, materialized views, and indexes in order to improve SQL performance.

Data Dictionary Tables

DBA_
Includes all the objects in the database. A user must have DBA privileges to use this view.
USER_
Includes only the objects in the user’s own database schema.
ALL_
Includes all the objects in the database to which a particular user has access. If a user has been granted rights to objects in another user’s schema, these objects will appear in this view.

alter table
table_name
DISABLE constraint
constraint_name;

lub

begin
for i in (select constraint_name, table_name from user_constraints) LOOP
execute immediate ‘alter table ‘||i.table_name||’ disable constraint ‘||i.constraint_name||”;
end loop;
end;
/

SQL> alter table transaction disable constraint TRANSACTION_PK;
alter table transaction disable constraint TRANSACTION_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) – dependencies exist

Cause of the Problem
Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint.

Solution of the Problem
Two solutions exist for this problem.
1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint.

SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
       p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
       FROM dba_constraints p
       JOIN dba_constraints c ON(p.constraint_name=c.r_constraint_name)
       WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
       AND c.constraint_type = 'R'
       AND p.table_name = UPPER('&table_name');
Enter value for table_name: transaction
old   7:      AND p.table_name = UPPER('&table_name')
new   7:      AND p.table_name = UPPER('transaction')

Parent Table                   Child Table                    Parent Constraint              Child Constraint
------------------------------ ------------------------------ ------------------------------ ------------------------------
TRANSACTION                    USER_SALARY_RECORD             TRANSACTION_PK                 SYS_C005564
TRANSACTION                    TRANSACTION_DETAIL             TRANSACTION_PK                 TRNSCTN_DTL_TRNSCTN_FK

SQL> alter table USER_SALARY_RECORD disable constraint SYS_C005564;
Table altered.

SQL> alter table TRANSACTION_DETAIL  disable constraint TRNSCTN_DTL_TRNSCTN_FK;
Table altered.

SQL> alter table transaction disable constraint TRANSACTION_PK;
Table altered.

2)Disable the constraint with cascade option.
SQL> alter table transaction disable constraint TRANSACTION_PK cascade;
Table altered.

The six types of integrity constraint are described briefly here and more fully in “Semantics”:

• A NOT NULL constraint prohibits a database value from being null.
• A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
• A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
• A foreign key constraint requires values in one table to match values in another table.
• A check constraint requires a value in the database to comply with a specified condition.
• A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.

Trzeba znaleźć w PKs dany constraint i sprawdzić do jakiej tabeli się odwołuje. (ewentualnie usunąć)

It’s very easy to reproduce this situation:create table test1(test1_parent_id number,col1 varchar2 (30),constraint test1_pk primary key (test1_parent_id))/create table test2(test2_id number,test1_child_id number,col2 varchar2(30),constraint test2_fk foreign key (test1_child_id) references test1(test1_parent_id))/alter table test1 drop constraint test1_pk/ORA-02273: this unique/

select * from all_constraints
where constraint_type='R' and r_constraint_name='YOUR_CONSTRAINT';

(wykasować child constraints)

Przy kasowaniu przestrzeni tablic EXAMPLE (10g)

wcześniej należy:

DROP USER BI CASCADE;

DROP USER HR CASCADE;

DROP USER IX CASCADE;

DROP USER OE CASCADE;

DROP USER PM CASCADE;

DROP USER SH CASCADE;

Źródła:

http://www.dba-oracle.com/t_oracle_disable_constraints.htm

http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html

http://www.dba-oracle.com/t_constraints.htm

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm

http://www.dba-oracle.com/t_alter_table_add_constraint_syntax_example.htm

http://oraclequirks.blogspot.com/2007/09/ora-02273-this-uniqueprimary-key-is.html

role w oracle:
http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_26.shtml

ORA-02292 – przy kasowaniu rekordu z tabeli master
Błąd wskazuje właściciela i constraint name

Np: tworzenie constraint:

SQL> alter table emp
2 add (constraint job_fk foreign key (job_key)
3 references job (job_key)
4 on delete cascade);

Now, when INSERT or UPDATE the job key column in the EMP table, the foreign key constraint will check to insure that the job already exists in the JOB table (or at least the job key exist in the JOB table).

Foreign key constraints can also be disabled, enabled and dropped.
alter table emp disable constraint job_fk;
alter table emp enable constraint job_fk;
alter table emp drop constraint job_fk;

Now, let’s talk about the ON DELETE part of the constraint. When we created the foreign key constraint . we included ON DELETE SET NULL or ON DELETE CASCADE. This clause tells the database what to do with the child records when the parent record is deleted. In the example above, we created the job_fk constraint with ON DELETE CASCADE.

This will cause the database to cascade the deletes. If I go to the JOB table and DELETE a job, all the employees that have that job will also be deleted as the DELETE will cascade to the child rows.

If we use the ON DELETE SET NULL, then when we delete the parent record, the child records with that value will be set to NULL.

ORA-O6413
Ten błąd pojawił się po instalce Forms and Reports 9i na Win7 (64 bit) przy próbach połączenia z SQL Tools do dowolnej bazy danych.
Zmieniłem PATH i wpis “c:\orawin\bin” (od forms and reports) przeniosłem z pierwszej na ostatnią pozycję – to rozwiązało problem.

SGA_TARGET

ALTER SYSTEM SET SGA_TARGET=2048M scope=both;

alter SYSTEM SET SGA_MAX_SIZE=2560M scope=both;
ORA-02095: Podany parametr inicjalizacyjny nie może być modyfikowany

ALTER SYSTEM SET SGA_MAX_SIZE=2560M scope=spfile;
restart bazy

(w systemie jest 4GB RAM)

(w 32 bitowych obsługiwane 1,8 GB – jako SGA)

ORA-27100 shared memory realm already exist
sql> create pfile from spfile
C:\oracle\product\10.2.0\db_1\database\INITorcl.ora
zmienić wartość sga_max_size na mniejszą
sql>create spfile from pfile
zrestartować serwis i jest OK

sql> show parameter sga

Źródła:
http://www.dba-oracle.com/t_ora_02292_constraint_violation_child_record_found.htm

http://blog.flimatech.com/tag/ora-02292/

http://www.dbasupport.com/oracle/ora10g/sizingSGA02.shtml

Oracle Memory Configuration for Windows

The Expression Filter
The Expression Filter uses the Rules Manager to work with expressions.

Once you’ve completed an optimal logical database
design, youmu st go back and consider what indexes youshou ld add to improve the
anticipated performance of the database and whether youshou ld designate any
tables as part of a cluster or hash cluster.

Constraints
A constraint enforces certain aspects of data integrity within a database. When youadd a constraint to a particular column, Oracle automatically ensures that data violatingthat constraint is never accepted. (unique, not null)

Each table can have, at most, a single primary key constraint. The primary keymay consist of more than one column in a table. The primary key constraint forces each primary key to have a unique value. Itenforces both the unique constraint and the NOT NULL constraint. A primarykey constraint will create a unique index, if one doesn’t already exist for thespecified column(s).

The foreign key constraint is defined for a table (known as the child) that has arelationship with another table in the database (known as the parent). The value entered in a foreign key must be present in a unique or primary key of another specific table. For example, the column for a department ID in an employee table might be a foreign key for the department ID primary key in the department table. Normally, you cannot delete a row in a parent table if it causes a row in the childtable to violate a foreign key constraint. However, you can specify that a foreignkey constraint causes a cascade delete, which means that deleting a referenced row in the parent table automatically deletes all rows in the child table that reference the primary key value in the deleted row in the parent table.

All constraints can be either immediate or deferred. An immediate constraint is enforced as soon as a write operation affects a constrained column in the table. A deferred constraint is enforced when the SQL statement that caused the change in the constrained column completes.
You can temporarily suspend the enforcement of constraints for a particular table. When youenable the operation of the constraint, you can instruct Oracle tovalidate all the data for the constraint or simply start applying the constraint to thenew data. When you add a constraint to an existing table, youcan also specifywhether you want to check all the existing rows in the table. (126)

ALTER JAVA CLASS ODM.”/10b943ec_CompoundPredicate” resolve;

Kasowanie dużej ilości wierszy z tabeli:

– Create a test table
CREATE TABLE test_tbl
(test_col INTEGER)
/
– Populate it with 100,000 rows
BEGIN
FOR i in 1..100000 LOOP
INSERT INTO test_tbl
VALUES (i – TRUNC(i, -1));
END LOOP;
END;
/
COMMIT
/
– Delete 60% of the rows. This is the actual delete. You would just replace the
– where clause with yours.
DECLARE
TYPE tt_delete IS TABLE OF ROWID;
t_delete tt_delete;
CURSOR c_delete IS
SELECT ROWID
FROM test_tbl
WHERE test_col < 6;
l_delete_buffer PLS_INTEGER := 5000;
BEGIN
OPEN c_delete;
LOOP
FETCH c_delete BULK COLLECT
INTO t_delete LIMIT l_delete_buffer;
FORALL i IN 1..t_delete.COUNT
DELETE test_tbl
WHERE ROWID = t_delete (i);
EXIT WHEN c_delete%NOTFOUND;
COMMIT;
END LOOP;
CLOSE c_delete;
END;
/

(źródło: http://forums.oracle.com/forums/thread.jspa?threadID=345861)

i trochę inne podejście:

CREATE TABLE test_tbl_temp AS
SELECT *
  FROM TEST_TBL
 WHERE
MONTHS_BETWEEN(SYSDATE, TO_DATE(YRMO_NBR||'01','YYYYMMDD')) <= 38;

TRUNCATE TABLE test_tbl;
lub drop test_tbl; 

INSERT /*+ APPEND +/ INTO test_tbl
SELECT *
  FROM test_tbl_temp;

lub

RENAME TABLE test_tbl_temp to test_tbl;

ale tutaj trzeba uważać na ORA=02449 (unique/primary keys in table referenced by foreign keys)
aby sprawdzić jakie constraints odwołują się do tabeli można:
SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = “test_tabl”;

lub

DELETE FROM myfata$ WHERE rownum < 100000; –Start with a small number and gradually increase to see how many rows you can delete in one shot without hitting the UNDO problem.

lub

CREATE OR REPLACE PROCEDURE delete_tab (tablename IN VARCHAR2,
                                            empno IN NUMBER  ,
                                            nrows IN NUMBER    ) IS

 sSQL1    VARCHAR2(2000);
 sSQL2    VARCHAR2(2000);
 nCount    NUMBER; 

BEGIN

  nCount := 0;
  sSQL1:='delete from '|| tablename ||
         ' where ROWNUM < ' || nrows || ' and empno=' || empno;
  sSQL2:='select count(ROWID) from ' || tablename ||
         ' where empno= ' || empno;

  LOOP

    EXECUTE IMMEDIATE sSQL1;

    EXECUTE IMMEDIATE sSQL2 INTO nCount;

    DBMS_OUTPUT.PUT_LINE('Existing records: ' || to_char(ncount) );

    commit;    

    EXIT WHEN nCount = 0;

  END LOOP;

END delete_tab;
/

Wyłączenie wyzwalacza:
ALTER TRIGGER trigger_name DISABLE;

ALTER TABLE inventory
    ENABLE ALL TRIGGERS;

Najpierw ustawimy sobie polską klawiaturę.
Zainstaluj z poziomu systemu polską klawiaturę (quertz)

Zainstaluj patch:

/usr/sfw/bin/wget http://www.jaceksen.pl/pliki/pl-keyboard-5.10.patch
patch -i pl-keyboard-5.10.patch -p1 /usr/X11/lib/X11/xkb/symbols/pl

/usr/sfw/bin/wget http://www.jaceksen.pl/pliki/setxkbmap
chmod 755 setxkbmap
./setxkbmap pl

groupadd oinstall
groupadd dba
groupadd oper
useradd -g oinstall -G dba -d /export/home/oracle oracle
mkdir /export/home/oracle
chown -R oracle:oinstall /export/home/oracle
passwd -r files oracle (-r okresla repozytorium do ktorego sie odnosi)

unzip solaris.x64_11gR2_database_1of2.zip
unzip solaris.x64_11gR2_database_2of2.zip

projadd oracle

do pliku /etc/user_attr dopisujemy
oracle::::project=oracle

Loguję się na użytkownika Oracle i sprawdzam aktualne parametry:

prctl -n project.max-shm-memory -i project oracle

u mnie:

ustawiam nowe wartości:
# prctl -n project.max-shm-memory -v 4gb -r -i project oracle
zapisuję je na stałe do pliku /etc/project:
# projmod -s -K “project.max-shm-memory=(priv,4gb,deny)” oracle

sprawdzam /etc/project

Ku przestrodze:
The Oracle installer seems incapable of recognising kernel parameter set using resource control projects, but if you ignore the warnings the installation completes successfully.

Instaluję “SUNWi1cs” i “SUNWi15cs”:
# pkgadd -d /cdrom/sol_10_910_x86/Solaris_10/Product SUNWi1cs SUNWi15cs

mkdir -p /u01/app/oracle/product/11gR2/db_1
chown -R oracle:oinstall /u01

tworze plik .profile:

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
# Select the appropriate ORACLE_BASE
#ORACLE_BASE=/export/home/oracle; export ORACLE_BASE
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11gR2/db_1; export ORACLE_HOME
ORACLE_SID=db1sol; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
/usr/bin/setxkbmap pl

Źródła:
http://blogs.sun.com/roller/resources/timf/setxkbmap

http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnSolaris10.php

http://automaciej.jogger.pl/2009/02/23/solaris-10-i-polska-klawiatura-programisty

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.

Note that a temporary tablespace cannot contain permanent objects and therefore doesn’t need to be backed up.

Unlike normal data files, TEMPFILEs are not fully initialised (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.

TEMPFILEs are not recorded in the database’s control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. This opens interesting possibilities like having different TEMPFILE configurations between permanent and standby databases, or configure TEMPFILEs to be local instead of shared in a RAC environment.

One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database. Look at his example:

SQL> ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;

If you remove all tempfiles from a temporary tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty. Use the following statement to add a TEMPFILE to a temporary tablespace:

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;

Except for adding a tempfile, as illustrated in the above example, you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace (operations like rename, set to read only, recover, etc. will fail).

SQL> CREATE TEMPORARY TABLESPACE temp
      TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter.

To see the default temporary tablespace for a database, execute the following query: SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead. One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME                BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP                             52428800   52428800

Oracle 9i does not release allocated TEMP segments until you shutdown the database. While the TEMP segment is allocated, it does not mean that it is unavailable for use. When a user requests a disk sort, Oracle will allocate a TEMP segment. Once that sort is done, Oracle releases this TEMP segment for future use, but does not deallocate it. When the next user requests a disk sort, Oracle does not have to allocate a new TEMP segment. It uses the same one that no user is currently using. Oracle manages this for you in 9i. And there is really only one TEMP segment in the TEMP tablespace. Multiple users can utilitize this one segment.
There have been problems in 9i where users running a TEMPORARY TEMP tablespace with TEMPFILES that is Locally Managed where Oracle does not release the sort space once it is no longer being used. This is a known bug. The workaround has been to revert back to Dictionary Managed tablespace for TEMP.

Tekst pochodzi ze strony: http://www.orafaq.com/node/2

Tu można znaleźć wszystko czego dusza zapragnie:
http://www.oracle-base.com/articles/11g/OracleDB11gR2InstallationOnFedora14.php

Fajna strona o parametrach SHMALL, SHMMAX ..
http://www.idevelopment.info/data/Oracle/DBA_tips/Linux/LINUX_8.shtml

Fajna strona o ustawianiu parametrów TCP w Linuksie:
http://www.speedguide.net/articles/linux-tweaking-121

Strona o instalacji OHS:
http://apex-at-work.blogspot.com/2010/11/oracle-http-server-ohs-part-of-oracle.html

aio-nr & aio-max-nr:
	aio-nr is the running total of the number of events specified on the
	io_setup system call for all currently active aio contexts.  If aio-nr
	reaches aio-max-nr then io_setup will fail with EAGAIN.  Note that
	raising aio-max-nr does not result in the pre-allocation or re-sizing
	of any kernel data structures.

you can increase the maximum number of open files by setting a new value
in kernel variable /proc/sys/fs/file-max

Setting SHMALL Parameter
This parameter sets the total amount of shared memory pages that can
be used system wide. Hence, SHMALL should always be at least
ceil(shmmax/PAGE_SIZE).
The default size for SHMALL in RHEL 3/4 and 2.1 is 2097152 which is also
Oracle's recommended minimum setting for 9i and 10g on x86 and x86-64
platforms. In most cases this setting should be sufficient since it means
that the total amount of shared memory available on the system is
2097152*4096 bytes (shmall*PAGE_SIZE) which is 8 GB. PAGE_SIZE is usually
4096 bytes unless you use Big Pages or Huge Pages which supports the
configuration of larger memory pages.
First off, editing sysctl.conf doesn't change the value until you either reboot, or execute:
sysctl -p
To have it reload the values.
You mention this is a 32-bit Linux. That puts constraints on how large you can set SHMMAX
 to and how big the Oracle SGA can be. See Installing Oracle9i on FC2 for more information
 about the limits you'll run into here. The largest generally useful setting is this:
kernel.shmmax=2147483648
And since the one you tried is >4GB that's why it failed altogether.
Many people seem to use some guide or Oracle's suggestions for a setting here as a magic
number without actually considering whether the shared memory values really make sense
for their system or not. I wrote the following little script to generate the settings
for me on Linux. As written, it limits the shared memory block to 50% of total RAM, which
might be light for your Oracle use; easy to adjust it to a higher percentage.
I hate seeing people set this value to higher than the amount of RAM in their server.
#!/bin/bash
mem_bytes=`awk '/MemTotal:/ { printf "%0.f",$2 * 1024}' /proc/meminfo`
mem_max=`expr $mem_bytes / 2`
page_size=`getconf PAGE_SIZE`
shmall=`expr $mem_bytes / $page_size`
echo \# Maximum shared segment size in bytes
echo kernel.shmmax = $mem_max
echo \# Maximum number of shared memory segments in pages
echo kernel.shmall = $shmall
The output from this can get written right to the end of the sysctl.conf, run "sysctl -p",
and you're off with a reasonable yet safe setting.
shmmni specifies the maximum number of shared memory segments allowed to exist simultaneously,
system-wide.
Setting shmmni to an arbitrarily large number wastes memory and can degrade system performance.
 Setting the value too high on systems with small memory configuration may consume enough memory
space that the system cannot boot. Select a value that is as close to actual system requirements
as possible for optimum memory usage. A value not exceeding 1024 is recommended unless system
requirements dictate otherwise.

Semaphores can best be described as counters which are used to provide synchronization between
processes or between threads within a process for shared resources like shared memories.
System V semaphores support semaphore sets where each one is a counting semaphore. So when an
application requests semaphores, the kernel releases them in “sets”. The number of semaphores
per set can be defined through the kernel parameter SEMMSL
/proc/sys/net/core/rmem_max - Maximum TCP Receive Window
/proc/sys/net/core/wmem_max - Maximum TCP Send Window
/proc/sys/net/ipv4/tcp_rmem - memory reserved for TCP rcv buffers (reserved memory per connection default)
/proc/sys/net/ipv4/tcp_wmem  - memory reserved for TCP snd buffers (reserved memory per connection default)

/etc/selinux/config
ustawić selinux=disabled

The xhost program is used to add and delete host names or user names to the list allowed to make connections
to the X server.

Po wykonaniu instalki wg. instrukcji instalator dalej marudzi:


Gdy się da ignoruj, to wywala się na linkowaniu binariów:

aby pozbyć się błędu ins_emagent.mk należy:

This error is due to a change in the GCC linker in Fedora 13. The announcement is here. The fix is to
edit $ORACLE_HOME/sysman/lib/ins_emagent.mk, search for the line $(MK_EMAGENT_NMECTL) and replace the
line with $(MK_EMAGENT_NMECTL) -lnnz11 as shown above.
Informacja pochodzi ze strony:
http://blog.fpmurphy.com/2010/08/installing-oracle-11g-release-2-on-fedora-13.html#ixzz1BIWbQ3ps

Logowanie do EM: OK

emctl stop dbconsole
emctl start dbconsole

You can install and run multiple versions of Oracle on a single-server machine. AllOracle products use a directory referred to by the environment or system variable ORACLE_HOME to find the base directory for the software they will use.

A rolling upgrade allows you to bring down some of the nodes of the cluster, upgrade their software, and then bring them back online as part of the cluster.

Online transaction processing (OLTP) systems usually have a larger number of users performing smaller transactions, while data warehouses usually have a smaller number of users performing larger queries.

With Automatic Storage Management (ASM), introduced in Oracle Database 10g, you can add additional disk space or take away disks without interrupting database service.

The Automatic Workload Repository (AWR), first available in Oracle Database 10g, maintains a history of workload and performance measurements, which are used by the Automatic Database Diagnostic Monitor (ADDM) to spot performance anomalies. You can also use AWR to track ongoing changes in workload.

Oracle Database Configuration Assistant
Tworzenie ze skryptów: The Oracle software CD-ROM also includes a sample scriptcalled BUILD_DB.SQL

A version of Oracle Net runs on the client machine and on the database server, andallows clients and servers to communicate over a network using virtually any popularnetwork protocol.
Local name resolution – TNSNAMES.ORA
Oracle Names service -  przestarzały 9i
Oracle Internet Directory – Lightweight Directory Access Protocol (LDAP). Oracle Internet Directory (OID) is a part of FusionMiddleware. The OID is also used for a variety of other purposes, such as enabling single sign-on for the Oracle Application Server Portal.
Host naming

These name resolution options are not mutually exclusive. For example, you can use Oracle Internet Directory and local name resolution (TNSNAMES.ORA files)together. In this case, you specify the order Oracle should use in resolving names inthe SQLNET.ORA file.

System tuning
- how to size redo_logs aby nie przełączały się co 2 sekundy
log_checkpoint_timeout
log_checkpoint_interval
fast_start_mttr_target (10g)
- pga_aggregate_target (9i)

program global area - zastepuje: sort_area_size, hash_area_size, bitmap_merge_area_size, create_bitmap_area_size

16% całej pamięci na OLTP
40% całej pamięci na data warehouse

- pinning packages in memory
- statspack

należy ustawić statistics_level = Typical|All
show parameter statistics_level

i badać widkoki:
v$shared_pool_advice
select shared_pool_size_for_estimate, shared_pool_size_factor,est_lc_time_saved from v$shared_pool_advice;
pierwsza kolumna to MB
można ustawić na ostatnią wartość

alter system set shared_pool_size=124m scope=spfile;
shutdown immediate;
startup

show parameter share_pool_size

v$pga_target_advice
v$db_cache_advice
v$java_pool_advice

SQL tuninig
- indexes
- oracle optimizer
- explain plan – co zrobi baza przy wykonaniu czegos ale tak naprawde nie bedzie tego wykonywala
- hins – into individual statements
- stored outlines – save an execution path for a execution statement

latches dają exclusive access do obiektu
locks – concurrent access but only one can write at the time
simple objects (np. buffer cache)
compound object (table)

locks dla simple objects:
- exclusive – session needs to modify a simple object
- shared – session needs to view an object
- null – if session caches info about an object – null locki is placed on it – it acts like a trigger in case an object is invalidated

tylko dla compound
- sub shared – shared locks on some rows in a table (np w przypadku utworzenia kursora)
- sub exclusive – session need an exclusive lock on a part of an object
- shared sub-exclusive lock – shared lock on a whole table and an exclusive lock on a part of a table

dictionary views:
dba_locks – session id who is holding a lock, type of lock, what mod the lock is in, whether it is blocking another session for access
dba_dml_locks – data manipulation language locks
dba_ddl_locks – data dictionary language locks
dba_blockers – show which sessions are blocking other sessions
dba_waiters – show which sessions are waiting for resources

select * from dba_tables
tam jet init_trans i max_trans
buffer cache – tu trzymane bloki z transakcji

v$system_event
select event, total_waits, average_wait from v$system_event where wait_class != ‘Idle’;

V$session_event – describes events since the session has started up

V$session_wait – check what sid is of the user which is haveing problems

select event, seconds_in_wait, state
from v$session_waits
where sid=142;

select * from v$session;

blocking_session, blockin_instance
select row_wait_obj#, row_wait_block#, row_wait_row#, blocking_session from v$session where sid=142;

w dba_objects sprawdzam czego dotyczy ten object
select owner, object_name from dba_objects where object_id=51161;

select * from v$session

śledzenie sesji
alter session set events ’10046 trace name context forever, lever 8′;

wyłączenie śledzenia sesji
alter session set events ’10046 trace name context off’;

potem do userdump directory i sprawdzenie co się dzieje.
aby to działało to trzeba mieć time_statistics ustawione na true

show paramete timed_statistics;
db file sequential read – indexes
db file scattered read - full table scans
direct path read/write – extensive sort – not enough memory
buffer busy wait – you should tune your sql statements

select * from dba_ts_quotqs;
grant unlimited tablespace to scott; – przyznanie nieograniczonej przestrzeni tabel
sprawdzenie jakie przywileje ma Scott:
select privilege from dba_sys_privs where grantee=’SCOTT’;

select * from dba_profile;
DBA_STMT_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_AUDIT_SESSION

———————- NET SERVICES ———————-

sqlplus scott@host:1521/orcl
po ustawieniu zmiennej TNS_ADMIN – tam pliki net
listener.ora – konfiguracja listenera
tu można ustawić shared server zamiast dedicated server
sqlnet.ora – tu różne rzeczy – nameing, authentication
można dodać TCP_VALIDNODE_CHECKING = YES

TCP.INVITED_NODES = (, ,)
TCP.EXCLUDED_NODES = ( , , )
SQLNET.AUTHENTICATION_SERVICES = (NTS)
NTS – windows authentication
NAMES.TRACE_LEVEL = ADMIN

tnsnames.ora – używany tylko, jeżelu używa się metody local naeming

na kliencie:
netca
nameing methods
on modyfikuje sqlnet.ora i dodaje NAMES.DIRECTORY_PATH = (EZCONNECT, TNSNAMES)

aby zmodyfikować tns to: netca
a potel local net service name configuration
lsnrctl
status
set trc_level support
set log_directory c:\oratrc
set log_file mylog.log
seve_config

RAC – wiele instancji w jednej bazie
netca – konfiguracja net – graficzna
select * from all_services;
distributed query – zapytuje wiecej niż jedna baze danych
sqlplus / as sysdba

sql> create public database link orcl2
connect to scott identified by tiger
using ‘orcl2′ (tu nazwa na której nasłuchuje listener)

sql> select sysdate from dual@orcl2;
select * from dba_db_links;
col db_link format a20
col username format a15
col host format a12

w tnsnames.ora można zdefiniować w jakim modzie będzie pracował serwar shared or dedicated
defoult’owe pasło dla sys is “change_on_install”
delfoul’owe paseło dla system is “manager”

scott/tiger
hr/hr

aby uruchomić funkcje sprawdzające hasło:
@%oracle_home%\rdbms\admin\utlpwdmg

select text from dba_source;