THE Wiki

User Tools

Site Tools

Trace: • tape_device_driver • start
oracle

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
oracle [2024/11/27 09:41] – [Dataguard related] mduerschoracle [2025/12/15 08:24] (current) – [SELECTS] mduersch
Line 1: Line 1:
 ====== ORACLE ====== ====== ORACLE ======
  
 +==== CDB PDB ====
 +
 +Befehle 
 +  SHOW CON_NAME
 +  ALTER SESSION SET CONTAINER=pdb1;
 +  SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID; 
 +  SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
 +  SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
 +
 +==== ACLs ====
 +
 +ACLs abfragen:
 +
 +  SET LINESIZE 150
 +  COLUMN host FORMAT A40
 +  COLUMN acl FORMAT A50
 +  SELECT host, lower_port, upper_port, acl
 +  FROM   dba_network_acls
 +  ORDER BY host;
 +
 +
 +
 +  SET LINESIZE 150
 +  COLUMN acl FORMAT A50
 +  COLUMN principal FORMAT A20
 +  COLUMN privilege FORMAT A10
 +  SELECT acl,
 +       principal,
 +       privilege,
 +       is_grant,
 +       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
 +       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
 +  FROM   dba_network_acl_privileges
 +  ORDER BY acl, principal, privilege;
 +
 +Source: https://oracle-base.com/articles/12c/fine-grained-access-to-network-services-enhancements-12cr1
 +==== Spool ====
 +
 +Spool output as csv
 +
 +  connect <user>
 +  set markup csv on;
 +  spool /tmp/data.out
 +  select * from <table>;
 +  spool off;
 +
 +==== Sessions ====
 +
 + SQL> set line 1000
 + SQL> col NETWORK_SERVICE_BANNER for a100
 + SQL> select SID,SERIAL#,NETWORK_SERVICE_BANNER from V$SESSION_CONNECT_INFO order by SID,SERIAL#;
 +  SID SERIAL# NETWORK_SERVICE_BANNER
 +  ---------- ----------  ----------------------------------------------------------------------------------------------------
 +  76 0
 +  80 0
 +  133 15475 TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
 +  133 15475 Encryption service for Linux: Version 19.0.0.0.0 - Production
 +  133 15475 AES256 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production        ###　Make sure it     is encrypted with AES256 ###
 +  133 15475 Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production
 +
 +Besser:
 +  SELECT
 +   sid,
 +   username,
 +   schemaname,
 +   osuser,
 +   terminal,
 +   program
 +  FROM sys.gv_$session s
 +  WHERE (inst_id, sid) not in
 +    (SELECT inst_id, sid
 +       FROM sys.gv_$session_connect_info
 +        WHERE network_service_banner like '%Encryption service adapter%'
 +         or network_service_banner like '%Crypto-checksumming service adapter%')
 +  and TYPE <> 'BACKGROUND'; 
 +
 +==== Grants by User ====
 +
 +  set long 200000 pages 0 lines 131
 +  column meta format a121 word_wrapped
 +  select dbms_metadata.get_ddl('USER', '&&username') meta from dual;
 +  select dbms_metadata.get_granted_ddl('OBJECT_GRANT', '&&username') meta from dual;
 +  select dbms_metadata.get_granted_ddl('ROLE_GRANT', '&&username') meta from dual;
 +  select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&&username') meta from dual;
  
 ==== DBexport ==== ==== DBexport ====
Line 8: Line 92:
   expdp -help   expdp -help
    
 +==== Tables ====
 +
 +size of table 
  
 +   SELECT SUM(bytes)/1024/1024 AS "Table Size (MB)" FROM user_segments WHERE segment_name='&tablename';
  
-==== Tablesoace Usage ====+==== Tablespace Usage ====
  
    select sum(round(bytes/1024/1024/1024)) as Summe_Size_GB, sum(round(MAXBYTES/1024/1024/1024)) as Summe_Max_Size_GB from dba_data_files where TABLESPACE_NAME='DATA';     select sum(round(bytes/1024/1024/1024)) as Summe_Size_GB, sum(round(MAXBYTES/1024/1024/1024)) as Summe_Max_Size_GB from dba_data_files where TABLESPACE_NAME='DATA'; 
Line 136: Line 224:
 ==== Sessions (to kill?) ==== ==== Sessions (to kill?) ====
  
-set lines 200 +  set lines 200 
-set pages 999+  set pages 999
  
   col username for a12   col username for a12
Line 158: Line 246:
   ,OSUSER,MACHINE, substr(PROGRAM,1,32) PROGRAM, to_char(LOGON_TIME,'DD.MM.yyyy HH24:MI:SS')LOGON_TIME   ,OSUSER,MACHINE, substr(PROGRAM,1,32) PROGRAM, to_char(LOGON_TIME,'DD.MM.yyyy HH24:MI:SS')LOGON_TIME
    from gv$session    from gv$session
-   where type='USER' +   where type='USER'; 
 + 
 +   # Danach ggf:    
 +   ALTER SYSTEM KILL SESSION 'sid,serial#';
  
 ==== AWR ==== ==== AWR ====
Line 309: Line 400:
    alter system reset log_archive_config sid='*' scope=both;     alter system reset log_archive_config sid='*' scope=both; 
  
 +
 +   set pagesize 100
 +   set linesize 100
 +   colum segment_name format A50
 +   colum object_name format A50
 +
 +   # Größe von Index Tabelle bestimmen    
 +   select 
 +     segment_name, 
 +     round( bytes/1024/1024/1024,1) as GB
 +   from 
 +     dba_segments 
 +   where 
 +     owner like 'GA_%' and 
 +     segment_type='INDEX';
 +
 +   # Erstelldatum der Indexe    
 +   select 
 +     object_name, 
 +       created 
 +   from 
 +     dba_objects 
 +   where 
 +     owner like 'GA_%' and 
 +     object_type='INDEX'
 +   order by created;
 +  
 +   # Größe und Zahl der vorhandenen / bereits duplizierten Tablepsaces
 +   select 
 +     segment_type, 
 +     round(sum(bytes)/1024/1024/1024,1) as GB 
 +   from 
 +     dba_segments 
 +   where 
 +    owner like 'GA_%'
 +   group by segment_type;
  
 ==== Selects ==== ==== Selects ====
Line 441: Line 568:
    select LOG_MODE from v$database;    select LOG_MODE from v$database;
    select name from V$database;    select name from V$database;
 +   # flashbackquery
 +   select count(1) from somewhere 
 +                 AS OF TIMESTAMP TO_TIMESTAMP('2025-12-12 16:00', 'YYYY-MM-DD HH24:MI:SS') 
 +           where 
 +                 something is NULL;
  
 ==== GRANTS ==== ==== GRANTS ====
oracle.1732700512.txt.gz · Last modified: by mduersch