Differences

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

--- oracle [2025/04/22 12:45] – mduersch
+++ oracle [2025/12/15 08:24] (current) – [SELECTS] mduersch
@@ Line 1: / Line 1: @@
 ====== 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 ====
@@ Line 14: / Line 60: @@
 15475 AES256 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production        ###　Make sure it     is encrypted with AES256 ###
 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 ====
@@ Line 30: / Line 92: @@
   expdp -help
+==== Tables ====
+size of table
-==== Tablesoace Usage ====
+   SELECT SUM(bytes)/1024/1024 AS "Table Size (MB)" FROM user_segments WHERE segment_name='&tablename';
+==== 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';
@@ Line 158: / Line 224: @@
 ==== Sessions (to kill?) ====
-set lines 200
+  set lines 200
-set pages 999
+  set pages 999
   col username for a12
@@ Line 180: / Line 246: @@
   ,OSUSER,MACHINE, substr(PROGRAM,1,32) PROGRAM, to_char(LOGON_TIME,'DD.MM.yyyy HH24:MI:SS')LOGON_TIME
    from gv$session
-   where type='USER'
+   where type='USER';
+   # Danach ggf:
+   ALTER SYSTEM KILL SESSION 'sid,serial#';
 ==== AWR ====
@@ Line 499: / Line 568: @@
    select LOG_MODE 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 ====