Oracle is my girl.

BACKUP_ROW.sql

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;

BackupObjects

-- 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

Constraints.sql

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)

CreateJob.sql

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;
/

CreateSchema.sql

-- 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;

ForeignKeys.sql

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)

index.sql

--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'
;

IndexSize.sql

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

JobDetails.sql

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;

Karisik.sql

--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;

ObjectReferences.sql

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

OracleSessions.sql

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;
     

RESTORE_ROW.sql

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;

rollback.sql

--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>';

SpatialIndex.sql

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;

ValidateGeometry.sql

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;