http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [68]
02.File Book
File 자료실 [95]
Site Link [16]
개인폴더 [194]
03.Field Book
Altibase [19]
Tibero [29]
MS-SQL [18]
MySQL [38]
DB2 [77]
ORACLE [2712]
PostgreSQL [88]
기타정보 [146]
운영체제 [563]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [1]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: ORACLE

 회원가입

create tablespace procedure / create user / alter user / grant get ddl / create database link ddl / get ddl
최길호 [LIST]   2021-07-16 19:51:08, 조회 : 35


CREATE OR REPLACE procedure crts (text IN varchar2)
IS
v1 varchar2(400);
cursor c1 is
-- select tablespace_name||' '||text||' '||row_number() over(partition by tablespace_name order by file_id desc ) from dba_data_files;
select decode(sign(no -1),1,
' '''||text||lower(tablespace_name)||'.'||no||'.dbf'' size 20m autoextend on next 200m maxsize unlimited',
'create tablespace '||tablespace_name||' datafile
'''||text||lower(tablespace_name)||'.'||no||'.dbf'' size 20m autoextend on next 200m maxsize unlimited') ||
decode( no ,(select count(1) cnt from dba_data_files where tablespace_name=a.tablespace_name),';',',')
from ( select tablespace_name,LPAD(row_number() over(partition by tablespace_name order by file_id desc),3,'0') no
from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1') order by 1,2 ) a  ;
begin
OPEN c1;
loop
fetch c1 into v1;
exit when c1%notfound;
dbms_output.put_line(v1);
end loop;
close c1;
end;
/

set lines 200
set serverout on
exec crts('/oracle/oradata/db10g/');



set long 999999999 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

-- CREATE USER "SCOTT" IDENTIFIED BY VALUES
SELECT DBMS_METADATA.GET_DDL('USER', username) AS ddl from dba_users order by created;

-- CREATE object_type
select dbms_metadata.get_ddl(object_type,object_name,owner) AS ddl
from dba_objects where owner = 'SCOTT' -- and object_name in('EMP','DEPT');

출처: http://iotn.co.kr/power/zboard.php?id=9_1_bbs&no=1188

-- CREATE DATABASE LINK / CREATE PUBLIC DATABASE LINK
SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) AS ddl FROM dba_db_links a;

-- GRANT obj (모든것) to to_user
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = '&to_user' group by tp.grantee;

출처: https://oracle-base.com/dba/script?category=script_creation&file=user_ddl.sql

3.235.185.78


  LIST

제목 작성자 작성일 조회
Admin  진행중.    최길호 2008/11/26 109
Admin  [ 필요 하신게 있으시면 Login 하시고 쪽지 또는 gilho.kr@gmail.com 메일 주세요~ ]    최길호 2006/07/14 130
Troubleshoot  [INS-08106] Unexpected error occurred while loading the view 'selectDatabase' associated to state 'selectDatabasePage'.    최길호 2021/07/26 246
Troubleshoot  ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYSTEM ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95    최길호 2021/07/23 8
Troubleshoot  ORA-29707: inconsistent value 0 for initialization parameter [ 조치: 모든 DB restart ] lv:9   최길호 2021/07/20 7
Script  create tablespace procedure / create user / alter user / grant get ddl / create database link ddl / get ddl    최길호 2021/07/16 35
SQL,PL*SQL  POWER BITAND 사용 예    최길호 2021/07/16 20
Troubleshoot  ORA-02475: maximum cluster chain block count of 65534 has been exceeded    최길호 2021/07/14 19
Troubleshoot  11.2.0.4 / library cache lock / select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0    최길호 2021/07/12 22
Script  drop invalid object [ dba_objects, dba_users scott 이전 사용자 (SYS,SYSTEM,... )]    최길호 2021/07/12 27
Troubleshoot  DBMS_XDB_CONFIG ORA-00600: internal error code, arguments: [kokeaai5], [18], [23]    최길호 2021/07/09 40
Troubleshoot  ORA-01075: you are currently logged on ORA-00904: "SPARE10": invalid identifier [ catupgrd.sql ]    최길호 2021/07/09 44
Admin  DROP USER DMSYS CASCADE;    최길호 2021/07/07 31
Troubleshoot  [INS-08101] Unexpected error while executing the action at state: 'selectDatabase'    최길호 2021/07/06 38
Troubleshoot  ORA-28374: typed master key not found in wallet    최길호 2021/07/06 34
Install and Config  ./runInstaller -silent -responseFile inventory/response/db_install.rsp    최길호 2021/07/05 37

    목록보기   다음페이지 1 [2][3][4][5][6][7][8][9][10]..[136]   [다음 10개]
       

Copyright 1999-2021 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
2021/06/30
윈도우 서버에서 sqlplus .... ORA-...
by 최길호
2021/06/30
ORA-28500: connection from O...
by 최길호
2019/05/02
pkg 확인 SELECT * FROM AL...
by 최길호
최근 게시물
07/26
[ORACLE]
[INS-08106] Unexpected error o....
by 최길호
07/23
[ORACLE]
ORA-31637: cannot create job S....
by 최길호
07/18
[게시판]
이재명을 지지합니다~ ☜(゚ヮ゚....
by 최길호