레이블이 DB인 게시물을 표시합니다. 모든 게시물 표시
레이블이 DB인 게시물을 표시합니다. 모든 게시물 표시

2010년 4월 10일 토요일

콘솔창에서 oracle http port 변경

콘솔창에서 oracle http port 변경

1. 시작-실행-cmd
 =>sqlplus
2. 사용자명 입력:system
   암호:XXX
3. exec dbms_xdb.sethttpport(8088);

작성자: 시간: 댓글 없음:
라벨: ,

log4sql 사용

log4sql.jar
commons-lang.jar

 

출처: http://realcool.egloos.com/6061439 

http://log4sql.sourceforge.net/index_kr.html

사용법
1.다운받은 zip파일을 압축을 풀고 log4sql.jar를 클래스 패스에 넣습니다.
2.commons-lang.jar파일을 다운받아서 클래스 패스에 넣습니다.
3.드라이버명을 다음과 같이 바꿉니다.

JDBC TYPE Origin Your Driver Class -> log4sql Driver Class
[ORACLE DRIVER CLASS] 'oracle.jdbc.drirver.OracleDriver' -> 'core.log.jdbc.driver.OracleDriver'
[MYSQL DRIVER CLASS] 'com.mysql.jdbc.Driver' or'org.gjt.mm.mysql.Driver' -> 'core.log.jdbc.driver.MysqlDriver'
[SYBASE DRIVER CLASS] 'com.sybase.jdbc2.jdbc.SybDriver' -> 'core.log.jdbc.driver.SybaseDriver'
[DB2 DRIVER CLASS] 'com.ibm.db2.jcc.DB2Driver' -> 'core.log.jdbc.driver.DB2Driver'
[INFOMIX DRIVER CLASS] 'com.informix.jdbc.IfxDriver' -> 'core.log.jdbc.driver.InfomixDriver'
[POSTGRESQL DRIVER CLASS] 'org.postgresql.Driver' -> 'core.log.jdbc.driver.PostgresqlDriver'
[MAXDB DRIVER CLASS] 'com.sap.dbtech.jdbc.DriverSapDB' -> 'core.log.jdbc.driver.MaxDBDriver'
[FRONTBASE DRIVER CLASS] 'com.frontbase.jdbc.FBJDriver' -> 'core.log.jdbc.driver.FrontBaseDriver'
[HSQL DRIVER CLASS] 'org.hsqldb.jdbcDriver' -> 'core.log.jdbc.driver.HSQLDriver'
[POINTBASE DRIVER CLASS] 'com.pointbase.jdbc.jdbcUniversalDriver' -> 'core.log.jdbc.driver.PointBaseDriver'
[MIMER DRIVER CLASS] 'com.mimer.jdbc.Driver' -> 'core.log.jdbc.driver.MimerDriver'
[PERVASIVE DRIVER CLASS] 'com.pervasive.jdbc.v2.Driver' -> 'core.log.jdbc.driver.PervasiveDriver'
[DAFFODILDB DRIVER CLASS] 'in.co.daffodil.db.jdbc.DaffodilDBDriver' -> 'core.log.jdbc.driver.DaffodiLDBDriver'
[JDATASTORE DRIVER CLASS] 'com.borland.datastore.jdbc.DataStoreDriver' -> 'core.log.jdbc.driver.JdataStoreDriver'
[CACHE DRIVER CLASS] 'com.intersys.jdbc.CacheDriver' -> 'core.log.jdbc.driver.CacheDriver'
[DERBY DRIVER CLASS] 'org.apache.derby.jdbc.ClientDriver' -> 'core.log.jdbc.driver.DerbyDriver'
[ALTIBASE DRIVER CLASS] 'Altibase.jdbc.driver.AltibaseDriver' -> 'core.log.jdbc.driver.AltibaseDriver'
[MCKOI DRIVER CLASS] 'com.mckoi.JDBCDriver' -> 'core.log.jdbc.driver.MckoiDriver'
[JSQL DRIVER CLASS] 'com.jnetdirect.jsql.JSQLDriver' -> 'core.log.jdbc.driver.JsqlDriver'
[JTURBO DRIVER CLASS] 'com.newatlanta.jturbo.driver.Driver' -> 'core.log.jdbc.driver.JturboDriver'
[JTDS DRIVER CLASS] 'net.sourceforge.jtds.jdbc.Driver' -> 'core.log.jdbc.driver.JTdsDriver'
[INTERCLIENT DRIVER CLASS] 'interbase.interclient.Driver' -> 'core.log.jdbc.driver.InterClientDriver'
[PURE JAVA DRIVER CLASS] 'org.firebirdsql.jdbc.FBDriver' -> 'core.log.jdbc.driver.PureJavaDriver'
[JDBC-ODBC DRIVER CLASS] 'sun.jdbc.odbc.JdbcOdbcDriver' -> 'core.log.jdbc.driver.JdbcOdbcDriver'
[MSSQL 2000 DRIVER CLASS] 'com.microsoft.jdbc.sqlserver.SQLServerDriver' -> 'core.log.jdbc.driver.MssqlDriver'
[MSSQL 2005 DRIVER CLASS] 'com.microsoft.sqlserver.jdbc.SQLServerDriver' -> 'core.log.jdbc.driver.Mssql2005Driver'

콘솔에서 확인해보면 쿼리가 출력되고 실행시간도 출력이 됩니다.

작성자: 시간: 댓글 없음:
라벨:

오라클 분석함수(ratio_to_report)를 사용하여 비율 구하기 (퍼센트)

http://blog.naver.com/tyboss?Redirect=Log&logNo=70043043176


[출처] 오라클 분석함수(ratio_to_report)를 사용하여 비율 구하기 (퍼센트)|작성자 마루아라

RATIO_TO_REPORT


문법

ratio_to_report::=

그림 설명


참 조 :

"Analytic Functions " for information on syntax, semantics, and restrictions, including valid forms of expr


목적

RATIO_TO_REPORT함수는 분석 함수이다. 이 함수는 값의 세트의 합에 대한 값의 비율을 계산한다. 만약 expr이 NULL이라면, ratio-to-report값은 NULL이다.

값의 집합은 query_partition_clause에 의해서 정해진다. 만약 이 구문을 생략한다면, ratio-to-report는 쿼리에 의해 반환되는 모든 열에 의해 계산된다.

expr에 대해서는 RATIO_TO_REPORT 또는 임의의 다른 분석 함수를 이용할수 없다. 중첩 분석 함수는 이용할수 없으나, 다른 내장 함수를 이용할수 있다.

참고

http://www.elancer.co.kr/eTimes/page/eTimes_view.html?str=c2VsdW5vPTYxMg==


예제

다음 예제는 모든 사무원의 급여의 합계에 대한 각 사무원의 급여의 비율 값을 계산한다.

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
FROM employees
WHERE job_id = 'PU_CLERK';

LAST_NAME                     SALARY         RR
------------------------- ---------- ----------
Khoo                            3100 .223021583
Baida                           2900 .208633094
Tobias                          2800 .201438849
Himuro                          2600  .18705036
Colmenares                      2500 .179856115
 
 
테이블 table_123에서 점이 1100인 것의 
select mngbr, cifno, jikwonno, janamt
, row_number() over(partition by jikwonno order by mngbr, cifno ) accum -- 직원별 순차번호
, sum(janamt) over(partition by jikwonno order by mngbr, cifno rows between unbounded preceding and current row) accum1 
-- 점,직원별/ 고객번호순 잔액의 누적
, sum(janamt) over(partition by jikwonno) accum_tot -- 점,직원별 잔액의 합계
, round(ratio_to_report(janamt) over(partition by jikwonno) * 100, 2) ratio_tot -- 점,직원별 잔액이 차지하는 비율
from table_123
where mngbr = 1100
 
 
 
  References

  1. Oracle Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02
  - http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions124.htm
 
  2. 출처
  - http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1030&seq=352&page=2&position=2
 
 
==========================================================================================================================
==========================================================================================================================
 
http://www.oracleclub.com/article/44234
 
테이블에  금액/갯수 해서 저장하는 금액 컬럼 과 금액/갯수해서 나온 각각의 비율을 저장하는 컬럼이 있씁니다 
금액 컬럼은 정수만 들어가고요..
비율을 소수점 2째자리까지 들어 갑니다
금액은 금액/갯수 해서 소숫점이 나오면 .. 소수점을 제거 하고 
정수만 모두 더한후 원래의 금액이 될대까지 1원씩 루프를 돌면서 더합니다
밑에 처럼요...
select 90922255/3 from dual  -- 30307418.3333333 
select 30307418 + 30307418 + 30307418 from dual  -- 90922254 
select 30307419 + 30307418 + 30307418 from dual  -- 90922255  -- 100 으로 맟추기 위해서 1번째 것에 1을 더함

그럼 금액은 맞아지는대요...

위에서 구한 금액에 맞게 각각의 비율을 구해야 하는대... 비율을 소수점 2째자리까지 계산할수 있씁니다.
이런건 밑에 2가지 경우중 어떤게 맞는 건가요?


1번째 :

select  ((90922255/3)/90922255)*100 from dual  -- 33.3333333333333

select 33.33 + 33.33 + 33.33 from dual  -- 99.99

select 33.34 + 33.33 + 33.33 from dual  -- 100 으로 맟추기 위해서 1번째 것에 1을 더함

2번째 :

select  ((90922255/3)/90922255)*100 from dual  -- 33.3333333333333

select 33 + 33 + 33 from dual  -- 99 

select 34 + 33 + 33 from dual   -- 100 으로 맟추기 위해서 1번째 것에 1을 더함
 
-------------------------------------------------------------------------------------------------------------------------
 
SELECT a, b 
, LEVEL lv 
, TRUNC(a/b) + CASE WHEN LEVEL <= a-TRUNC(a/b)*b THEN 1 ELSE 0 END v1 
, TRUNC(100/b) + CASE WHEN LEVEL <= 100-TRUNC(100/b)*b THEN 1 ELSE 0 END v2 
, TRUNC(100/b,2) + CASE WHEN LEVEL*0.01 <= 100-TRUNC(100/b,2)*b THEN 0.01 ELSE 0 END v3 
FROM (SELECT 90922255 a, 3 b FROM dual) 
CONNECT BY LEVEL <= b
작성자: 시간: 댓글 없음:
라벨: ,

오라클 누적 데이터 select

<원본 데이터 >

MSG_ID BASE_AMT
====================

205117 1400
423266 1000
423267 1000
423268 1000
891138 1400
891139 1000

<누적 합계>

MSG_ID BASE_AMT 누적합계

===========================

205117 1400 1400
423266 1000 2400
423267 1000 3400
423268 1000 4400
891138 1400 5800
891139 1000 6800

위와 같은 결과를 얻고 싶을 때, 다음과 같은 방법으로 select한다.

 

   select  msg_id,  base_amt,
   sum(base_amt) over(order by msg_id rows between unbounded preceding and current row) 
   from cdr_data
   where   subscr_no = 228377;

[출처] 오라클 누적 데이터 select|작성자 조조조

작성자: 시간: 댓글 없음:
라벨: ,

2009년 8월 7일 금요일

오라클 sys, system암호 까먹었을때

오라클 sys, system암호 까먹었을때
명령 프롬프트에서 다음을 실행합니다.
 
C:>sqlplus "/as sysdba"
SQL> show user
USER is "SYS"
 
암호를 원하는 대로 설정합니다.
 
SQL> alter user sys identified by 암호;
SQL> alter user system identified by 암호;
 
접속
 
SQL> connect sys/암호 as sysdba
SQL> connect systemp/암호  

[출처] [오라클계정]sys&system계정 암호 모를 때|작성자 ichonci

작성자: 시간: 댓글 없음:
라벨: ,

오라클과 NLS

오라클과 NLS

  • KO16KSC5601
  • KO16MSWIN949
  • UTF8
  • AL32UTF8

http://www.oracle.com/technology/global/kr/pub/columns/oracle_nls_1.html

 

 

select * from NLS_DATABASE_PARAMETERS where PARAMETER='NLS_CHARACTERSET'

 

set NLS_LANG=KOREAN_KOREA.AL32UTF8

다국어 처리를 위해

update sys.props$ set value$='AL32UTF8' where name='NLS_CHARACTERSET';
update sys.props$ set value$='AL32UTF8' where name='NLS_NCHAR_CHARACTERSET';

작성자: 시간: 댓글 없음:
라벨: ,

2009년 7월 24일 금요일

오라클 - 에러 메세지 [ORA-02201 to ORA-02300]

ORA-02201 sequence not allowed here

Cause: An attempt was made to reference a sequence in a from-list.

Action: A sequence can only be referenced in a select-list.


ORA-02202 no more tables permitted in this cluster

Cause: An attempt was made to create a table in a cluster which already contains 32 tables.

Action: Up to 32 tables may be stored per cluster.


ORA-02203 INITIAL storage options not allowed

Cause: An attempt was made to alter the INITIAL storage option of a table, cluster, index, or rollback segment. These options may only be specified when the object is created.

Action: Remove these options and retry the statement.


ORA-02204 ALTER, INDEX and EXECUTE not allowed for views

Cause: An attempt was made to grant or revoke an invalid privilege on a view.

Action: Do not attempt to grant or revoke any of ALTER, INDEX, or EXECUTE privileges on views.


ORA-02205 only SELECT and ALTER privileges are valid for sequences

Cause: An attempt was made to grant or revoke an invalid privilege on a sequence.

Action: Do not attempt to grant or revoke DELETE, INDEX, INSERT, UPDATE, REFERENCES or EXECUTE privilege on sequences.


ORA-02206 duplicate INITRANS option specification

Cause: INITRANS is specified more than once.

Action: Specify INITRANS at most once.


ORA-02207 invalid INITRANS option value

Cause: The INITRANS value is not an integer between 1 and 255 and less than or equal to the MAXTRANS value.

Action: Choose a valid INITRANS value.


ORA-02208 duplicate MAXTRANS option specification

Cause: MAXTRANS is specified more than once.

Action: Specify MAXTRANS at most once.


ORA-02209 invalid MAXTRANS option value

Cause: The MAXTRANS value is not an integer between 1 and 255 and greater than or equal to the INITRANS value.

Action: Choose a valid MAXTRANS value.


ORA-02210 no options specified for ALTER TABLE

Cause: No ALTER TABLE option was specified.

Action: Specify at least one alter table option.


ORA-02211 invalid value for PCTFREE or PCTUSED

Cause: The specified value for PCTFREE or PCTUSED is not an integer between 0 and 100.

Action: Choose an appropriate value for the option.


ORA-02212 duplicate PCTFREE option specification

Cause: PCTFREE option specified more than once.

Action: Specify PCTFREE at most once.


ORA-02213 duplicate PCTUSED option specification

Cause: PCTUSED option specified more than once.

Action: Specify PCTUSED at most once.


ORA-02214 duplicate BACKUP option specification

Cause: The BACKUP option to ALTER TABLE is specified more than once.

Action: Specify the option at most once.


ORA-02215 duplicate tablespace name clause

Cause: There is more than one TABLESPACE clause in the CREATE TABLE, CREATE INDEX, or CREATE ROLLBACK SEGMENT statement.

Action: Specify at most one TABLESPACE clause.


ORA-02216 tablespace name expected

Cause: A tablespace name is not present where required by the syntax for one of the following statements: CREATE/DROP TABLESPACE, CREATE TABLE, CREATE INDEX, or CREATE ROLLBACK SEGMENT.

Action: Specify a tablespace name where required by the syntax.


ORA-02217 duplicate storage option specification

Cause: A storage option (INIITAL, NEXT, MINEXTENTS, MAXEXTENTS, PCTINCREASE) is specified more than once.

Action: Specify all storage options at most once.


ORA-02218 invalid INITIAL storage option value

Cause: The specified value must be an integer.

Action: Choose an appropriate integer value.


ORA-02219 invalid NEXT storage option value

Cause: The specified value must be an integer.

Action: Choose an appropriate integer value.


ORA-02220 invalid MINEXTENTS storage option value

Cause: The specified value must be a positive integer less than or equal to MAXEXTENTS.

Action: Specify an appropriate value.


ORA-02221 invalid MAXEXTENTS storage option value

Cause: The specified value must be a positive integer greater than or equal to MINEXTENTS.

Action: Specify an appropriate value.


ORA-02222 invalid PCTINCREASE storage option value

Cause: The specified value must be a positive integer.

Action: Specify an appropriate value.


ORA-02223 invalid OPTIMAL storage option value

Cause: The specified value must be an integer.

Action: Choose an appropriate integer value.


ORA-02224 EXECUTE privilege not allowed for tables

Cause: An attempt was made to grant or revoke an invalid privilege on a table.

Action: Do not attempt to grant or revoke EXECUTE privilege on tables.


ORA-02225 only EXECUTE and DEBUG privileges are valid for procedures

Cause: An attempt was made to grant or revoke an invalid privilege on a procedure, function or package.

Action: Do not attempt to grant or revoke any privilege besides EXECUTE or DEBUG on procedures, functions or packages.


ORA-02226 invalid MAXEXTENTS value (max allowed: string)

Cause: The MAXEXTENTS specified is too large for the database block size. This applies only to SYSTEM rollback segment.

Action: Specify a smaller value.


ORA-02227 invalid cluster name

Cause: A cluster name was not properly formed.

Action: Check the rules for forming object names and enter an appropriate cluster name.


ORA-02228 duplicate SIZE specification

Cause: The SIZE option is specified more than once.

Action: Specify the SIZE option at most once.


ORA-02229 invalid SIZE option value

Cause: The specified value must be an integer number of bytes.

Action: Specify an appropriate value.


ORA-02230 invalid ALTER CLUSTER option

Cause: An option other than PCTFREE, PCTUSED, INITRANS, MAXTRANS, STORAGE, or SIZE is specified in an ALTER CLUSTER statement.

Action: Specify only legal options.


ORA-02231 missing or invalid option to ALTER DATABASE

Cause: An option other than ADD, DROP, RENAME, ARCHIVELOG, NOARCHIVELOG, MOUNT, DISMOUNT, OPEN, or CLOSE is specified in the statement.

Action: Specify only legal options.


ORA-02232 invalid MOUNT mode

Cause: A mode other than SHARED or EXCLUSIVE follows the MOUNT keyword in an ALTER DATABASE statement.

Action: Specify either SHARED, EXCLUSIVE, or nothing following MOUNT.


ORA-02233 invalid CLOSE mode

Cause: A mode other than NORMAL or IMMEDIATE follows the CLOSE keyword in an ALTER DATABASE statement.

Action: Specify either NORMAL, IMMEDIATE, or nothing following CLOSE.


ORA-02234 changes to this table are already logged

Cause: The log table to be added is a duplicate of another.

Action: Do not add this change log to the system; check that the replication product's system tables are consistent.


ORA-02235 this table logs changes to another table already

Cause: The table to be altered is already a change log for another table.

Action: Do not log changes to the specified base table to this table; check that the replication product's system tables are consistent.


ORA-02236 invalid file name

Cause: A character string literal was not used in the file name list of a LOGFILE, DATAFILE, or RENAME clause.

Action: Use correct syntax.


ORA-02237 invalid file size

Cause: A non-integer value was specified in the SIZE or RESIZE clause.

Action: Use correct syntax.


ORA-02238 filename lists have different numbers of files

Cause: In a RENAME clause in ALTER DATABASE or TABLESPACE, the number of existing file names does not equal the number of new file names.

Action: Make sure there is a new file name to correspond to each existing file name.


ORA-02239 there are objects which reference this sequence

Cause: The sequence to be dropped is still referenced by other objects.

Action: Make sure the sequence name is correct or drop the constraint or object that references the sequence.


ORA-02240 invalid value for OBJNO or TABNO

Cause: A number does not follow either OBJNO or TABNO.

Action: Specify a number after OBJNO or TABNO.


ORA-02241 must of form EXTENTS (FILE n BLOCK n SIZE n, ...)

Cause: There was an error in the extent storage clause.

Action: Respecify the storage clause using the correct syntax and retry the command.


ORA-02242 no options specified for ALTER INDEX

Cause: No options specified.

Action: Specify at least one of REBUILD, INITRANS, MAXTRANS, or STORAGE.


ORA-02243 invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

Cause: An option other than INITRANS, MAXTRANS, or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.

Action: Specify only legal options.


ORA-02244 invalid ALTER ROLLBACK SEGMENT option

Cause: The STORAGE option is expected but not found.

Action: Specify the STORAGE option.


ORA-02245 invalid ROLLBACK SEGMENT name

Cause: An identifier was expected, but not found, following ALTER [PUBLIC] ROLLBACK SEGMENT.

Action: Place a rollback segment name following SEGMENT.


ORA-02246 missing EVENTS text

Cause: A character string literal was expected, but not found, following ALTER SESSION SET EVENTS.

Action: Place the string literal containing the events text after EVENTS.


ORA-02247 no option specified for ALTER SESSION

Cause: The option SET EVENTS was expected, but not found, following ALTER SESSION.

Action: Place the SET EVENTS option after ALTER SESSION.


ORA-02248 invalid option for ALTER SESSION

Cause: An option other than SET EVENTS was found following the ALTER SESSION command.

Action: Specify the SET EVENTS option after the ALTER SESSION command and try again.


ORA-02249 missing or invalid value for MAXLOGMEMBERS

Cause: A valid number does not follow MAXLOGMEMBERS. The value specified must be between 1 and the port-specific maximum number of log file members.

Action: Specify a valid number after MAXLOGMEMBERS.


ORA-02250 missing or invalid constraint name

Cause: The constraint name is missing or invalid.

Action: Specify a valid identifier name for the constraint name.


ORA-02251 subquery not allowed here

Cause: Subquery is not allowed here in the statement.

Action: Remove the subquery from the statement.


ORA-02252 check constraint condition not properly ended

Cause: The specified search condition for the check constraint is not properly ended.

Action: End the condition properly.


ORA-02253 constraint specification not allowed here

Cause: Constraint specification is not allowed here in the statement.

Action: Remove the constraint specification from the statement.


ORA-02254 DEFAULT expression not allowed here

Cause: Default value expression is not allowed for the column here in the statement.

Action: Remove the default value expression from the statement.


ORA-02255: NOT NULL not allowed after DEFAULT NULL

Cause: A NOT NULL specification conflicts with the NULL default value.

Action: Remove either the NOT NULL or the DEFAULT NULL specification and try again.


ORA-02256 number of referencing columns must match referenced columns

Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list.

Action: Make sure that the referencing columns match the referenced columns.


ORA-02257 maximum number of columns exceeded

Cause: The number of columns in the key list exceeds the maximum number.

Action: Reduce the number columns in the list.


ORA-02258 duplicate or conflicting NULL and/or NOT NULL specifications

Cause: A duplicate or conflicting NULL and/or NOT NULL was specified.

Action: Remove one of the conflicting specifications and try again.


ORA-02259 duplicate UNIQUE/PRIMARY KEY specifications

Cause: A duplicate unique or primary key was specified.

Action: Remove the duplicate specification and try again.


ORA-02260 table can have only one primary key

Cause: Two or more primary keys were specified for the same table.

Action: Remove the extra primary keys and try again.


ORA-02261 such unique or primary key already exists in the table

Cause: A unique or primary key was specified that already exists for the table.

Action: Remove the extra key and try again.


ORA-02262 ORA-string occurs while type-checking column default value expressionexpression

Cause: New column datatype causes type-checking error for existing column default value expression.

Action: Remove the default value expression or do not alter the column datatype.


ORA-02263 need to specify the datatype for this column

Cause: The required datatype for the column is missing.

Action: Specify the required datatype.


ORA-02264 name already used by an existing constraint

Cause: The specified constraint name has to be unique.

Action: Specify a unique constraint name for the constraint.


ORA-02265 cannot derive the datatype of the referencing column

Cause: The datatype of the referenced column is not defined as yet.

Action: Make sure that the datatype of the referenced column is defined before referencing it.


ORA-02266 unique/primary keys in table referenced by enabled foreign keys

Cause: An attempt was made to drop or truncate a table with unique or primary keys referenced by foreign keys enabled in another table.

Action: Before dropping or truncating 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 constraint_name, table_name, status from user_constraints where r_constraint_name in ( select constraint_name from user_constraints where table_name ='tabnam');

ORA-02267 column type incompatible with referenced column type

Cause: The datatype of the referencing column is incompatible with the datatype of the referenced column.

Action: Select a compatible datatype for the referencing column.


ORA-02268 referenced table does not have a primary key

Cause: The referenced table does not have a primary key.

Action: Specify explicitly the referenced table unique key.


ORA-02269 key column cannot be of LONG datatype

Cause: An attempt was made to define a key column of datatype LONG. This is not allowed.

Action: Change the datatype of the column or remove the LONG column from the key, and try again.


ORA-02270 no matching unique or primary key for this column-list

Cause: An attempt was made to reference a unique or primary key in a table with a CREATE or ALTER TABLE statement when no such key exists in the referenced table.

Action: Add the unique or primary key to the table or find the correct names of the columns with the primary or unique key, and try again.


ORA-02271 table does not have such constraint

Cause: An attempt was made to reference a table using a constraint that does not exist.

Action: Check the spelling of the constraint name or add the constraint to the table, and try again.


ORA-02272 constrained column cannot be of LONG datatype

Cause: A constrained column cannot be defined as datatype LONG. This is not allowed.

Action: Change the datatype of the column or remove the constraint on the column, and try again.


ORA-02273 this unique/primary key is referenced by some foreign keys

Cause: A unique or primary key referenced by foreign keys cannot be dropped.

Action: Remove all references to the key before dropping it.


ORA-02274 duplicate referential constraint specifications

Cause: A referential constraint was specified more than once. This is not allowed.

Action: Remove the duplicate specification.


ORA-02275 such a referential constraint already exists in the table

Cause: An attempt was made to specify a referential constraint that already exists. This would result in duplicate specifications and so is not allowed.

Action: Be sure to specify a constraint only once.


ORA-02276 default value type incompatible with column type

Cause: The type of the evaluated default expression is incompatible with the datatype of the column.

Action: Change the type of the column, or modify the default expression.


ORA-02277 invalid sequence name

Cause: The specified sequence name is not a valid identifier name.

Action: Specify a valid identifier name for the sequence name.


ORA-02278 duplicate or conflicting MAXVALUE/NOMAXVALUE specifications

Cause: Duplicate or conflicting MAXVALUE and/or NOMAXVALUE specifications.

Action: Remove one of the conflicting specifications and try again.


ORA-02279 duplicate or conflicting MINVALUE/NOMINVALUE specifications

Cause: Duplicate or conflicting MINVALUE and/or NOMINVALUE clauses were specified.

Action: Remove one of the conflicting specifications and try again.


ORA-02280 duplicate or conflicting CYCLE/NOCYCLE specifications

Cause: Duplicate or conflicting CYCLE and/or NOCYCLE clauses were specified.

Action: Remove one of the conflicting specifications and try again.


ORA-02281 duplicate or conflicting CACHE/NOCACHE specifications

Cause: Duplicate or conflicting CACHE and/or NOCACHE clauses were specified.

Action: Remove one of the conflicting specifications and try again.


ORA-02282 duplicate or conflicting ORDER/NOORDER specifications

Cause: Duplicate or conflicting ORDER and/or NOORDER clauses were specified.

Action: Remove one of the conflicting specifications and try again.


ORA-02283 cannot alter starting sequence number

Cause: An attempt was made to alter a starting sequence number. This is not allowed.

Action: Do not try to alter a starting sequence number.


ORA-02284 duplicate INCREMENT BY specifications

Cause: A duplicate INCREMENT BY clause was specified.

Action: Remove the duplicate specification and try again.


ORA-02285 duplicate START WITH specifications

Cause: A duplicate START WITH clause was specified.

Action: Remove the duplicate specification and try again.


ORA-02286 no options specified for ALTER SEQUENCE

Cause: No ALTER SEQUENCE option was specified.

Action: Check the syntax. Then specify at least one ALTER SEQUENCE option.


ORA-02287 sequence number not allowed here

Cause: The specified sequence number reference, CURRVAL or NEXTVAL, is inappropriate at this point in the statement.

Action: Check the syntax. Then remove or relocate the sequence number.


ORA-02288 invalid OPEN mode

Cause: A mode other than RESETLOGS was specified in an ALTER DATABASE OPEN statement. RESETLOGS is the only valid OPEN mode.

Action: Remove the invalid mode from the statement or replace it with the keyword RESETLOGS, and try again.


ORA-02289 sequence does not exist

Cause: The specified sequence does not exist, or the user does not have the required privilege to perform this operation.

Action: Make sure the sequence name is correct, and that you have the right to perform the desired operation on this sequence.


ORA-02290 check constraint (string.string) violated

Cause: The value or values attempted to be entered in a field or fields violate a defined check constraint.

Action: Enter values that satisfy the constraint.


ORA-02291 integrity constraint (string.string) violated - parent key not found

Cause: A foreign key value has no matching primary key value.

Action: Delete the foreign key or add a matching primary key.


ORA-02292 integrity constraint (string.string) violated - child record found

Cause: An attempt was made to delete a row that is referenced by a foreign key.

Action: It is necessary to DELETE or UPDATE the foreign key before changing this row.


ORA-02293 cannot validate (string.string) - check constraint violated

Cause: An attempt was made via an ALTERTABLE statement to add a check constraint to a populated table that had no complying values.

Action: Retry the ALTER TABLE statement, specifying a check constraint on a table containing complying values. For more information about ALTER TABLE, see the Oracle9i SQL Reference.


ORA-02294 cannot enable (string.string) - constraint changed during validation

Cause: While one DDL statement was attempting to enable this constraint, another DDL changed this same constraint.

Action: Try again, with only one DDL changing the constraint this time.


ORA-02295 found more than one enable/disable clause for constraint

Cause: An attempt was made via a CREATE or ALTER TABLE statement to specify more than one ENABLE and/or DISABLE clause for a given constraint.

Action: Only one ENABLE or DISABLE clause may be specified for a given constraint.


ORA-02296 cannot enable (string.string) - null values found

Cause: An ALTER TABLE command with an ENABLE CONSTRAINT clause failed because the table contains values that do not satisfy the constraint.

Action: Make sure that all values in the table satisfy the constraint before issuing an ALTER TABLE command with an ENABLE CONSTRAINT clause. For more information about ALTER TABLE and ENABLE CONSTRAINT, see the Oracle9i SQL Reference.


ORA-02297 cannot disable constraint (string.string) - dependencies exist

Cause: An alter table disable constraint failed because the table has foreign keys that are dependent on the constraint.

Action: Either disable the foreign key constraints or use a DISABLE CASCADE command.


ORA-02298 cannot validate (string.string) - parent keys not found

Cause: An ALTER TABLE ENABLE CONSTRAINT command failed because the table has orphaned child records.

Action: Make sure that the table has no orphaned child records before issuing an ALTER TABLE ENABLE CONSTRAINT command. For more information about ALTER TABLE and ENABLE CONSTRAINT, see the Oracle9i SQL Reference.


ORA-02299 cannot validate (string.string) - duplicate keys found

Cause: An ALTER TABLE ENABLE CONSTRAINT command failed because the table has duplicate key values.

Action: Make sure that the table has no duplicate key values before issuing an ALTER TABLE ENABLE CONSTRAINT command. For more information about ALTER TABLE and ENABLE CONSTRAINT, see the Oracle9i SQL Reference.


ORA-02300 invalid value for OIDGENERATORS

Cause: A number was not specified for the value of OIDGENERATORS.

작성자: 시간: 댓글 없음:
라벨: ,

2009년 6월 28일 일요일

Define foreign key

rop table Books;
Drop table Authors;
Drop table AuthorBook;


CREATE TABLE Books (
   BookID SMALLINT NOT NULL PRIMARY KEY,
   BookTitle VARCHAR(60) NOT NULL,
   Copyright YEAR NOT NULL
)
ENGINE=INNODB;

INSERT INTO Books VALUES (1, 'Letters', 1934),
                         (2, 'Ohio', 1919),
                         (3, 'Angels', 1966),
                         (4, 'Speaks', 1932),
                         (5, 'Man', 1996),
                         (6, 'A', 1980),
                         (7, 'Card', 1992),
                         (8, 'The', 1993);

CREATE TABLE Authors (
   AuthID SMALLINT NOT NULL PRIMARY KEY,
   AuthFN VARCHAR(20),
   AuthMN VARCHAR(20),
   AuthLN VARCHAR(20)
)
ENGINE=INNODB;

INSERT INTO Authors VALUES (1, 'Henry', 'S.', 'Thompson'),
                           (2, 'Jack', 'Carol', 'Oates'),
                           (3, 'Red', NULL, 'Elk'),
                           (4, 'White', 'Maria', 'Rilke'),
                           (5, 'Anne', 'Kennedy', 'Toole'),
                           (6, 'Jane', 'G.', 'Neihardt'),
                           (7, 'Jane', NULL, 'Yin'),
                           (8, 'Alan', NULL, 'Wang');

CREATE TABLE AuthorBook (
   AuthID SMALLINT NOT NULL,
   BookID SMALLINT NOT NULL,
   PRIMARY KEY (AuthID, BookID),
   FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
   FOREIGN KEY (BookID) REFERENCES Books (BookID)
)
ENGINE=INNODB;

INSERT INTO AuthorBook VALUES (1, 8),
                              (2, 7),
                              (3, 6),
                              (4, 5),
                              (5, 4),
                              (6, 2),
                              (8, 1);
작성자: 시간: 댓글 없음:
라벨: ,

긴 문자열 짧게 보여주기

SELECT IF(LENGTH(content) > 20, CONCAT(SUBSTRING(content, 1, 20), '....'),content) content FROM table

 content 이 20보다 길면 뒤에 '...' 을 붙여서 출력
작성자: 시간: 댓글 없음:
라벨: ,

[mysql]명령어모음

출처 DAIOS.CO.KR | 다이오스
원문 http://blog.naver.com/moonsoo3/120039682685

 

-------------접속---------------------

C:\Documents and Settings\황재호> cd \mysql\bin

C:\mysql\bin> mysql

C:\mysql\bin> mysql -u계정 -p비밀번호 데이터베이스명

mysql> quit

 

-------------------DB생성---------------------

create database daios;

 



-----------------테이블 검색---------------------

show tables;




----------------필드정보 검색-----------------------

show columns from tablename;



------------------테이블 구조 정보-------------------

desc tablename;



----------------테이블 생성---------------

create table friend(
num int not null,
name varchar(10),
address varchar(80),
tel varchar(20),
PRIMARY KEY(num)
);






----------------데이터 삽입-----------------

mysql> insert into 테이블명 (필드명1, 필드명2, ....)
             values (필드값1, 필드값2, ...);


mysql> insert into friend (num, name, address, tel)
       values (1, ‘배성진‘, ’서울 동작구 노량진동‘, ’234-7693‘);


mysql> insert into friend values(1,'박문수','서울시 노원구','010-8886-0487');
mysql> insert into friend values(2,'김성복','서울시 성북구','010-7776-0123');




------------------데이터 확인------------------

mysql> select * from friend;



-----------------조건검색------------------

mysql> select 필드명1, 필드명2, … from 테이블명 where 조건식;

mysql> select id, name, address, tel, sex from mem where sex='W';

mysql> select * from mem where age>=50;

mysql> select name, id, address, post_num from mem where
       age>=20 and age<30;

mysql> select name, id, address, post_num, age from mem where
       name='김진모';

mysql> select name, address, age from mem where
       (age>=40 and age<50) and sex='M';

mysql> select name, id, address, tel, age from mem where
       ( (age>=20 and age<30) or (age>=40 and age<50) )
       and sex='W';

mysql> select name, address, tel from mem where name like '김%';

mysql> select * from mem where address like '서울%';

mysql> select name, address, sex from mem where
         -> address like '부산%' and sex='W' ;

mysql> select name, id from mem where name like '__용%' ;

mysql> select name, address, tel from mem where
        -> address like '광주%' and name like '김%';

mysql> select 필드명1, 필드명2 from 테이블명 order by 필드명;

mysql> select age, id, name, sex, tel from mem order by age;

mysql> select age, id, name, sex, tel from mem order by age desc;

mysql> select age, name, address from mem where address like '서울%‘
       order by age desc;



-------------------데이터 수정-------------------------

mysql> update 테이블명 set 필드명=필드값 [where 조건식]
mysql> update mem set tel='123-4567' where id='yjhwang';
mysql> select id, name, tel from mem where id='yjhwang';
mysql> update mem set age=27 where name='신수진';
mysql> select name, age from mem where name='신수진';


ALTER TABLE `members` ADD `home` VARCHAR( 50 ) NULL AFTER `email` ;





------------------데이터 삭제하기---------------

mysql> delete from 테이블명 [where 조건식]
mysql> delete from mem where name='김길수';
mysql> delete from mem where age>=30 and age<=50;
mysql> select name, address, age from mem;
mysql> delete from mem;




----------------데이터 백업--------------------

C:\mysql\bin> mysqldump -u계정 -p비밀번호 데이터베이스 이름 >
                          백업파일명

C:\mysql\bin> mysqldump -uphp5 -p1234 php5_db >
                          php5_db.sql



------------데이터 복원-------------------

C:\mysql\bin> mysql -u계정 -p비밀번호 데이터베이스 이름 <
                          백업파일명

C:\mysql\bin> mysql -utest -p1234 test_db < php5_db.sql




--------------생성된 DB확인----------------

mysql> show databases;




------------계정 비번 등록---------------

mysql> insert into user (host, user, password)
       values ('localhost', 'php5', password('1234'));



-------------계정 확인---------------

mysql> select host, user, password from user;



---------------권한 등록----------------

mysql> insert into db values ('localhost', 'php5_db', 'php5', 'y', 'y',
       'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y');



--------------변경된 내용 저장하기------------

mysql> flush privileges;



--------------새로운 계정 접속---------------

C:\mysql\bin> mysql -uphp5 -p1234 php5_db


------------관리자 계정 접속----------------

C:\mysql\bin> mysql mysql


-----------비밀번호 변경-----------------------

mysql> update user set password = password('1234') where
       user = 'root';


-------------시스템에 적용---------------

mysql> flush privileges;



------------데이터 베이스 삭제------------

mysql> drop database 데이터베이스명;



-------------테이블 목록 보기------------

mysql> show tables;



--------------테이블 구조 보기-----------

mysql> desc 테이블명;



----------새로운 테이블 추가----------------

mysql> alter table 테이블명 add 새로운필드명 타입
           [first 또는 after 필드명] ;

mysql> alter table friend add age int;




--------------address 다음에 필드 추가---------------

mysql> alter table friend add email char(30) after address;



---------------필드 삭제------------------

mysql> alter table 테이블명 drop 삭제할필드명1, 삭제할필드명2;

mysql> alter table friend drop email;



---------------필드 수정------------------

mysql> alter table 테이블명 change 이전필드명 새로운필드명 타입;

mysql> alter table friend change tel phone int;



------------필드 타입 수정--------------

mysql> alter table 테이블명 modify 필드명 새로운타입;

mysql> alter table friend modify name int;



----------테이블명 수정----------------

mysql> alter table 이전테이블명 rename 새테이블명;

mysql> alter table friend rename student;



-----------테이블 삭제----------------

mysql> drop table friend;





----------------.sql명 실행--------------------

C:\mysql\bin> mysql -uphp5 -p1234 php5_db < friend.sql







-----------------table 생성--------------------

create table testboard(
num                 int not null default '0' auto_increment,
writer                varchar(12),
passwd                 varchar(12),
subject                varchar(50),
content         blob,
visit                 int(4),
primary key(num)
);


--------------조건검색---------------------

select * from user_info where LIKE '이%'
--성이 '이'성만 가진 사람을 검색

select * from user_info where LIKE '%이%'
--'이'를 가진 사람을 모두 검색



---------------조건삭제-------------------

delete from 테이블명 where 필드네임=130;
delete from board where num=130;

delete from board where nun<130;
--넘버가 130아래로 전부삭제

delete from board where subject LIKE '%민%'
--서브젝트에서 '민'이란 단어들어가면 전부 삭제



----------------조건 업데이트------------------

update test set name='다이오스' where num=120;


-------------------인서트문---------------
insert into test(name, id)
values('다이오스','kim')

----------------권한부여--------------------------
grant select, insert, update, delete, create, drop
on chap11.* to 'jspexam'@'localhost'
identified by '1234';


grant select, insert, update, delete, create, drop
on chap11.* to 'jspexam'@'%'
identified by '1234';

 

 

 

-------------공백관련 레고드 검색-----------
select * from MEMBER where NAME <> "" ;
select * from MEMBER where NAME is null;
select * from MEMBER where NAME is not null;

작성자: 시간: 댓글 없음:
라벨: ,

게시판에서 게시물 가져올때...

작성자: 시간: 댓글 없음:
라벨: ,

mysql 로그인 비밀번호 바꾸기

mysql > set password for 아이디@'localhost'=password('새비번')
작성자: 시간: 댓글 없음:
라벨: ,

Oracle 명령..

select [* or 필드명] from [테이블명] where [조건]

                                 order by [필드명] desc/asc   
select [* or 필드명] from [테이블명] where [조건]

                                 group by [필드명] inving [조건]

 

in(자료) <=자료중 만족하는 것들    
all(자료) <= 모든 조건믈 만족할때

 

create table [테이블명] (필드명 타입설정)
                  not null
                  primary key

create sequence [시컨스명] increment by 1 start with 1;

[시컨스명].currval    <=현재의 값
[시컨스명].nextval    <=다음값으로

commit; <= 저장

alter table [테이블명]  add 필드명 필드타입
   add primary key (필드명)
   rename column [필드명] to [필드명]
   modify 필드명 필드타입


sum() 합
avg() 평균
max() 최대값
min() 최소값

작성자: 시간: 댓글 없음:
라벨: ,

oracle vs mysql (줄번호)

--oracle--
  rownum
 
 --mysql
 SELECT @rownum:=@rownum+1 rownum, t.*
 FROM (SELECT @rownum:=0) r, mytable t;
작성자: 시간: 댓글 없음:
라벨:

MySQL Like 한글 문제시

 1) select * from member where addr like '%금정%'

 

2) select * from member where binary(addr) like '%금정%'


1)  => 2)번 방법으로..

작성자: 시간: 댓글 없음:
라벨: ,
피드 구독하기: 글 (Atom)