sql_kl.txt

CREATE TABLE table_name ( Column name datatype, Column name datatype, . . . );
 
ALTER TABLE table_nameADD (column name datatype)

MODIFY (column name datatype);
 
DROP TABLE table_name;
 
CREATE VIEW view_name (view column name1, view column name2, . . .) as QUERY;
 
DROP VIEW view_name;
 

CREATE INDEX index_name onTable_name (column1, column2, . . .);
 
DROP INDEX index_name;
 
CREATE SYNONYM synonym name FOR Object_name;
 
DROP SYNONYM synonym_name;
 

DELETE FROM table_name WHERE qualifiers . . . ;
 
INSERT INTO table_name (column1, column2, . . .) VALUES (value1, value2, . . .);
 
UPDATE table_name SET column1 = value, column2 = value, . . .WHERE qualifiers . . . ;
 
RENAME old_name TO new_name;
 
COMMIT;
 
ROLLBACK; ROLLBACK TO savepoint_name;
 
SAVEPOINT savepoint_name;
 
SELECT column1,column2, . . .(or * for all columns)FROM table_name WHERE qualifiers for rows;
 

-- SQL*Plus

spool c:\CHAPTER_3_REPORT
set echo off
set pagesize 55
set linesize 132
set newpage 0
ttitle left   'All Employees and Phone Numbers' -
      right  'Pg: ' format 999 sql.pno  skip 1 -
      center 'Guerrilla Oracle University' skip 1 -
      center 'As of &&date' skip 2
column PERSON_ID  format 999999999 Heading 'Person ID'
column Name      format a20 Heading 'Name'
column Phone     format a12 Heading 'Phone Number'
break on PERSON_ID skip 2 -

SELECT  PERSON_TABLE.PERSON_ID,  NAME, PHONE_NUMBER
                      from PERSON_TABLE,
TEMP_PERSON_TABLE
WHERE PERSON_TABLE.PERSON_ID = TEMP_PERSON_TABLE.PERSON_ID
order by 1;
spool off;

-- 4 rendszer változó:
%ROWCOUNT eredmény sorszám,
%NOTFOUND Nincs több erdménysor,
%FOUND Eredmény kurzornak van még sora,
%ISOPEN a kurzor meg van nyítva

-- Oracle tábla terek
Tablespace  Data File  Contents
 
SYSTEM      SYSTEM01.DBF Data dictionary, definitions of tables, views
USERS 	    USER01.DBF   Data for applications
TEMP        TEMP01.DBF   Space for sorts
RBS         RBS01.DBF    Rolled-back transactions that did not complete normally
INDX        INDX01.DBF   Indexes tied to the data in the USERS tablespace
DRSYS       DR01.DBF     Objects tied to Oracle interMedia
TOOLS       TOOLS01.DBF  Initially empty, used for third-party software
 
redo logs: REDO01.LOG, REDO02.LOG, REDO03.LOG.
INIT.ORA 
control files: CONTROL01.CTL, CONTROL02.CTL, CONTROL03.CTL. 
18 db rollback segments: SYSTEM, RB_TEMP, and RB1 through RB16.

-- SQL*Loader

- control (.ctl) file létrehozása.(bemenet)
- adat (.dat) file elõállítása.          (bemenet)

- DOS prompt-ból  "SQLLDR" meghívása

- Napló (.log) file   (kimenet)
- Discard file (.dsc) (kimenet)
- Hibák file (.bad)   (kimenet)

-- adatbázis rendszer táblák/nézetek
USER_CATALOG
 Tables, views, synonyms, and sequences that the user owns. There are only two columns of data: Table_Name and Table_Type.
 
USER_CONS_COLUMNS
 The columns that are involved with the constraints. Use this with USER_CONSTRAINTS.
 
USER_CONSTRAINTS
 Any constraints on any table that the user owns. Use ALL_CONSTRAINTS to see the constraints on all the tables that the user can access.
 
USER_OBJECTS
 Information for all types of objects for the user.
 
USER_RESOURCE_LIMITS
 Any limits, such as logons, that were given to the user's profile.
 
USER_SEQUENCES
 All the sequences that the user owns. Use ALL_SEQUENCES to see all the sequences that the user can access.
 
USER_SYNONYMS
 Synonyms that the user owns. Use ALL_SYNONYMS to see the entire list of synonyms that the user can access.
 
USER_TAB_COLS
 All the columns for all the tables that the user owns. Use ALL_TAB_COLS to see all the tables and columns that the user can access.
 
USER_TABS
 26 columns of information on tables that the user owns. Use the ALL_TABS view to see all the tables that the user can access.
 
USER_VIEWS
 Views that the user owns. Use ALL_VIEWS to see the views that the user can access.
 
-- 

V$SESSION shows all current sessions.
V$SYSSTAT gives statistics about the entire database.
V$BGPROCESS shows any background processes that are active.
V$DATAFILE shows data on the files in the database

--
left outer join: WHERE STUDENTS.STUDENT_ID(+) = TEST_HISTORY.STUDENT_ID.
right outer join: WHERE TEST_HISTORY.TEST_ID = TEST_ID.TEST_ID(+)

--
SELECT TRANSLATE(L_NAME, 'R', ' ') from STUDENTS   
SELECT RTRIM(TEST_NAME, 'Ph.D') from TEST_ID;
SELECT RPAD(L_NAME,40,'.'), F_NAME from STUDENTS;
SELECT LENGTH(L_NAME) from STUDENTS
SELECT SUBSTR(L_NAME, 3,5) from STUDENTS; 
SELECT L_NAME||F_NAME from STUDENTS ::: SELECT CONCAT (L_NAME,F_NAME) from STUDENTS; 
SELECT L_NAME INSTR(L_NAME, 'a') from STUDENTS;
DECODE (mezõnév, mit1, mire1, mit2, mire2, mit3, mire3,  egyébként )

-- Tábla töltése másik táblából
CREATE TABLE t AS SELECT * FROM table1@remote_site;
INSERT INTO t SELECT * FROM table1@remote_site;
UPDATE t SET lobcol = (SELECT lobcol FROM table1@remote_site);
INSERT INTO table1@remote_site SELECT * FROM local_table;
UPDATE table1@remote_site SET lobcol = (SELECT lobcol FROM local_table);
DELETE FROM table1@remote_site <WHERE clause involving non_lob_columns>

-- Optimalizálás hangolása (ALL_ROWS, FIRST_ROWS, DBMS_STATS, APPEND, CACHE, INSERT, FULL )
SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
  FROM employees
  WHERE employee_id = 7566;

SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id
  FROM employees e
  WHERE manager_id < 110
    AND department_id < 50;

-----
REF:

 

 

SELECT CASE WHEN constraint_name IS NULL THEN 'Nincs foreign key' ELSE 'Van foreign key' END, COUNT(*)

FROM

(

 

SELECT /*+ no_merge(fk) use_hash(a,b) use_hash(a,fk)*/

       a.table_name

      ,a.column_name

      ,fk.constraint_name

      ,fk.target_table_name

      ,fk.target_column_name

FROM DBA_TAB_cols a

     ,dba_tables b

     ,(SELECT a.owner AS source_owner, a.table_name AS source_table_name, a.column_name AS source_column_name, a.constraint_name

             ,c.owner AS target_owner, c.table_name AS target_table_name, c.column_name AS target_column_name

         FROM dba_cons_columns a

             ,dba_constraints b

             ,dba_cons_columns c

       WHERE a.owner = 'DM'

         AND a.owner = b.owner

         AND a.table_name = b.table_name

         AND a.constraint_name = b.constraint_name

         AND b.constraint_type = 'R'

         AND b.R_OWNER = c.owner

         AND b.R_CONSTRAINT_NAME = c.CONSTRAINT_NAME) fk

WHERE a.owner = 'DW'

  AND a.table_name LIKE 'DW%'

  AND a.table_name NOT LIKE '%/_V' ESCAPE '/'

  AND a.table_name NOT LIKE '%/_V_' ESCAPE '/'

  AND a.table_name NOT LIKE '%/_SAV' ESCAPE '/'

  AND a.table_name NOT LIKE '%/_HIST' ESCAPE '/'

  AND SUBSTR(a.table_name,-1) NOT IN ('0','1','2','3','4','5','6','7','8','9')

  AND a.table_name ||'_CURR' = b.table_name(+)

  AND a.column_name LIKE '%/_ID' ESCAPE '/'

  AND column_name NOT IN ('ID', 'LOAD_ID', 'UPDATE_ID')

  AND b.table_name IS NULL

  AND a.owner = fk.source_owner(+)

  AND a.table_name = fk.source_table_name(+)

  AND a.column_name = fk.source_column_name(+)

 

)

GROUP BY CASE WHEN constraint_name IS NULL THEN 'Nincs foreign key' ELSE 'Van foreign key' END

;

 

 

 

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

   FOREIGN KEY (column1, column2, ... column_n)

   REFERENCES parent_table (column1, column2, ... column_n);

  

ALTER TABLE "DM"."AB_DEAL" ADD CONSTRAINT AB_DEAL_AD_INTY_FK2 FOREIGN KEY (AD_INTY_ID)  REFERENCES "DM"."AD_INTEREST_TYPE" (ID);  

   

SELECT 'ALTER TABLE "'||source_owner||'"."'||source_table_name||'" ADD CONSTRAINT '||constraint_name||' FOREIGN KEY ('||source_column_name

     ||')  REFERENCES "'||target_owner||'"."'||target_table_name||'" ('||target_column_name||');'

FROM   

(SELECT a.owner AS source_owner, a.table_name AS source_table_name, a.column_name AS source_column_name, a.constraint_name

             ,c.owner AS target_owner, c.table_name AS target_table_name, c.column_name AS target_column_name

             ,row_number() OVER (PARTITION BY a.constraint_name ORDER BY a.constraint_name) rn    

         FROM dba_cons_columns a

             ,dba_constraints b

             ,dba_cons_columns c

       WHERE a.owner = 'DM'

         AND a.owner = b.owner

         AND a.table_name = b.table_name

         AND a.constraint_name = b.constraint_name

         AND b.constraint_type = 'R'

         AND b.R_OWNER = c.owner

         AND b.R_CONSTRAINT_NAME = c.CONSTRAINT_NAME) fk  

WHERE constraint_name LIKE 'AG_BATR%'

  AND rn = 1;

 

 

 

REF KL

 

set head off;

set feed off;

set trimspool on;

set linesize 32767;

set LONG 32767;

set LONGCHUNKSIZE 32767;

set pagesize 10;

set echo off;

SET VERIFY OFF

set termout on;

SET RECSEP OFF;

SET TERMOUT OFF;

     

SPOOL c:\Users\u23442\kl\modell_zsolt\PowerDesigner\PD_DM_DDL_20210118.sql replace;

 

BEGIN

    dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);

    dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);

    dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);

--    dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);

--    dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);

    dbms_metadata.set_transform_param (dbms_metadata.session_transform,'PARTITIONING', false);

            dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

    --- exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

END;

/

--AC  -> Riport tábla

--AP  -> Partíció cserével töltött táblákhoz tartozó átmeneti pár

--AR_ -> Riportolásra előkészített ténytábla, denormalizált)

--AM_ -> Munkatáblák, amelyhez felhasználó is hozzáférhet

--AT_ -> Kimenő fájl interfész

--PM  -> DM töltés közbülső táblája

-- select dbms_metadata.get_dependent_ddl( 'COMMENT', table_name, user )

-- select dbms_metadata.get_ddl(object_type, object_name, owner)||';'

select dbms_metadata.get_ddl(object_type, object_name, owner)||';'

from

(

    --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:

    select

        owner,

        --Java object names may need to be converted with DBMS_JAVA.LONGNAME.

        --That code is not included since many database don't have Java installed.

        object_name,

        decode(object_type,

            'DATABASE LINK',      'DB_LINK',

            'JOB',                'PROCOBJ',

            'RULE SET',           'PROCOBJ',

            'RULE',               'PROCOBJ',

            'EVALUATION CONTEXT', 'PROCOBJ',

            'CREDENTIAL',         'PROCOBJ',

            'CHAIN',              'PROCOBJ',

            'PROGRAM',            'PROCOBJ',

            'PACKAGE',            'PACKAGE_SPEC',

            'PACKAGE BODY',       'PACKAGE_BODY',

            'TYPE',               'TYPE_SPEC',

            'TYPE BODY',          'TYPE_BODY',

            'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',

            'QUEUE',              'AQ_QUEUE',

            'JAVA CLASS',         'JAVA_CLASS',

            'JAVA TYPE',          'JAVA_TYPE',

            'JAVA SOURCE',        'JAVA_SOURCE',

            'JAVA RESOURCE',      'JAVA_RESOURCE',

            'XML SCHEMA',         'XMLSCHEMA',

            object_type

        ) object_type

       ,RANK() OVER (ORDER BY object_name) rn

    from dba_objects

    where 1=1

        AND owner in ('DM')

        -- szűrések DM-hez

        and not (owner = 'DM' AND object_type = 'TABLE' and object_name NOT like 'A%')

        and not (owner = 'DM' AND object_type = 'TABLE' and substr(object_name,1,2) IN ('AR', 'AP', 'AC', 'AT'))

        --AND object_name LIKE '%DICTIONARY%' 

        AND object_name NOT LIKE '%/_HIST' ESCAPE '/'

        AND object_name NOT LIKE 'TMP%' ESCAPE '/'

        AND object_name NOT LIKE '%/_SAV%' ESCAPE '/'

        AND object_name NOT LIKE 'INC%' ESCAPE '/'

        AND object_type NOT IN ('VIEW', 'PACKAGE', 'PACKAGE BODY')

        AND object_type IN ('TABLE')

        --These objects are included with other object types.

        and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',

           'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')

        --Ignore system-generated types that support collection processing.

        and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')

        --Exclude nested tables, their DDL is part of their parent table.

        and (owner, object_name) not in (select owner, table_name from dba_nested_tables)

        --Exclude overflow segments, their DDL is part of their parent table.

        and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')

)

--WHERE rn BETWEEN 0 AND 50

order by owner, object_type, object_name;

------------------

select dbms_metadata.get_dependent_ddl( 'COMMENT', table_name, owner )||';'

from (select table_name, owner from all_tab_comments where comments is not null

--and table_name = 'AB_ANONYMIZATION'

-- szűrések DM-hez

and owner in ('DM')

and table_name like 'A%'

and substr(table_name,1,2) NOT IN ('AR', 'AP', 'AC', 'AT')

AND table_name NOT LIKE '%/_HIST' ESCAPE '/'

AND table_name NOT LIKE 'TMP%' ESCAPE '/'

AND table_name NOT LIKE '%/_SAV%' ESCAPE '/'

AND table_name NOT LIKE 'INC%' ESCAPE '/'

);

 

spool off;

SET TERMOUT on;

set echo on;

 

 

 

 

 

 

 

FK:

 

 

SELECT CASE WHEN constraint_name IS NULL THEN 'Nincs foreign key' ELSE 'Van foreign key' END, COUNT(*)

FROM

(

 

SELECT /*+ no_merge(fk) use_hash(a,b) use_hash(a,fk)*/

       a.table_name

      ,a.column_name

      ,fk.constraint_name

      ,fk.target_table_name

      ,fk.target_column_name

FROM DBA_TAB_cols a

     ,dba_tables b

     ,(SELECT a.owner AS source_owner, a.table_name AS source_table_name, a.column_name AS source_column_name, a.constraint_name

             ,c.owner AS target_owner, c.table_name AS target_table_name, c.column_name AS target_column_name

         FROM dba_cons_columns a

             ,dba_constraints b

             ,dba_cons_columns c

       WHERE a.owner = 'DM'

         AND a.owner = b.owner

         AND a.table_name = b.table_name

         AND a.constraint_name = b.constraint_name

         AND b.constraint_type = 'R'

         AND b.R_OWNER = c.owner

         AND b.R_CONSTRAINT_NAME = c.CONSTRAINT_NAME) fk

WHERE a.owner = 'DW'

  AND a.table_name LIKE 'DW%'

  AND a.table_name NOT LIKE '%/_V' ESCAPE '/'

  AND a.table_name NOT LIKE '%/_V_' ESCAPE '/'

  AND a.table_name NOT LIKE '%/_SAV' ESCAPE '/'

  AND a.table_name NOT LIKE '%/_HIST' ESCAPE '/'

  AND SUBSTR(a.table_name,-1) NOT IN ('0','1','2','3','4','5','6','7','8','9')

  AND a.table_name ||'_CURR' = b.table_name(+)

  AND a.column_name LIKE '%/_ID' ESCAPE '/'

  AND column_name NOT IN ('ID', 'LOAD_ID', 'UPDATE_ID')

  AND b.table_name IS NULL

  AND a.owner = fk.source_owner(+)

  AND a.table_name = fk.source_table_name(+)

  AND a.column_name = fk.source_column_name(+)

 

)

GROUP BY CASE WHEN constraint_name IS NULL THEN 'Nincs foreign key' ELSE 'Van foreign key' END

;

 

 

 

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

   FOREIGN KEY (column1, column2, ... column_n)

   REFERENCES parent_table (column1, column2, ... column_n);

  

ALTER TABLE "DM"."AB_DEAL" ADD CONSTRAINT AB_DEAL_AD_INTY_FK2 FOREIGN KEY (AD_INTY_ID)  REFERENCES "DM"."AD_INTEREST_TYPE" (ID);  

   

SELECT 'ALTER TABLE "'||source_owner||'"."'||source_table_name||'" ADD CONSTRAINT '||constraint_name||' FOREIGN KEY ('||source_column_name

     ||')  REFERENCES "'||target_owner||'"."'||target_table_name||'" ('||target_column_name||');'

FROM   

(SELECT a.owner AS source_owner, a.table_name AS source_table_name, a.column_name AS source_column_name, a.constraint_name

             ,c.owner AS target_owner, c.table_name AS target_table_name, c.column_name AS target_column_name

             ,row_number() OVER (PARTITION BY a.constraint_name ORDER BY a.constraint_name) rn    

         FROM dba_cons_columns a

             ,dba_constraints b

             ,dba_cons_columns c

       WHERE a.owner = 'DM'

         AND a.owner = b.owner

         AND a.table_name = b.table_name

         AND a.constraint_name = b.constraint_name

         AND b.constraint_type = 'R'

         AND b.R_OWNER = c.owner

         AND b.R_CONSTRAINT_NAME = c.CONSTRAINT_NAME) fk  

WHERE constraint_name LIKE 'AG_BATR%'

  AND rn = 1;