Last active
December 17, 2015 10:49
-
-
Save ahmetkakici/5597232 to your computer and use it in GitHub Desktop.
Oracle is my girl.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE PROCEDURE BACKUP_ROW_RECURSIVE(SCHEMA_NAME VARCHAR2, | |
TABLE_NAME VARCHAR2, | |
COLUMN_NAME VARCHAR2, | |
COLUMN_VALUE NUMBER, | |
USER_ID NUMBER, | |
VERSION NUMBER, | |
PROJECT_ID NUMBER) AUTHID CURRENT_USER IS | |
TABLE_ID NUMBER; | |
SQL_QUERY VARCHAR2(500); | |
PROJECT_CONDITION VARCHAR2(100); | |
BEGIN | |
SQL_QUERY := 'SELECT ID FROM GISMASTER.TABLE_DEFINITIONS WHERE NAME = ''' || | |
TABLE_NAME || ''''; | |
EXECUTE IMMEDIATE SQL_QUERY | |
INTO TABLE_ID; | |
IF (SCHEMA_NAME <> 'GISMASTER') THEN | |
PROJECT_CONDITION := ' PROJECT_ID = ' || PROJECT_ID || ' AND '; | |
ELSE | |
PROJECT_CONDITION := ''; | |
END IF; | |
SQL_QUERY := 'INSERT INTO MIMANAGER.ROW_HISTORY(TABLE_ID, ROW_ID, ROW_DATA, USER_ID, VERSION, PROJECT_ID) | |
SELECT | |
' || TABLE_ID || ', | |
' || COLUMN_VALUE || ', | |
TO_CLOB(DBMS_XMLGEN.GETXMLTYPE(''SELECT * FROM ' || SCHEMA_NAME || '.' || | |
TABLE_NAME || ' WHERE ' || PROJECT_CONDITION || COLUMN_NAME || ' = ' || | |
COLUMN_VALUE || ''').GETCLOBVAL()), ' || USER_ID || ', ' || VERSION || ', ' || | |
PROJECT_ID || ' FROM DUAL'; | |
EXECUTE IMMEDIATE SQL_QUERY; | |
END BACKUP_ROW_RECURSIVE; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Get Functions | |
SELECT DBMS_METADATA.GET_DDL('FUNCTION', a.object_name, a.owner) | |
FROM dba_objects a | |
WHERE OWNER = 'MIMANAGER' | |
AND object_TYPE = 'FUNCTION' | |
ORDER BY owner | |
-- Get Procedures | |
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', a.object_name, a.owner) | |
FROM dba_objects a | |
WHERE OWNER = 'MIMANAGER' | |
AND object_TYPE = 'PROCEDURE' | |
ORDER BY owner |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT ucc1.TABLE_NAME, ucc1.column_name, ucc2.TABLE_NAME, ucc2.column_name | |
FROM user_constraints uc, user_cons_columns ucc1, user_cons_columns ucc2 | |
WHERE uc.constraint_name = ucc1.constraint_name | |
AND uc.r_constraint_name = ucc2.constraint_name | |
AND ucc1.POSITION = ucc2.POSITION | |
AND uc.constraint_type = 'R' | |
ORDER BY ucc1.TABLE_NAME, uc.constraint_name; | |
-- | |
select table_name, | |
constraint_name, | |
cname1 || nvl2(cname2, ',' || cname2, null) || nvl2(cname3, ',' || cname3, null) || | |
nvl2(cname4, ',' || cname4, null) || nvl2(cname5, ',' || cname5, null) || | |
nvl2(cname6, ',' || cname6, null) || nvl2(cname7, ',' || cname7, null) || | |
nvl2(cname8, ',' || cname8, null) columns | |
from (select b.table_name, | |
b.constraint_name, | |
max(decode(position, 1, column_name, null)) cname1, | |
max(decode(position, 2, column_name, null)) cname2, | |
max(decode(position, 3, column_name, null)) cname3, | |
max(decode(position, 4, column_name, null)) cname4, | |
max(decode(position, 5, column_name, null)) cname5, | |
max(decode(position, 6, column_name, null)) cname6, | |
max(decode(position, 7, column_name, null)) cname7, | |
max(decode(position, 8, column_name, null)) cname8, | |
count(*) col_cnt | |
from (select substr(table_name, 1, 30) table_name, | |
substr(constraint_name, 1, 30) constraint_name, | |
substr(column_name, 1, 30) column_name, | |
position | |
from user_cons_columns) a, | |
user_constraints b | |
where a.constraint_name = b.constraint_name | |
and b.constraint_type = 'R' | |
group by b.table_name, b.constraint_name) cons | |
where col_cnt > ALL | |
(select count(*) | |
from user_ind_columns i | |
where i.table_name = cons.table_name | |
and i.column_name in | |
(cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8) | |
and i.column_position <= cons.col_cnt | |
group by i.index_name) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
begin | |
sys.dbms_scheduler.create_job(job_name => 'CBS_USER.MANHOLES_CP', | |
job_type => 'PLSQL_BLOCK', | |
job_action => 'BEGIN; A_BINA; END;', | |
start_date => to_date('03-06-2013 00:00:00', 'dd-mm-yyyy hh24:mi:ss'), | |
repeat_interval => 'Freq=Yearly;Interval=1;ByHour=22;ByMinute=22', | |
end_date => to_date(null), | |
job_class => 'DEFAULT_JOB_CLASS', | |
enabled => true, | |
auto_drop => false, | |
comments => 'Menholümde gül oya, gülmedim doya doya.'); | |
end; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Create the user | |
create user SURVEY1 | |
identified by "" | |
default tablespace DATA03 | |
temporary tablespace TEMP | |
profile DEFAULT | |
password expire; | |
-- Grant/Revoke role privileges | |
grant dba to SURVEY1; | |
-- Grant/Revoke system privileges | |
grant create any assembly to SURVEY1; | |
grant create any procedure to SURVEY1; | |
grant insert any table to SURVEY1; | |
grant select any table to SURVEY1; | |
grant unlimited tablespace to SURVEY1; | |
grant update any table to SURVEY1; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select table_name, | |
constraint_name, | |
cname1 || nvl2(cname2, ',' || cname2, null) || nvl2(cname3, ',' || cname3, null) || | |
nvl2(cname4, ',' || cname4, null) || nvl2(cname5, ',' || cname5, null) || | |
nvl2(cname6, ',' || cname6, null) || nvl2(cname7, ',' || cname7, null) || | |
nvl2(cname8, ',' || cname8, null) columns | |
from (select b.table_name, | |
b.constraint_name, | |
max(decode(position, 1, column_name, null)) cname1, | |
max(decode(position, 2, column_name, null)) cname2, | |
max(decode(position, 3, column_name, null)) cname3, | |
max(decode(position, 4, column_name, null)) cname4, | |
max(decode(position, 5, column_name, null)) cname5, | |
max(decode(position, 6, column_name, null)) cname6, | |
max(decode(position, 7, column_name, null)) cname7, | |
max(decode(position, 8, column_name, null)) cname8, | |
count(*) col_cnt | |
from (select substr(table_name, 1, 30) table_name, | |
substr(constraint_name, 1, 30) constraint_name, | |
substr(column_name, 1, 30) column_name, | |
position | |
from user_cons_columns) a, | |
user_constraints b | |
where a.constraint_name = b.constraint_name | |
and b.constraint_type = 'R' | |
group by b.table_name, b.constraint_name) cons | |
where col_cnt > ALL | |
(select count(*) | |
from user_ind_columns i | |
where i.table_name = cons.table_name | |
and i.column_name in | |
(cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8) | |
and i.column_position <= cons.col_cnt | |
group by i.index_name) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--spatial index taşırken alınan ORA-29871 hatasından kurtulup tablespace değiştirmek için | |
ALTER INDEX <INDEX_NAME> REBUILD PARAMETERS('TABLESPACE=<TABLESPACE_NAME>'); | |
--indexi farklı bir tablespace'e taşıyor | |
ALTER INDEX <INDEX_NAME> REBUILD TABLESPACE <TABLESPACE_NAME>; | |
--spatial indexler tablolarının orjinal tablolarla ilişkisi | |
SELECT SDO_INDEX_NAME, SDO_INDEX_TABLE FROM USER_SDO_INDEX_METADATA; | |
--index drop ederken index_status ne olursa olsun uçururuz | |
DROP INDEX <INDEX_NAME> FORCE | |
--spatial index yaratalım | |
CREATE INDEX <INDEX_NAME> ON <TABLE_NAME>(<COLUMN_NAME>) INDEXTYPE IS MDSYS.SPATIAL_INDEX | |
--index rename | |
ALTER INDEX <OLD_INDEX_NAME> RENAME TO <NEW_INDEX_NAME> | |
--indexleri kontrol edelim | |
SELECT OWNER, INDEX_NAME, STATUS FROM ALL_INDEXES WHERE INDEX_TYPE = 'DOMAIN' AND STATUS <> 'VALID' | |
--index ddl alıyore | |
SELECT to_char(DBMS_METADATA.GET_DDL | |
( 'INDEX' | |
, index_name | |
, owner | |
)) | |
FROM all_indexes | |
WHERE table_owner = 'owner' | |
; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT idx.index_name, SUM(bytes) | |
FROM dba_segments seg, | |
dba_indexes idx | |
WHERE idx.table_owner = 'GISMASTER' | |
AND idx.table_name = 'BUILDINGS' | |
AND idx.owner = seg.owner | |
AND idx.index_name = seg.segment_name | |
GROUP BY idx.index_name |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS C WHERE C.OWNER='GISMASTER' order by log_date desc | |
--Hayırlı işler | |
declare | |
l_jobno number; | |
begin | |
dbms_job.submit(l_jobno, | |
'BEGIN XX; END;', | |
sysdate + interval '1' second); | |
COMMIT; | |
end; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--Move Table from tablespace to another | |
ALTER TABLE survey1.cabinet_Attributes MOVE TABLESPACE data03 | |
--Disk size | |
select round(sum(used.bytes) / 1024 / 1024 / 1024) || ' GB' "Database Size", | |
round(sum(used.bytes) / 1024 / 1024 / 1024) - round(free.p / 1024 / 1024 / 1024) || | |
' GB' "Used space", | |
round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" | |
from (select bytes | |
from v$datafile | |
union all | |
select bytes | |
from v$tempfile | |
union all | |
select bytes from v$log) used, | |
(select sum(bytes) as p from dba_free_space) free | |
group by free.p; | |
--Table space size | |
select a.TABLESPACE_NAME, | |
a.total, | |
nvl(b.used, 0) USED, | |
nvl((b.used / a.total) * 100, 0) PCT_USED | |
from (select TABLESPACE_NAME, sum(maxbytes) / (1024 * 1024) total | |
from sys.dba_data_files | |
group by TABLESPACE_NAME) a, | |
(select TABLESPACE_NAME, bytes / (1024 * 1024) used from sys.SM$TS_USED) b | |
where a.TABLESPACE_NAME = b.TABLESPACE_NAME(+); | |
--Search in objects | |
SELECT owner, name, type, line, text | |
FROM dba_source | |
WHERE instr(UPPER(text), UPPER(:srch_str)) > 0; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT * | |
FROM DBA_DEPENDENCIES | |
WHERE REFERENCED_NAME = 'GET_PARENT_DELETE_QUERY' | |
AND OWNER = 'GISMASTER' | |
ORDER BY REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE; | |
-- | |
--Foreign keys on a table | |
select table_name, constraint_name, status, owner | |
from all_constraints | |
where r_owner = :r_owner | |
and constraint_type = 'R' | |
and r_constraint_name in | |
( | |
select constraint_name from all_constraints | |
where constraint_type in ('P', 'U') | |
and table_name = :r_table_name | |
and owner = :r_owner | |
) | |
order by table_name, constraint_name |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select nvl(S.USERNAME,'Internal') username, | |
nvl(S.TERMINAL,'None') terminal, | |
L.SID||','||S.SERIAL# Kill, | |
U1.NAME||'.'||substr(T1.NAME,1,20) tab, | |
decode(L.LMODE,1,'No Lock', | |
2,'Row Share', | |
3,'Row Exclusive', | |
4,'Share', | |
5,'Share Row Exclusive', | |
6,'Exclusive',null) lmode, | |
decode(L.REQUEST,1,'No Lock', | |
2,'Row Share', | |
3,'Row Exclusive', | |
4,'Share', | |
5,'Share Row Exclusive', | |
6,'Exclusive',null) request | |
from V$LOCK L, | |
V$SESSION S, | |
SYS.USER$ U1, | |
SYS.OBJ$ T1 | |
where L.SID = S.SID | |
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) | |
and U1.USER# = T1.OWNER# | |
and S.TYPE != 'BACKGROUND' | |
order by 1,2,5 | |
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess, | |
id1, id2, lmode, request, type, inst_id | |
FROM GV$LOCK | |
WHERE (id1, id2, type) IN | |
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0) | |
ORDER BY id1, request; | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create or replace procedure RESTORE_ROW(schema_name varchar2, | |
table_name varchar2, | |
history_id number) is | |
oldRow clob; | |
l_ctx dbms_xmlsave.ctxType; | |
l_rows number; | |
begin | |
select row_data into oldRow from row_history where Id = history_id; | |
l_ctx := dbms_xmlsave.newContext(schema_name || '.' || table_name); | |
l_rows := dbms_xmlsave.insertxml(l_ctx, oldRow); | |
dbms_xmlsave.closeContext(l_ctx); | |
commit; | |
end RESTORE_ROW; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--kablodaki kayıtlara dön bebeğim diyoruz | |
ALTER TABLE <TABLE_NAME> ENABLE ROW MOVEMENT; | |
FLASHBACK TABLE <TABLE_NAME> TO TIMESTAMP SYSTIMESTAMP - INTERVAL '5' HOUR; | |
ALTER TABLE <TABLE_NAME> DISABLE ROW MOVEMENT; | |
--bu da tablonun o andaki halini select ediyor | |
SELECT * FROM ALL_SOURCE AS OF TIMESTAMP | |
TO_TIMESTAMP('16-03-2012 09:49:00', 'DD-MM-YYYY HH24:MI:SS') | |
WHERE OWNER = '<SCHEMA_NAME>'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE | |
SCHEMA_NAME VARCHAR2(30); | |
BEGIN | |
SCHEMA_NAME := 'GISMASTER'; | |
FOR I IN (SELECT TABLE_NAME | |
FROM ALL_TAB_COLS | |
WHERE OWNER = SCHEMA_NAME | |
AND TABLE_NAME NOT LIKE 'VW_%' | |
AND COLUMN_NAME = 'GEOLOC') LOOP | |
DBMS_OUTPUT.PUT_LINE('CREATE INDEX ' || I.TABLE_NAME || '_IDX ON ' || | |
SCHEMA_NAME || '.' || I.TABLE_NAME || | |
' (GEOLOC) INDEXTYPE IS MDSYS.SPATIAL_INDEX;'); | |
END LOOP; | |
END; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE | |
R NUMBER; | |
V VARCHAR2(500); | |
BEGIN | |
FOR I IN (SELECT * FROM ANKARA.KAPILINK@CBS_LINK WHERE SIL = 0) LOOP | |
BEGIN | |
R := I.ID; | |
SELECT SDO_GEOM.VALIDATE_GEOMETRY(I.GEOLOC, 0.05) INTO V FROM DUAL; | |
EXCEPTION | |
WHEN OTHERS THEN | |
DBMS_OUTPUT.PUT_LINE(R); | |
END; | |
END LOOP; | |
END; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment