윤귀의 우주속 티끌

<테스트용 쿼리>

--덤프 export / import 테스트

--테이블스페이스 생성

 

CREATE TABLESPACE TEST_DATA_IMP

     DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\test_data_imp.dbf' SIZE 10M

     DEFAULT STORAGE

         (INITIAL    10K

          NEXT      10K

          MINEXTENTS 2

          MAXEXTENTS 50

          PCTINCREASE 50);

 

CREATE TEMPORARY TABLESPACE TEST_DATA_IMP_TEMP

     TEMPFILE 'D:\oracle\product\10.2.0\oradata\orcl\test_data_imp_temp.dbf' SIZE 2M;

    

CREATE TABLESPACE TEST_DATA_EXP

     DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\test_data_exp.dbf' SIZE 10M

     DEFAULT STORAGE

         (INITIAL    10K

          NEXT      10K

          MINEXTENTS 2

          MAXEXTENTS 50

          PCTINCREASE 50);

 

CREATE TEMPORARY TABLESPACE TEST_DATA_EXP_TEMP

     TEMPFILE 'D:\oracle\product\10.2.0\oradata\orcl\test_data_exp_temp.dbf' SIZE 2M;    

 

 

--유저생성

create user TEST_EXP identified by aaaa

    default tablespace TEST_DATA_EXP

    temporary tablespace TEST_DATA_EXP_TEMP

    quota unlimited on TEST_DATA_EXP;

 

grant connect, resource to TEST_EXP;

 

create user TEST_IMP identified by aaaa

    default tablespace TEST_DATA_IMP

    temporary tablespace TEST_DATA_IMP_TEMP

    quota unlimited on TEST_DATA_IMP;

 

grant connect, resource to TEST_IMP;

revoke unlimited tablespace from TEST_IMP;          -- 유저에게서 모든 TableSpace 권한 뺏기

 

 

--테이블 생성

create table TEST(

    id  integer

,   str VARCHAR2(10)   

);

 

insert into TEST values(1,'my');

insert into TEST values(2,'urs');

insert into TEST values(3,'them');

 

commit;

 

--테이블 스페이스 확인

select tablespace_name,sum(bytes),max(bytes)

from dba_free_space group by tablespace_name;

   ORACLR_CONNECTION_DATA = 

  (DESCRIPTION = 

    (ADDRESS_LIST = 

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) 

    ) 

    (CONNECT_DATA = 

      (SID = CLRExtProc) 

      (PRESENTATION = RO) 

    ) 

  ) 

  ORACLR_CONNECTION_DATA = 

  (DESCRIPTION = 

    (ADDRESS_LIST = 

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) 

    ) 

    (CONNECT_DATA = 

      (SID = CLRExtProc) 

      (PRESENTATION = RO) 

    ) 

  ) 

select '0'+cast(cast(휴대폰 as numeric(11,0)) as varchar(11)) from 고객목록

http://www.zipfinder.co.kr/zipcode/index.html

load data infile [해당 csv 파일명] into table [db명].[테이블명] fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

예) load data infile 'c:\\download\\zipcode.csv' into table db1.tb_zipcode fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO

-- Create a gap in the identity values.
DELETE products
WHERE product = 'saw'
GO

SELECT *
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.                    <--  이녀석이 on 이 되어야지만 실행이된다. 안그러면 에러가뜸
SET IDENTITY_INSERT products ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO

SELECT *
FROM products
GO
-- Drop products table.
DROP TABLE products
GO

<Join 사용 Update 문>
    update [테이블] set [테이블].[원컬럼명]=A.[새컬럼명]
    from [테이블]
    join [복사할테이블] A on [테이블].[조인할컬럼명]=A.[조인할컬럼명]

MS-SQL 사용예
update statistics 테이블명

시드값 확인
dbcc checkident('테이블 이름', noreseed)

시드값 변경

dbcc checkident('테이블 이름', reseed, 변경값)

* 변경값은 현재시드값을 그 쪽으로 이동시켜주는 것이 됩니다. 예를 들어 증가값이 1로 identity가 설정되어있고 변경값에 40을 입력하여준 뒤 데이터를 추가시키면 컬럼값은 41이 되어서 데이터가 입력이 되게 됩니다(그리고 이 상태에서 계속 순차적으로 값이 올라갑니다)