ORACLE DDL
Lowy Shin edited this page Nov 2, 2020
·
1 revision
- Create database link using tnsnames.ora file
create [public] database link GIIPDB connect to GIIPADMIN identified by "GIIPPWD" using 'GIIPDBTNS';
- tnsnames.ora
GIIPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = giipdb.littleworld.net)(PORT = 1984))
(CONNECT_DATA =
(SERVICE_NAME = GIIPDB)
)
)
- Create Table
create table <tablename> (
Field1 varchar(100)
, Field2 number(10)
)
tablespace USR_D01
;
- Copy table
create table <tablename> tablespace USR_D01 as
select * from <tablenameorg>
;
- Change table name
ALTER TABLE TAB_A RENAME TO TAB_B;
Find primary key from original table
select OWNER, CONSTRAINT_NAME, TABLE_NAME from all_constraints where CONSTRAINT_TYPE = 'P' and TABLE_NAME = '<tablename>';
Add primary key
alter table <tablename>
add constraint <tablename>_PK primary key (<Field1>) ;
- Change column spec
alter table TABLENAME
modify (FIELDNAME1 varchar2(20));
- Change tablespace
alter table TABLENAME
move tablespace TBS002;
-- if use long when ddl script be truncated
set long 100000
select dbms_metadata.get_ddl('TABLE','TableName','Owner') from dual;
select dbms_metadata.get_ddl('Procedure','ProcedureName','Owner') from dual;
- Copy sequence at same starting point
SELECT ' CREATE SEQUENCE ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME ||
' INCREMENT BY ' || TO_CHAR(INCREMENT_BY) ||
' START WITH ' || TO_CHAR(LAST_NUMBER) ||
' MAXVALUE ' || TO_CHAR(MAX_VALUE) ||
' MINVALUE ' || TO_CHAR(MIN_VALUE) ||
' CACHE ' || TO_CHAR(case when CACHE_SIZE = 0 then 10 else CACHE_SIZE end) ||
DECODE (CYCLE_FLAG, 'N', ' NOCYCLE ', 'Y', ' CYCLE ') ||
DECODE (ORDER_FLAG, 'N', ' NOORDER ', 'Y', ' ORDER ') || ';'
FROM ALL_SEQUENCES
WHERE SEQUENCE_OWNER in ('GIIP');
- set sequence
alter sequence
<SEQNAME>
increment by 9999;
- get sequence
- Error occurs when current session has no sequence data. It need execute nextval when error occurs
-- next sequence of SEQA
select SEQA.NEXTVAL from dual;
-- current sequence of SEQA
select SEQA.CURRVAL from dual;
- KB wiki is multi-lingual writing. If you want translate to your language, use google translate!
- Go to giip service Page : http://giipweb.littleworld.net
- Documentation : https://github.com/LowyShin/giip/wiki
- Sample automation scripts : https://github.com/LowyShin/giip/tree/gh-pages/giipscripts
See more : https://github.com/LowyShin/giip/wiki
- Token exchanges : https://tokenjar.io/GIIP
- Token exchanges manual : https://www.slideshare.net/LowyShin/giipentokenjario-giip-token-trade-manual-20190416-141149519
- GIIP Token Etherscan : https://etherscan.io/token/0x33be026eff080859eb9dfff6029232b094732c52
If you want get GIIP, contact us any time!
- LowyWorkEnv : https://github.com/LowyShin/Lowyworkenv/wiki
- CLoud RDBMS比較
- MachineLearning
- SQL Server
- ORACLE
- MySQL
- mariadh
- Redis with MySQL
- Mongodb
- Apache-Drill
- A5MK2-MultiDBMSTool
- BI
- Referrals
- Juliaの自動微分パッケージ Zygote の紹介
- color code table
- Canva - free Online Design tool
- vscode
- git(github)
- GoogleSheet
- UiPath(RPA)
- WinAutomation(official)
- Excel
- VNC
- VPN
- Note Tool
- Blog
- google news alert
- Online PDF to JPG
- LoadTest " putty
- Shell/bash
- crontab
- PowerShell
- Windows(DOS) batch
- Wscript/wsf
- AWS
- Azure
- File Sync(rsync, scp...)
- KnownPort(wikipedia)
- CentOS(Linux)
- Windows10
- Android