Difference between revisions of "Oracle"
Jump to navigation
Jump to search
| (6 intermediate revisions by the same user not shown) | |||
| Line 2: | Line 2: | ||
== Configuration == | == Configuration == | ||
| + | |||
| + | === Processes === | ||
* ORA-12516: TNS:listener could not find available handler with matching protocol stack | * ORA-12516: TNS:listener could not find available handler with matching protocol stack | ||
** Edit %oracle_home%/database/init<SID>.ora | ** Edit %oracle_home%/database/init<SID>.ora | ||
** Add | ** Add | ||
PROCESSES=200 | PROCESSES=200 | ||
| + | |||
| + | === Flashback === | ||
| + | In order to make use of something like | ||
| + | <syntaxhighlight lang="sql"> | ||
| + | FLASHBACK TABLE MYTABLE TO TIMESTAMP TO_TIMESTAMP('2010-12-31 23:59:59'); | ||
| + | </syntaxhighlight> | ||
| + | it is necessary to enable row movement. | ||
== FAQ == | == FAQ == | ||
| + | |||
| + | === How do I realize a certain field type? === | ||
| + | {| class="wikitable" | ||
| + | |+ align="bottom" | Oracle: Data Type Table Field Definitions | ||
| + | ! Type !! Oracle !! Comment | ||
| + | |- | ||
| + | | Boolean || NUMBER(1,0) || Values: 0, -1 | ||
| + | |- | ||
| + | | Integer || NUMBER(5,0) | ||
| + | |- | ||
| + | | Long Integer || NUMBER(11,0) | ||
| + | |- | ||
| + | | Text || VARCHAR2(255) || Access Text Field | ||
| + | |} | ||
=== How do I realize an auto-incrementing field? === | === How do I realize an auto-incrementing field? === | ||
| + | <syntaxhighlight lang="sql"> | ||
| + | CREATE TABLE MYTABLE ( | ||
| + | ID NUMBER(11, 0) NOT NULL | ||
| + | ); | ||
| + | |||
| + | CREATE SEQUENCE MYTABLE_ID_SEQ | ||
| + | START WITH 1 | ||
| + | INCREMENT BY 1 | ||
| + | NOMAXVALUE; | ||
| + | |||
| + | CREATE TRIGGER MYTABLE_ID_TRG | ||
| + | BEFORE INSERT ON MYTABLE | ||
| + | FOR EACH ROW | ||
| + | BEGIN | ||
| + | SELECT MYTABLE_ID_SEQ.NEXTVAL | ||
| + | INTO :NEW.ID | ||
| + | FROM DUAL; | ||
| + | END; | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | === How do I rollback to a previously specified point? === | ||
| + | <syntaxhighlight lang="sql"> | ||
| + | SAVEPOINT MYSAVEPOINT; | ||
| + | -- ... | ||
| + | ROLLBACK TO MYSAVEPOINT; | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | === How do I rollback to a certain time? === | ||
| + | <syntaxhighlight lang="sql"> | ||
| + | FLASHBACK TABLE MYTABLE TO TIMESTAMP TO_TIMESTAMP('2010-12-31 23:59:59'); | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | == Errors == | ||
| + | |||
| + | === ORA-00911 === | ||
| + | * Pass Through Queries must not have a semicolon at the end of the SQL statement. | ||
Latest revision as of 00:32, 18 June 2011
Configuration
Processes
- ORA-12516: TNS:listener could not find available handler with matching protocol stack
- Edit %oracle_home%/database/init<SID>.ora
- Add
PROCESSES=200
Flashback
In order to make use of something like
FLASHBACK TABLE MYTABLE TO TIMESTAMP TO_TIMESTAMP('2010-12-31 23:59:59');
it is necessary to enable row movement.
FAQ
How do I realize a certain field type?
| Type | Oracle | Comment |
|---|---|---|
| Boolean | NUMBER(1,0) | Values: 0, -1 |
| Integer | NUMBER(5,0) | |
| Long Integer | NUMBER(11,0) | |
| Text | VARCHAR2(255) | Access Text Field |
How do I realize an auto-incrementing field?
CREATE TABLE MYTABLE (
ID NUMBER(11, 0) NOT NULL
);
CREATE SEQUENCE MYTABLE_ID_SEQ
START WITH 1
INCREMENT BY 1
NOMAXVALUE;
CREATE TRIGGER MYTABLE_ID_TRG
BEFORE INSERT ON MYTABLE
FOR EACH ROW
BEGIN
SELECT MYTABLE_ID_SEQ.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;
How do I rollback to a previously specified point?
SAVEPOINT MYSAVEPOINT;
-- ...
ROLLBACK TO MYSAVEPOINT;
How do I rollback to a certain time?
FLASHBACK TABLE MYTABLE TO TIMESTAMP TO_TIMESTAMP('2010-12-31 23:59:59');
Errors
ORA-00911
- Pass Through Queries must not have a semicolon at the end of the SQL statement.