28 lutego 2011
Z sieci … dzisaj muzycznie.
25 lutego 2011
Oracle management (K1)
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:
SQL Test Case Builder used for reproducing the problem and transmitting the information to Oracle Support. The advisors in this infrastructure include:
SQL Repair Advisor
If a SQL statement fails with a critical error, the SQL Repair Advisor will analyze the statement and recommend a patch to repair it.
Data Recovery Advisor
The Data Recovery Advisor is used in recovering from corrupted blocks, corrupted or missing files, and other data failures and is integrated with database health checks and RMAN.
Oracle Enterprise Manager
Database Management Packs – Diagnostics, Tuning, Change Management, Configuration Management,Provisioning
Database Diagnostics Pack - Provides automatic performance diagnostics by leveraging ADDM, the AWR, monitoring templates, and advanced event notification and alerting
Database Tuning Pack - Provides the statistics, SQL profiling, access path, and SQL structure analysis leveraged by the SQL Tuning Advisor and includes the SQL Access Advisor and Object Reorganization Wizard
Database Change Management Pack - Provides capture and version baselines, database object and data copying, and object definition updates
Database Configuration Management Pack - Provides system inventory collection and reporting, configuration comparisons and history, policy manager, and critical patch advisor
Database Provisioning Pack- Provides automated patching, cloning, provisioning, and single instance to RAC conversion
Standalone Management Packs- Provisioning, Service Level Management
Application Management Packs - E-Business Suite, PeopleSoft Enterprise, Siebel
Middleware (Oracle Application Server) Management Packs - Diagnostics, Configuration Management, Identity Management, Provisioning, SOA Management
Management Connectors - Microsoft Operations Manager, Remedy Helpdesk
Operating System Management Packs - Oracle Linux
System Monitoring Plug-ins - EMC Celerra, EMC Symmetrix DMX, NetApp Filer, BEA WebLogic, JBoss Application Server, IBM WebSphere, IBM WebSphere MQ, IBM DB2, Microsoft IIS Server, Microsoft Active Directory, Microsoft BizTalk Server, Microsoft Commerce Server, Microsoft ISA Server, Microsoft .NET framework, Microsoft SQL Server, Check Point Firewall, Juniper Netscreeen Firewall, F5 BigIP Local Traffic Manager, Linux Hosts, Unix Hosts, Windows Hosts
Oracle Management Agents - These agents monitor targets for health, status, and performance. Management Agents can also send Simple Network Management Protocol (SNMP) traps to database performance monitors in other system monitoring tools.
EM2Go is a mobile version of Enterprise Manager introduced with Oracle Database 10g.
In Oracle, a collection of contiguous blocks is referred to as an extent. A collection of extents is referred to as a segment.
As of Oracle Database 10g, resolving fragmentation issues became fairly trivial. You can perform an online segment shrink using the Segment Advisor interface accessible through EM. ADDM recommends segments to shrink, and you simply choose to accept the recommendations. (151)
Vmware ESXi 4.0 to 4.1 upgrade
rozpakować “upgrade-from-ESXi4.0-to-4.1.0-0.0.260247-release.zip”
skopiować rozpakowany katalog na datastore ESXa
Z poziomu serwera:
Alt + F1
wpisać: unsupported (nazwa mówi sama za siebie)
podać hasło root
# cd /vfs/volumes/datastore/upgrade/
# esxupdate update -m metadata.zip
wsio
Źródła:
http://www.amikkelsen.com/?p=478
http://blog.jargontech.com/how-to-preform-an-vsphere-esxi-4-0-to-4-1-upgrade/
http://www.bauer-power.net/2010/04/vmware-esxi-hack-to-allow-ssh.html
http://www.tangerinelogic.com/index.php/2010/07/18/upgrade-esxi-4-0-to-4-1/
24 lutego 2011
MsSQL 2008 R2 installation.
SQL Server supports multiple instances of SQL Server on a single server or processor, but only one instance can be the default instance. All others must be named instances. A computer can run multiple instances of SQL Server concurrently, and each instance runs independently of other instances.
Failover cluster instances only — Specify the SQL Server failover cluster network name. This name identifies the failover cluster instance on the network.
Default or Named instance — Consider the following information when you decide whether to install a default or named instance of SQL Server:
- If you plan to install a single instance of SQL Server on a database server, it should be a default instance.
- Use a named instance for situations where you plan to have multiple instances on the same computer. A server can host only one default instance.
- Any application that installs SQL Server Express should install it as a named instance. This will minimizes conflict when multiple applications are installed on the same computer.
On the Server Configuration — Service Accounts page, specify login accounts for SQL Server services. The actual services that are configured on this page depend on the features that you selected to install.
You can assign the same login account to all SQL Server services, or you can configure each service account individually. You can also specify whether services start automatically, are started manually, or are disabled. Microsoft recommends that you configure service accounts individually to provide least privileges for each service, where SQL Server services are granted the minimum permissions they have to have to complete their tasks.
Uprawnienia jakie należy nadać dla odpowiednich kont:
Zalecenia odnośnie uprawnień:
Źródła:
http://msdn.microsoft.com/en-us/library/ms143531.aspx
http://www.mytechmantra.com/LearnSQLServer/Install_SQL_Server_2008_R2_P3.html
http://technet.microsoft.com/en-us/library/ms143504.aspx
http://www.kajware.pl/program-hotelowy/instalacja-ms-sql.html
Oracle datastructures (K1)
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.
23 lutego 2011
ORA-02266 ORA-02297 ORA-02429 ORA-29857
ORA-02266: |
unique/primary keys in table referenced by enabled foreign keys |
Cause: | An attempt was made to truncate a table with unique or primary keys referenced by foreign keys enabled in another table. Other operations not allowed are dropping/truncating a partition of a partitioned table or an ALTER TABLE EXCHANGE PARTITION. |
Action: | Before performing the above operations the table, disable the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = “tabnam”; |
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;
/
ORA-02297: |
cannot disable constraint (string.string) – dependencies exist |
Cause: | an alter table disable constraint failed becuase the table has foriegn keys that are dpendent on this constraint. |
Action: | Either disable the foreign key constraints or use disable cascade |
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 theREF
column and the object it references.
ORA-02429: |
cannot drop index used for enforcement of unique/primary key |
Cause: | user attempted to drop an index that is being used as the enforcement mechanism for unique or primary key. |
Action: | drop the constraint instead of the index. |
Trzeba znaleźć w PKs dany constraint i sprawdzić do jakiej tabeli się odwołuje. (ewentualnie usunąć)
ORA-02273: |
this unique/primary key is referenced by some foreign keys |
Cause: | Self-evident. |
Action: | Remove all references to the key before the key is to be dropped. |
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)
ORA-29857: |
domain indexes and/or secondary objects exist in the tablespace |
Cause: | An attempt was made to drop a tablespace which contains secondary objects and/or domain indexes. |
Action: | Drop the domain indexes in his tablespace. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace. |
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
PAE – Physical Address Extension
PAE (Physical Address Extension) is a “workaround” for letting x86-32bit(!) OS see more than 4GB of RAM. 4GB is the limit for 32bit memory addresses. PAE is not needed and not implemented on x86-64 processors when 64 bit (“long mode“) is enabled.
How does it work?
In short, it simply adds 4 bits to the memory addresses (32bit -> 36bit) and one more level of memory-lookup-hierarchy, and: voila, OS can access up to 64GB ram (which is not “science fiction” these crazy days..). Of course, a single 32bit process is not aware, and would still only have a 4GB of virtual address space, even with PAE.
Performance penalty: yes or no, and how much?
PAE is hardware supported since Intel Pentium Pro (back in the mid 90s..). Reading “hardware supported” might mislead, and make one think that it’s all accelerated and there’s no performance penalty. But still, even in hardware, PAE adds one more hierarchy level for memory lookups, and my weak hardware knowledge tells that it might still slow something down…
The Linux kernel of most 32bit distributions (in particular RedHat 5) ships with PAEdisabled, while an optional PAE-enabled kernel is available. On 32bit Windows, PAE used to be disabled by default, but since WinXP SP2 windows is PAE-enabled by default. I’ve also seen some Linux distros enabling PAE in their default kernel recently.
Googling for “PAE performance affection” was not easy, and that’s the main reason I wrote this article: to spread the knowledge. Best articles I’ve found are specified at the bottom.
Research conclusions:
- Main reason for PAE being disabled by default, seems to be hardware compatibility: hardware with no PAE support can’t boot a PAE-enabled kernel. That’s mostly history now, anyway, for all recent x86 processors support PAE.
- Performance penalty is very low (according to RedHat average is 1% and no more than 10%). Of course it depends on your exact scenarios, etc etc.
- As a friend suggested me: in most cases PAE is not needed, for x86-64 is so widely spread. One can simply run his 32bit apps on a 64bit OS. PAE lets the OS see up to 64GB, while x86-64 (current implementations) lets the OS see at least 16TB! So my main conclusion is.. that PAE is dead. All modern x86 processors (since ~year 2006) have x86-64 support.
- Best thing is compiling your code as 64bit and running a 64bit OS, of course
Accessing beyond the 4GB on 32bit mode
According to the PAE article on Wikipedia, there are ways for accessing areas of RAM beyond the regular 4GB of virtual address space. Looks like Windows has a nice API for that: Address Windowing Extention, and Linux is able to do it with mmap() system call. I couldn’t figure out how, though.
Włączenie i wyłączenie:
Windows automatically enables PAE if DEP is enabled on a computer that supports hardware-enabled DEP, or if the computer is configured for hot-add memory devices in memory ranges beyond 4 GB. If the computer does not support hardware-enabled DEP or is not configured for hot-add memory devices in memory ranges beyond 4 GB, PAE must be explicitly enabled.
To explicitly enable PAE, use the following BCDEdit /set command to set the pae boot entry option:
bcdedit /set [{ID}] pae ForceEnable
IF DEP is enabled, PAE cannot be disabled. Use the following BCDEdit /set commands to disable both DEP and PAE:
bcdedit /set [{ID}] nx AlwaysOff
bcdedit /set [{ID}] pae ForceDisable
Windows Server 2003 and Windows XP/2000: To enable PAE, use the /PAE switch in the Boot.ini file. To disable PAE, use the /NOPAE switch. To disable DEP, use the /EXECUTE switch.
Check type of OS (64 or 32 bit)
- Click Start, then click on Run or Start Search.
- Type msinfo32.exe and then press Enter key.
- In “System Information”, review the value for the System Type item:
- For 32-bit editions of Windows, the value of the System Type item isx86-based PC.
- For 64-bit editions of Windows, the value of the System Type item isx64-based PC.
Źródła:
http://www.held.org.il/blog/2008/07/pae-whats-that-and-how-bad-for-performance/
http://msdn.microsoft.com/en-us/library/aa366796(v=vs.85).aspx
21 lutego 2011
ORA-02292 Constraint violation – child records found
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, lets 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 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/
20 lutego 2011
19 lutego 2011
18 lutego 2011
Oracle datastructures (K1)
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)
Linux, NFS
Na serwerze:
/etc/exports
/mnt/data/katalog 192.168.0.10(rw)
/etc/rc.d/init.d/nfs start
(lub ręczne uruchomienie demona)
aby zobaczyć co jest udostępnione: exportfs
Na kliencie:
rpc.portmap lub portmap
mount -t nfs nazwa_serwera:/mnt/data/katalog /lokalny/katalog
15 lutego 2011
JAVA w Oracle i kasowanie dużej ilości wierszy z tabeli, triggers.
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;
12 lutego 2011
9 lutego 2011
Silversmithing – podstawy
7 lutego 2011
IRAN
In 2011, Ramadan will start on Monday, August 1 and will continue for 30 days until Tuesday, August 30..
Tabriz
Senandaj
Shush
Sushtar
Choqa Zanbil
Kermanshah
Ahwaz
Shiraz
Marv Dasht
Persepolis
Naqsh-e Rustam
Pasargad
Isfahan/Esfahan
Yazd
Kharanaq
Nain
Abyaneh
Qum
Teheran
Rasht
Masuleh
Kandovan, East Azerbaijan, Iran
Tabriz
———————————--
Tabriz (nocleg)
jest dużym, raczej średnio atrakcyjnym miastem.
Shush
jest jednym z najstarszych miast świata, ma ponad 6 tysięcy lat, chociaż istnieją ślady ludzkiej działalności w tej okolicy nawet sprzed 9 tysięcy lat. Wzmianki o nim można znaleźć w Starym Testamencie. Współczesne Shush jest średniej wielkości, nieszczególnie interesującym miastem. Na miejscu warto zobaczyć zamek Króla Xerxesa, Mauzoleum Proroka Daniela oraz Pałac Achameński. Wiele osób przyjeżdża do Shush ze względu na pobliski (45 kilometrów) ziggurat Choqa Zanbil.
Shushtar
jest starożytnym miastem słynnym z licznych i efektownych konstrukcji wodnych, jak młyny, mosty, zapory, kanały irygacyjne oraz tunele.
Choqa Zanbil
to kompleks świątynny z 1250 roku p.n.e. poświęcony elmickiemu bogu Inshushinak. Jest najlepiej zachowanym Zigguratem (monumentalna budowla sakralna w formie tarasowo uformowanej wieży) na świecie i prawdopodobniej najstarszą budowlą, jaką można zobaczyć w Iranie.
Nałatwiej można się tu dostać z oddalonego o 45 kilometrów Shush.
Shiraz, (nocleg)
niegdyś stolica Persji, zwany jest miastem poezji, wina, róż i słowików. Dziś wina nie można pić w Iranie wcale, a po wielkich perskich poetach pozostały tylko wspaniałe ogrody, ale Shiraz pozostał ważnym centrum kultury i sztuki Iranu. Do odwiedzenia jest tu twierdza Karima Khana, grobowiec (i park) Hafeza, mauzoleum Shah-Cheragh, uniwersytet, kilka parków i meczetów. Zaledwie 60 km od miasta znajduje się Persepolis, do którego łatwo dotrzeć autobusem lub taksówką.
Persepolis, (1 dzień -- zwiedzanie) około 60 km od Shiraz
miasto założone w 518 p.n.e. było antyczną stolicą Imperium Aszemidów i było prawdopodobnie najwspanialszym miastem swoich czasów. Wspaniały kompleks pałacowy, niezwykłe grobowce oraz pozostałe budynki musiały kiedyś być wyjątkowo piękne, niestety do naszych czasów przetrwały jedynie ruiny. Dla miłośników archeologii i historii jest to z pewnością uczta dla oczu i ducha, ale pozostali muszą mocno posiłkować się wyobraźnią.
Naqsh-e Rustam (1 dzień zwiedzanie -- razem z Persepolis) (12 km od Persepolis)
Naqsh-e Rustam -- wykute w skalnym klifie olbrzymie grobowce czterech perskich królów oraz Naqsh-e Rajab -- miejsce ze skalnymi reliefami z około IV wieku naszej ery
Pasargad -- grobowiec (po drodze)
ISFAHAN/ESFAHAN (2 dni -- zwiedzanie)
Esfahan jest jednym z największych skarbów Iranu. To piękne miasto obfituje w turystyczne atrakcje, zaczynając od największego w Iranie Meczetu Piątkowego oraz jednego z największych na świecie placów, Placu Immama Khomeiniego, poprzez wspaniałe, zabytkowe mosty na rzece Zayandeh, do liczących setki lat ormiańskich kościołów. Dodając do tego bazary i parki otrzymujemy niezwykle interesujące miejsce.
Yazd (1 dzień -- zwiedzanie)
jest jednym z najstarszych miast świata i jednym z najpiękniejszych miast Iranu. Otoczony pustynią, posiada wyjątkową w swoim rodzaju starówkę zbudowaną w całości z gliniano-piaskowych cegieł. Miasto jest ostoją kultury zoroastriańskiej.
Kharanaq
jest niewielką, urokliwą wioską, pamiętającą jeszcze czasy przed dominacją Islamu, w większości opuszczona, zamieszkałą dziś przez zaledwie dwie rodziny. Wszystkie budynki wybudowane są z glinianych cegieł i otynkowane wysuszoną na słońcu mieszanką błota z sianem. W pobliżu, pół kilometra za wioską, znajduje się efektowny akwedukt przecinający koryto sezonowej rzeki.
Nain
jest niedużym miasteczkiem słynącym z najstarszego w Iranie meczetu oraz zbudowanej 2000 lat temu efektownej fortecy.
Abyaneh (po drodze)
jest jedną z najstarszych i jednocześnie najpopularniejszych wiosek w Iranie. Jej charakterystyczną cechą jest to, że wszystkie budynki pokryte są czerwoną gliną. Położona jest malowniczo na zboczu wzgórza, a zboczu przeciwległym znajdują się ruiny starej twierdzy. Wydaje się, że mieszkańcy wioski żyją tak, jak setki lat temu i zupełnie nie przejmują się przemykającymi wązkimi uliczkami turystami.
W Abyaneh nie ma zbyt wielu opcji noclegowych. Najwygodniej jest zatrzymać się w pobliskim Kashan, skąd wynajętą taksówką można się dostać do Abyaneh.
Qom (nocleg)
jest jednym z najświętszych miast Iranu i najważniejszym ośrodkiem teologicznym, tu urodził się i wychował sam Chomeini. Znajduje się tu niezliczona ilość meczetów i szkół koranicznych, a na ulicach pełno jest smutnych dżentelmenów w turbanach na głowie -- duchownych. Qom jest miejscem bardzo konserwatywnym i zdarza się, że jego mieszkańcy czasem krzywo patrzą na turystów z zepsutego zachodu, dlatego warto zwrócić szczególną uwagę na właściwy strój i zachowanie.
Masuleh
jest bardzo ładną wioską, wyjątkowo atrakcyjnie położoną, bo na stromym zboczu góry Talesh, ponad tysiąc metrów nad poziomem morza. Zbocze jest tak strome i wolnego miejsca jest tu tak mało, że dachy jednych budynków stanowią ulice bądź ogródki przed domami położonymy nieco wyżej. W wiosce nie ma samochodów, nie jest łatwo do niej się dostać, niewielu turystów tu dociera, dzięki czemu zostały zachowane oryginalny charakter i atmosfera tego miejsca.
Kandovan
jest niewielką wioską położoną w pobliżu Tabriz, jest ulubionym miejscem weekendowych pikników dla mieszkańców tego dużego miasta. Atrakcją Kandovan są niezwykłe, wydrążone w skałach domy, w których do dziś mieszkają ludzie, tak jak żyli w nich setki lat temu. To miejsce jest bardzo podobne do tureckiej Kapadocji. Sielska atmosfera, wszechobecne osiołki i piknikujący wokół Irańczycy sprawiają, że warto spędzić tu parę godzin. W Kandovan nie ma żadnych opcji noclegowych, najwygodniej jest zatrzymać się w pobliskim Tabriz.
3 lutego 2011
Instalacja Oracle 11G R2 na Solaris 10
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
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
2 lutego 2011
Apache, htaccess – dostęp za pomocą hasła i z zakresu numerów IP bez hasła.
Aby do danego katalogu serwera WWW przyznać dostęp np. dla użytkowników naszej lokalnej sieci bez hasła i dla użytkowników zewnętrznych za pomocą hasła należy w pliku httpd.conf (lub osobnym pliku .htaccess – jak kto woli) dodać wpis:
Aby dodać do naszego pliku .htpasswd loginy i hasła osób, które mogą się logować do naszego katalogu, uruchamiamy program do generowania haseł:
cd /usr/local/apache/bin
./htpasswd /usr/local/apache/htdocs/katalog/.htpasswd login_użytkownika
1 lutego 2011
Wierszyk który powiedział mi mój syn :-)
Na plaży leży
grupa młodzieży
wszyscy zalani
i bez odzieży.
Po stawie pływa
babcia nieżywa
tłuste robaczki
jedzą jej flaczki.