Bazy danych « Różności …

Różności …

14 listopada 2013

Oracle 11g installation on Centos 6.4

Zaszufladkowany do: Bazy danych,Linux,Oracle — Tagi: , — Jacek @ 09:55

http://www.tecmint.com/oracle-database-11g-release-2-installation-in-linux/

19 września 2013

MSSQL restore

Zaszufladkowany do: Bazy danych,MsSQL — Tagi: — Jacek @ 21:59

MSSQL backup

Zaszufladkowany do: Bazy danych,MsSQL — Tagi: — Jacek @ 16:28
use NPZDRpl;
GO
BACKUP DATABASE NPZDRpl
TO DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\npzdr_full_2_130919.bck’
WITH FORMAT,
NAME = ‘Full Backup of NPZDRpl’;
GO
http://www.mssqltips.com/sqlservertutorial/20/sql-server-backup-database-command/

17 września 2013

MSSQL eksport

Zaszufladkowany do: Bazy danych — Tagi: — Jacek @ 11:58

19 czerwca 2013

MySql konwersja ISO8859-2 do UTF-8

Zaszufladkowany do: Bazy danych,MySQL — Tagi: — Jacek @ 16:46

/usr/bin/mysqldump –user=root –password=haslo –max_allowed_packet=99M –default-character-set=latin1 -c –insert-ignore –skip-set-charset -r 130619_latin1_biblioteka.sql biblioteka

file nazwa_pliku_dumpa.sql (pokazuje w jakim jest kodowaniu)

iconv -f ISO8859-2 -t UTF-8  nazwa_pliku_dumpa.sql > nazwa_pliku_dumpa-utf-8.sql

drop database

CREATE DATABASE nazwa_bazy_danych CHARACTER SET utf8 COLLATE utf8_general_ci

mysql -uroot -ppassword --default-character-set=utf8 nazwa_bazy_danych < nazwa_pliku_dumpa-utf-8.sql

zmiana templateów:

for i in *.tpl; do iconv -f ISO8859-2 -t UTF-8 $i > $i”_utf8″; done

for i in *.tpl; do mv “$i” “$(basename “$i” .tpl).tpl_iso8859_2″; done

25 kwietnia 2013

Kolejność działań

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 07:50

24 listopada 2012

Oracle Enterprise Manager in Linux.

Zaszufladkowany do: Bazy danych,Linux,Oracle — Tagi: , — Jacek @ 22:15

emctl start dbconsole

emctl status agent

emctl stop agent

https://host:1158/em/console/aboutApplication

ORA-00845: MEMORY_TARGET not supported on this system

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 21:52

Problem Description
While creating a startup database using dbca the database creation GUI gives error message in a pop up window,
ORA-00845: MEMORY_TARGET not supported on this system
from where you can ignore the error message.
The similar scenario also occur whenever you try to start your database then startup shows error message like below.

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system

Cause of the Problem
•Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMMORY_MAX_TARGET.

•On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.

•And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.

•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.

•The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.

•And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.

•The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

Solution of the Problem
Make sure /dev/shm is properly mounted. You can see it by,
#df -h or #df -k command.
The output should be similar like

$ df -k
Filesystem            Size  Used Avail Use% Mounted on
...
shmfs                 1G    512M 512M  50% /dev/shm
We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.
As a root user,
# mount -t tmpfs shmfs -o size=13g /dev/shm
In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:

shmfs /dev/shm tmpfs size=13g 0

Źródła:
http://arjudba.blogspot.com/2009/01/ora-00845-memorytarget-not-supported-on.html

22 stycznia 2012

Prosty CURSOR

Zaszufladkowany do: Oracle,PL/SQL,Programowanie — Tagi: — Jacek @ 20:02
DECLARE
CURSOR C1 IS SELECT ST_ID_OPISOWY_KROTKI, TP_ID_OPISOWY_KROTKI
FROM STW.SRODKI_TRWALE;
BEGIN
FOR rekord1 IN C1 LOOP
Dbms_Output.put_line(rekord1.ST_ID_OPISOWY_KROTKI);
END LOOP;
END;
/

17 listopada 2011

SOA

Zaszufladkowany do: Bazy danych,Oracle,Oracle — Tagi: — Jacek @ 22:11

Including SOA Features in Jdeveloper 11g R1 (11.1.1.1.0)

By rodrigo.jorquera on Jul 09, 2009

The new Jdeveloper’s release doesn’t include the development suite for SOA applications.
There’s an extension for that and can be downloaded from OTN.

The link to download the file (called Oracle SOA Composite Editor):
http://www.oracle.com/technology/products/jdev/101/update/fmw_products.xml

To download the file directly:
http://download.oracle.com/otn/java/jdeveloper/111/extensions/soa-jdev-extension.zip

After download the file, you can install it using the Check for update option in Jdeveloper.

As you can see in the following screenshot, the option is available under the Help menu.

Then select the option “Install From Local File”, and select the location where you put the zip file.

After that you will see that the Oracle SOA Composite Editor will be installed and Jdeveloper will restart.

To verify the installed extension, You can go to File -> New section and you will see the new option “SOA Tier”.

If you chose that you will have the following templates as an option:
Then, you are ready to work in your SOA project!.

źródło:

http://blogs.oracle.com/rodrigo/entry/including_soa_features_in_jdev

15 listopada 2011

PL/SQL – functions

Zaszufladkowany do: Bazy danych,PL/SQL,Programowanie — Tagi: , — Jacek @ 19:44

PL/SQL Functions

What is a Function in PL/SQL?

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

The General Syntax to create a function is:

CREATE [OR REPLACE] FUNCTION function_name [parameters]

RETURN return_datatype;

IS

Declaration_section

BEGIN

Execution_section

Return return_variable;

EXCEPTION

exception section

Return return_variable;

END;

1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.

For example, let’s create a frunction called ”employer_details_func’ similar to the one created in stored proc

1> CREATE OR REPLACE FUNCTION employer_details_func

2>    RETURN VARCHAR(20);

3> IS

5>    emp_name VARCHAR(20);

6> BEGIN

7>      SELECT first_name INTO emp_name

8>      FROM emp_tbl WHERE empID = ’100′;

9>      RETURN emp_name;

10> END;

11> /

In the example we are retrieving the ‘first_name’ of employee with empID 100 to variable ‘emp_name’.
The return type of the function is VARCHAR which is declared in line no 2.
The function returns the ‘emp_name’ which is of type VARCHAR as the return value in line no 9.

How to execute a PL/SQL Function?

A function can be executed in the following ways.

1) Since a function returns a value we can assign it to a variable.

employee_name :=  employer_details_func;

If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning the return type of the function to it.

2) As a part of a SELECT statement

SELECT employer_details_func FROM dual;

3) In a PL/SQL Statements like,

dbms_output.put_line(employer_details_func);

This line displays the value returned by the function.

source:

http://plsql-tutorial.com/plsql-functions.htm

18 października 2011

SQL Loader – Oracle

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 13:04

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

1 września 2011

Oracle Database – auditing.

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 11:00

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.

SELECT * FROM dba_audit_trail ORDER BY TIMESTAMP DESC;

Źródła:

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

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

18 maja 2011

ORA-04030

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 17:01

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.

22 kwietnia 2011

(K1)

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 12:13

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;

The substitution occurs duringthe phase of processing known as the bind phase, which follows the parse phase andoptimize phase.

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)

20 kwietnia 2011

(K1)

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 10:52

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.

Transaction routing. The logic in the middle tier can direct transactions to specific database servers,increasing scalability.

dbverify

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 08:37

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;

5 kwietnia 2011

Oracle ApEx – adding a link

Zaszufladkowany do: ApEx,Oracle,Programowanie — Tagi: — Jacek @ 07:29

Adding a link.

4 kwietnia 2011

Dafault value (alter table – oracle)

Zaszufladkowany do: Bazy danych,Oracle,PL/SQL — Tagi: — Jacek @ 17:56

alter table PROBY modify (“WYDANO” FLOAT(126) DEFAULT 0)

1 kwietnia 2011

ApEx – primary key

Zaszufladkowany do: ApEx,Bazy danych,Oracle — Tagi: , — Jacek @ 11:47

CREATE table “PROBY” (
“ID”                   NUMBER(7) NOT NULL,
“DATA”                 DATE,
“TEMAT”                VARCHAR2(250),
“FAKTURA”              VARCHAR2(200),
“MASA_PROBY”           NUMBER(7),
“MASA_BADANIA”         NUMBER(7),
“MASA_PO_BADANIACH”    NUMBER(7),
“W_ZAMRAZARCE”         NUMBER(7),
“DLA_PRACOWNIKOW”      NUMBER(7),
“WARTOSC”              NUMBER(7),
“WARTOSC_PO_BADANIACH” NUMBER(7),
“ODPADY”               NUMBER(7),
“ODPOWIEDZIALNY”       VARCHAR2(150),
constraint  ”PROBY_PK” primary key (“ID”)
)
/
CREATE sequence “PROBY_SEQ”
/
CREATE trigger “BI_PROBY”
before insert on “PROBY”
for each row
begin
if :NEW.”ID” is null then
select “PROBY_SEQ”.nextval into :NEW.”ID” from dual;
end if;
end;
/
alter table “PROBY” add
constraint “PROBY_UK1″
unique (“ID”)
/

Aby zmienić home page:

Go to shared component.
Click on Edit Security Attribute under Secuirty
In Home Link text box… you will find something smilar to f?p=&APP_ID.:1:&SESSION.

After &APP_ID. you see a number , that number denotes home page.. in the above example 1 denotes the home page change 1 to 4 if you want page 4 to be ur home page


« Nowsze wpisyStarsze wpisy »

Strona startowa: www.jaceksen.pl