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

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 sid=SIDNAME mod=dpump "CONTENT=METADATA_ONLY" 
expdp -help

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

oder

1. ——————————————————-
2. - free.sql
3. -
4. - This SQL Plus script lists freespace by tablespace
5. ——————————————————-

column dummy noprint

 column pct_used format 999.9 heading "%|Used"
 column name format a16 heading "Tablespace Name"
 column Kbytes format 999,999,999 heading "KBytes"
 column used format 999,999,999 heading "Used"
 column free format 999,999,999 heading "Free"
 column largest format 999,999,999 heading "Largest"
 column max_size format 999,999,999 heading "MaxPoss|Kbytes"
 column pct_max_used format 999.9 heading "%|Max|Used"
 break on report
 compute sum of kbytes on report
 compute sum of free on report
 compute sum of used on report
 
 select (select decode(extent_management,'LOCAL','*','') from dba_tablespaces where tablespace_name =
 b.tablespace_name) || nvl(b.tablespace_name,
 nvl(a.tablespace_name,'UNKOWN')) name,
 kbytes_alloc kbytes,
 kbytes_alloc-nvl(kbytes_free,0) used,
 nvl(kbytes_free,0) free,
 ((kbytes_alloc-nvl(kbytes_free,0))/
 kbytes_alloc)*100 pct_used,
 nvl(largest,0) largest,
 nvl(kbytes_max,kbytes_alloc) Max_Size,
 decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
 from ( select sum(bytes)/1024 Kbytes_free,
 max(bytes)/1024 largest,
 tablespace_name
 from sys.dba_free_space
 group by tablespace_name ) a,
 ( select sum(bytes)/1024 Kbytes_alloc,
 sum(maxbytes)/1024 Kbytes_max,
 tablespace_name
 from sys.dba_data_files
 group by tablespace_name
 union all
 select sum(bytes)/1024 Kbytes_alloc,
 sum(maxbytes)/1024 Kbytes_max,
 tablespace_name
 from sys.dba_temp_files
 group by tablespace_name )b
 where a.tablespace_name (+) = b.tablespace_name
 order by &1
 /

 Source: https://blog.zeddba.com/2019/05/31/online-redo-log-switch-frequency-map/ 

 set pages 999 lines 400
 col h0 format 999
 col h1 format 999
 col h2 format 999
 col h3 format 999
 col h4 format 999
 col h5 format 999
 col h6 format 999
 col h7 format 999
 col h8 format 999
 col h9 format 999
 col h10 format 999
 col h11 format 999
 col h12 format 999
 col h13 format 999
 col h14 format 999
 col h15 format 999
 col h16 format 999
 col h17 format 999
 col h18 format 999
 col h19 format 999
 col h20 format 999
 col h21 format 999
 col h22 format 999
 col h23 format 999
 SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day",
  COUNT (1) "Total",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
  SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",
  ROUND (COUNT (1) / 24, 2) "Avg"
 FROM gv$log_history
 WHERE thread# = inst_id
 AND first_time > sysdate -7
 GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
 ORDER BY 1,2;

Source: https://blog.zeddba.com/2019/05/31/online-redo-log-switch-frequency-map/

set lines 200 set pages 999

col username for a12
col osuser for a16
col terminal for a12
col machine for a14
col program for a32
--col INST_ID for a8
--col SID for a8
col INST_ID for 9
col SID for 99999999
col LOGON_TIME for a19
col HOST_NAME for a10
col STATUS for a10
col terminal for a20
col STATE for a12 
col SID_SERIAL# for a16

select SID||','||SERIAL# SID_SERIAL#,USERNAME,STATUS
,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' 

execute dbms_workload_repository. modify_snapshot_settings( interval => 15, retention => 21600);

 column local_tran_id  format a22 heading 'Local Txn Id'
 column global_tran_id format a52 heading 'Global Txn Id'
 column state          format a16 heading 'State'
 column mixed          format a5  heading 'Mixed'
 column advice         format a5  heading 'Advice'        
 
 select
    local_tran_id,
    global_tran_id,
    state,mixed,advice
 from  
    dba_2pc_pending
 order 
    by local_tran_id
 / 
 select local_tran_id, state from DBA_2PC_PENDING where state='prepared'; 
 select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared'; 

The rule of thumb for the pga_aggregate_limit is:

  PGA_AGGREGATE_LIMIT =(original PGA_AGGREGATE_LIMIT value) + ((maximum number of connected processes) * 4M)

https://logic.edchen.org/how-to-resolve-ora-08102-index-key-not-found/

ls -l /dev/disk/by-id/wwn* | awk -F 'wwn-0x' '{print $2}' | tr -d './' | sed s/-\>/,/ | tr -d ' ' > linux-diskinfos.csv

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text FROM v$sql; Select SQL_ID, SQL_TEXT from V$SQLSTATS Where SQL_ID='71c8abf33ab64';

Links:

EMCC Tipps: Lizenzsicherheit bei Management Packs

Adminstuff:

Check DB Status:

  SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

The utlrp.sql script recompiles all invalid objects. Run the script immediately after installation, to ensure that users do not encounter invalid objects. Run the utlrp.sql script, where Oracle_home is the Oracle home path:

SQL> @?/rdbms/admin/utlrp.sql

DBA:

• rman duplicate
• performance memory etc

Performance:

force_matching_signature is a hash value for what the statement would look like if literals used for column values were replaced by system-generated bind variables and all the redundant white space were removed

select force_matching_signature, count(*)
from   v$sqlstats
where  force_matching_signature > 0
group  by force_matching_signature
having count(*) > 10
order  by 2 desc

Recovery:

View-Name			Kontext
v$backup			Online-Backup
v$datafile_header		Online-Backup
v$datafile			Beschreibung Datafiles
v$logfile			Beschreibung Redolog-Gruppen 
v$log				Beschreibung Redolog-Member
v$controlfile			Beschreibung Controlfiles
v$controlfile_record_section	Controlfile Record Section
v$tablespace			Beschreibung TS
v$archived_log		alle Archived Redologs
v$recover_file		Datendateien die ein Recovery benötigen
v$recovery_log		Archived Logs die für ein Recovery benötigt werden
v$recovery_file_status	Datendateien die ein Recovery benötigen
v$recovery_status		DB Recovery Informationen
DBA_DATA_FILES		Beschreibung Datafiles

select owner, status, object_type from dba_objects where object_name='MYVIEW'

in diesem Fall Rechteproblem:
grant select on TABLESPACE.TABLE to USER;
alter view USER.VIEW compile; 

 echo "ORA-00600: TEST fuer Ueberwachung" >> /oracle/$ORACLE_SID/admin/bdump/alert_$ORACLE_SID.log

 select database_role, open_mode from v$database;
 select processm, status, sequence# from v$managed_standby;
 select NAME, OPEN_MODE, DATABASE_ROLE from v$database;
 select name, db_unique_name, database_role, switchover_status, open_mode from v$database;
 # recover mode ausschalten
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 # open
 ALTER DATABASE OPEN; 
 # recover mode einschalten
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY;
 select protection_mode, protection_level from v$database;
 select flashback_on from v$database;
 select * from v$dataguard_config order by db_unique_name;
 select * from v$database_incarnation;
 select * from v$archived_log order by recid;
 select * from v$block_change_tracking; 
 select dest_id, destination, status, error, transmit_mode from v$archive_dest where destination is not null;
 # Paramter abfragen
 ==========================
 set linesize 212
 set pagesize 1024
 col name format a40
 col value format a120

 select name, value
    from
 v$system_parameter
   where
 name in ( 'standby_file_management', 'db_recovery_file_dest', 'db_unique_name',
            'service_names', 'fal_client', 'log_archive_format', 'log_archive_config',
            'db_flashback_retention_target', 'log_archive_dest_1', 'log_archive_dest_2',
            'log_archive_dest_3', 'log_archive_dest_4', 'log_archive_dest_state_1',
            'log_archive_dest_state_2', 'log_archive_dest_state_3', 'log_archive_dest_state_4',
            'fal_server', 'db_file_name_convert', 'log_file_name_convert',
            'db_recovery_file_dest_size')
  order by 1;
  
 # ARCHIVE DEST ABSCHALTEN
 alter system set log_archive_dest_state_2 = defer;
 alter system reset log_archive_dest_2 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;

select COMP_NAME, STATUS from dba_registry;

SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS;

rman target /
RMAN> report unrecoverable;
RMAN> list archivelog all;
RMAN> report schema;
RMAN> list backup;
RMAN> show all
RMAN> list incarnation of database;

restore archivelog from sequence 9301 until sequence 9311;

RMAN> restore archivelog from sequence 9301 until sequence 9311;

Upgrading to 19.17…

COMP_ID COMP_NAME VERSION_FULL STATUS
---------- ----------------------------------- ------------------------- ----------
CATALOG Oracle Database Catalog Views 19.17.0.0.0 INVALID
CATPROC Oracle Database Packages and Types 19.17.0.0.0 INVALID
OWM Oracle Workspace Manager 19.17.0.0.0 INVALID
XDB Oracle XML Database 19.17.0.0.0 INVALID

@?/rdbms/admin/dbmsstat.sql
@?/rdbms/admin/utlrp.sql

OBJECT_NAME
-------------------------------------------
KUPU$UTILITIES_INT
KUPW$WORKER
KUPM$MCP
KUPF$FILE 

@?/rdbms/admin/dpload.sql
@?/rdbms/admin/utlrp.sql

DONE

RMAN> RUN
2> {
3> SET UNTIL SEQUENCE 29;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> }

RMAN> RUN
2> {
3> SET until time "to_date('11/15/2022 11:59:43','mm/dd/yyyy hh24:mi:ss')";
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> }
SYS@SID>alter database open RESETLOGS;

The following example creates a level 0 incremental backup to serve as a base for an incremental backup strategy:
  BACKUP INCREMENTAL LEVEL 0 DATABASE;
The following example creates a level 1 cumulative incremental backup:
  BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
The following example creates a level 1 differential incremental backup:
  BACKUP INCREMENTAL LEVEL 1 DATABASE;

CREATE DATABASE LINK ONETST2TWOTST CONNECT TO scott IDENTIFIED BY tiger USING 'TWOTST';
SYS@ONETST>CREATE DATABASE LINK ONETST2TWOTST CONNECT TO scott IDENTIFIED BY tiger USING 'TWOTST';
Database link created.

 Set linesize 200
 Set pagesize 100
 COLUMN object_name FORMAT A50
 COLUMN object_type FORMAT A30
 COLUMN owner FORMAT A30
 spool /tmp/objexts.txt
 select objet_name, ojbect_type, owner from dba_objects;
 spool off

spool /tmp/myfile.lst
  $something to spool
spool off

important performance views nach http://www.dba-oracle.com/t

 V$SYSSTAT - This is the main accumulator for many database-wide statistics.  It is used to compute the
 V$LIBRARYCACHE - This view contain performance statistics about all activities in the library cache.  These include cache_misses and cache_pins.
 V$SESSION_WAIT - This shows all Oracle sessions that are waiting on internal resources
 V$SGASTAT - This shows internal SGA statistics such as free_memory.
 V$SQLAREA - This view contain valuable information about executing SQL including the parse_count and the number of times the SQL was executed.
 V$WAITSTAT - This gives details about any sessions that are waiting on Oracle resources
 V$SESSION - This gives detailed session information about all connected Oracle users.
 V$SESSION_EVENT - This is used to show the number of session waits and session timeouts.

 SELECT username, elapsed_time, plsql_exec_time, sql_text, cpu_time, rm_last_action, rm_last_action_reason, rm_last_action_time, rm_consumer_group FROM v$sql_monitor WHERE username is not null;

SYS@DB>select RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE from V$RESOURCE_LIMIT where     RESOURCE_NAME in('sessions', 'processes');
RESOURCE_NAME            CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------------------ ------------------- --------------- ----------------------------------------
processes                                592             800        800
sessions                                 369             675       1280

 select LOG_MODE from v$database;
 select name from V$database;

 grant unlimited tablespace to scott;

 $rman_backup_subjob_details
 v$rman_backup_job_details
 v$backup_set_details
 v$backup_piece_details
 v$backup_copy_details
 $backup
 v$recovery_status
 v$recovery_file_status
 v$backup_set
 v$backup_piece
 v$backup_datafile
 v$backup_redolog
 v$backup_corruption
 v$backup_device
 v$backup_spfile
 v$backup_sync_io
 v$backup_async_io
 v$recover_file
 v$rman_status
 v$rman_output
 v$backup_datafile_details
 v$backup_controlfile_details
 v$backup_archivelog_details
 v$backup_spfile_details
 v$backup_datafile_summary
 v$backup_controlfile_summary
 v$backup_archivelog_summary
 v$backup_spfile_summary
 v$backup_set_summary
 v$recovery_progress
 v$rman_backup_type
 v$rman_configuration

  
  alter system set undo_tablespace=undotwo;
  alter system set undo_management=auto scope = spfile;

  ALTER SYSTEM SET job_queue_processes=4 SCOPE=SPFILE
  ALTER SYSTEM SET sga_max_size=300 SCOPE=SPFILE
  
  alter system set sga_max_size=300M scope=spfile;
  alter system set sga_target=300M scope=both;
  
  alter system set undo_tablespace = undotwo scope=both;

  ALTER DATABASE MOVE DATAFILE '/oracle/data4/TWOTST/data.dbf' TO '/oracle/data4/TWOTST/datafile/data_001.dbf';

  ALTER TABLESPACE DATA ADD DATAFILE '/oracle/data4/TWOTST/datafile/data_002.dbf' SIZE 20M
  
  ALTER TABLESPACE [Tablespacename] ADD DATAFILE '/oracle/data[X]/[DB]/datafile/[Datafilename].dbf' SIZE 128 M AUTOEXTEND ON NEXT 128 M MAXSIZE unlimited;
  
  ALTER DATABASE DATAFILE
  '/oracle/data4/TWOTST/datafile/data_002.dbf' AUTOEXTEND ON
  NEXT 10 M
  MAXSIZE 1024 M
  
  ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/data4/TWOTST/datafile/temp_002.dbf' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE 1024 M;
  
  ALTER TABLESPACE temp TEMPFILE '/oracle/data4/TWOTST/datafile/temp_001.dbf' OFFLINE;
  ALTER DATABASE TEMPFILE '/oracle/data4/TWOTST/datafile/temp_001.dbf' DROP INCLUDING DATAFILES;

  ALTER TABLESPACE [Tablespacename] ADD DATAFILE '/oracle/data[X]/[DB]/datafile/[Datafilename].dbf' SIZE 128 M AUTOEXTEND ON NEXT 128 M MAXSIZE unlimited;

  create undo tablespace UNDOTWO datafile '/oracle/data4/TWOTST/datafile/undotwo_001.dbf' size 1024M;

  CREATE TABLESPACE DATABYHORA
    DATAFILE '/oracle/data4/TWOTST/datafile/databyhora.dbf'
    SIZE 50M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO
  UNIFORM SIZE 128 K

BUK=MYBUK
ORACLE_SID=TWOTST
ORA_NLS10=/oracle/product/19.16.0.0.0p1/nls/data
ORACLE_BASE=/oracle/product
SCHEMA_PASSWD=/oracle/etc/pwdMYBUK
ORACLE_SW=/oracle/product/19.16.0.0.0p1
ORACLE_HOME=/oracle/product/19.16.0.0.0p1

Prüfe installieter database patches

SET LINESIZE 300
COLUMN PATCH_ID  FORMAT 9999999999
COLUMN PATCH_UID  FORMAT 9999999999
COLUMN STATUS  FORMAT A25
COLUMN VERSION  FORMAT A20
COLUMN BUNDLE_SERIES  FORMAT A15
COLUMN BUNDLE_ID  FORMAT 9999999999 
COLUMN ACTION_TIME FORMAT A30
COLUMN DESCRIPTION FORMAT A60

select PATCH_ID, PATCH_UID, STATUS, ACTION_TIME, DESCRIPTION from DBA_REGISTRY_SQLPATCH;

Files: http://www.oracle.com/technology/software/products/database/index.html
Check supported OS. Otherwise: NO FUN at all.

Create Groups, User and set password:
groupadd oinstall
groupadd dba
groupadd oper
groupadd asmadmin
useradd -g oinstall -G dba,oper,asmadmin oracle
passwd oracle

PGA: von Session aufgebauter Bereich. SGA: SystemBereich (System Global Area)

SQL> alter system kill session '140,118';

System wurde geõndert.

SQL> select sid, serial#, osuser, machine from v$session where username ='SYSTEM';

Verbinden mit Datenbank via sqlplus

./sqlplus "/as sysdba"
./sqlplus system@SID
./sqlplus system/oracle (nimmt ORACLE_SID)  
./sqlplus "sys@SID as sysdba" (syspw remote als sysdba) - greift auf tsnnames.ora zum auflösen zu. Benötigt ORAPWD<SID>.ora in $OH/database

Listener Starten:

C:\Dokumente und Einstellungen\Administrator>lsnrctl start LISTENER
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 27-SEP-2011 13:20:19
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
tnslsnr wird gestartet: Bitte warten...

CONNECT & SHUTDOWN:

C:\Dokumente und Einstellungen\Administrator>sqlplus sys@KURS15 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Di Sep 27 13:49:56 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Kennwort eingeben:
Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> shutdown immediate

shutdown transactional
abort (eher nicht zu empfehlen. Quick and Dirty)

startup (pfile=, spfile=) (ggf. sinnvol für Patchen, wenn Speicher vergrößert sein muss. -> Keine Auswirkungen auf normale Installation)
startup nomount
startup mount
alter database open;

Remote Connect zulassen: …

SQL-Statements:

SQL>  select * from DBA_FREE_SPACE;
SQL>  select * from DBA_DATA_FILES;
SQL>  select * from DBA_TABLESPACES;
SQL>  select FILE_NAME, AUTOEXTENSIBLE, MAXBYTES from DBA_DATA_FILES;
SQL>  select * from v$datafile;
SQL>  select * from dict where TABLE_NAME like 'V%';
SQL>  select * from v$instance; (sicherstellen, dass man mit der richtingen DB-Instanz verbunden ist)

– select * from dict where table_name like '%USER%' and not like 'USER%'; – desc ALL_USERS; desc DBA_USERS; – select username as NAME, user_id ID, authentication_type as AUTH, created as DATUM from DBA_USERS; select username, account_status from DBA_USERS; – select * from all_users; – select * from DBA_USERS_WITH_DEFPWD; select * from dba_data_files; select * from DBA_TS_QUOTAS where username = 'MICHAEL';

PL_SQL example:

set serveroutput on;
DECLARE v_file_name  VARCHAR2(100);

BEGIN

select name INTO v_file_name from v$dbfile where file# = 1;
dbms_output.put_line('Hallo Welt, die Datei heisst: '||v_file_name);

END;

Skripte im RDBMS Admin verzeichnis.

ADmin Kram:

alter TABLESPACE drop TABLESPACE data INCLUDING CONTENTS AND DATAFILES;

alter TABLESPACE app_data add datafile '/path/to/db02.dbf' size=200M autoextend on next 10M maxsize 500M;

alter database datafile '/path/to/db02.dbf' resize=200M;

(tablespace offline nehmen:) alter TABLESPACE app_data rename datafile '/path/to/db02.dbf' to '/2ndpath/to/db02.dbf'; (Datei darunter muss gemoved werden)

Table Space anlegen:

CREATE TABLESPACE test_data DATAFILE 'D:\oradata\KURS1\testdata01.dbf' size 20M ,'D:\oradata\KURS1\testdata02.dbf'\\ 
size 20M autoextend on maxsize 100M extent management local segment space management auto;

Eine Parameter anpassen (autoextend)

ALTER DATABASE DATAFILE 'D:\oradata\KURS1\TESTDATA01.dbf' AUTOEXTEND ON;

Eine zusätliche Datei hinzufügen

ALTER TABLESPACE users ADD DATAFILE 'D:\oradata\KURS1\users03.dbf' size 5M autoextend on;

Erweitern einer bestehenden Datendatei:

ALTER DATABASE DATAFILE 'D:\oradata\KURS1\TESTDATA01.dbf' resize 111M;

User anlegen/updaten und mit neuem Tablespace anlegen:

create user test identified by oracle default tablespace test_data;
alter user test default tablespace test_dummy;

Partition: (Festlegen anhand eines Sortierkriteriums welche Werte in welche “virtuelle Tabelle” kommen - kostet extra)

Testweise befüllen und Truncate einer DB insert into dummy (NAME) VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'); set serveroutput on DECLARE n_count NUMBER;

BEGIN for i IN 1..20 LOOP

INSERT INTO dummy SELECT * FROM dummy;
SELECT COUNT(*) INTO n_count FROM dummy;
DBMS_output.put_line('Aktuell '||n_count||' Zeilen');

END LOOP; END;

set timing on –DELETE FROM DUMMY; TRUNCATE TABLE DUMMY; – impliziert commit → kein rollback mehr moeglich select count(*) from dummy;

User anlegen:

create user michael identified by oracle default tablespace test_data\\ 
temporary tablespace temp QUOTA 15m ON test_data;

Alle Objekte die dem User gehören:

select * from cat;

Userrechte:

grant sysdba to testuser; <- nur mit sysdba (sqlplus, system kann das nicht)
select * from V$parameter where name like 'remote%';
select * from v$pwfile_users;
grant execute on dbms_pipe to public; <- schlechte idee ;)
grant update (first_name, salary) on employee to karen WITH GRANT OPTION;
grant select, insert, update, delete, references on dummy2 to michael2;

select * from dba_sys_privs where GRANTEE = 'MICHAEL';

select * from tab_sys_privs where GRANTEE = 'MICHAEL'; select * from col_sys_privs where GRANTEE = 'MICHAEL';

Export für einen User

C:\Dokumente und Einstellungen\Administrator>exp

Export: Release 11.2.0.1.0 - Production on Do Sep 29 15:13:08 2011

Copyright © 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Benutzername: system Kennwort: <

Angemeldet bei: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production Puffergr÷▀e f³r Array-Fetch eingeben: 4096 > <

Datei exportieren: EXPDAT.DMP > c:\schemaexport.dmp

(1)E(Gesamt-DB), (2)U(Benutzer) od. (3)T(abellen): (2)U > 2

Berechtigungen exportieren (ja/nein): ja > ja

Tabellendaten exportieren (ja/nein): ja > ja

Extents komprimieren < (ja/nein): ja > ja

Exportieren in WE8MSWIN1252-Zeichensatz und AL16UTF16-NCHAR-Zeichensatz durchgef³hrt

Spezifizierte Benutzer werden gleich exportiert … Zu exportierende Benutzer: (RETURN f³r Abbruch) > kurs

Zu exportierende Benutzer: (RETURN f³r Abbruch) >

. Prozedurale Pre-Schema-Objekte und -Aktionen werden exportiert . Fremdfunktions-Bibliotheksnamen f³r Benutzer KURS werden exportiert . Synonyme vom Typ PUBLIC werden exportiert . Synonyme vom Typ PRIVATE werden exportiert . Objekttypdefinitionen f³r Benutzer KURS werden exportiert KURS Objekte werden gleich exportiert … . Datenbank-Links werden exportiert . Sequenzzahlen werden exportiert . Clusterdefinitionen werden exportiert . Tabellen von KURS werden gleich exportiert … ³ber 'Conventional Path' . . Export der Tabelle DUMMY 4 Zeilen exportiert . Synonyme werden exportiert . Views werden exportiert . Gespeicherte Prozeduren werden exportiert . Operatoren werden exportiert . Referentielle Integritõts-Constraints werden exportiert . Trigger werden exportiert . Indextypen werden exportiert . Bitmap-, funktionale und erweiterbare Indizes werden exportiert . Exportieren von Posttable-Aktionen . Materialized Views werden exportiert . Snapshot-Log werden exportiert . Exportieren von Job-Queues . Exportieren von Refresh-Gruppen und untergeordneten Gruppen . Dimensionen werden exportiert . Prozedurale Post-Schema-Objekte und -Aktionen werden exportiert . Statistiken werden exportiert Export erfolgreich ohne Warnungen beendet.

C:\Dokumente und Einstellungen\Administrator>

C:\Dokumente und Einstellungen\Administrator>exp system/oracle@KURS1 owner=kurs file=c:\kurs1.dmp log=c:\kurs1exp.log

Export: Release 11.2.0.1.0 - Production on Do Sep 29 15:19:52 2011

Copyright © 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Angemeldet bei: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production Exportieren in WE8MSWIN1252-Zeichensatz und AL16UTF16-NCHAR-Zeichensatz durchgef³hrt

Spezifizierte Benutzer werden gleich exportiert … . Prozedurale Pre-Schema-Objekte und -Aktionen werden exportiert . Fremdfunktions-Bibliotheksnamen f³r Benutzer KURS werden exportiert . Synonyme vom Typ PUBLIC werden exportiert . Synonyme vom Typ PRIVATE werden exportiert . Objekttypdefinitionen f³r Benutzer KURS werden exportiert KURS Objekte werden gleich exportiert … . Datenbank-Links werden exportiert . Sequenzzahlen werden exportiert . Clusterdefinitionen werden exportiert . Tabellen von KURS werden gleich exportiert … ³ber 'Conventional Path' . . Export der Tabelle DUMMY 4 Zeilen exportiert . Synonyme werden exportiert . Views werden exportiert . Gespeicherte Prozeduren werden exportiert . Operatoren werden exportiert . Referentielle Integritõts-Constraints werden exportiert . Trigger werden exportiert . Indextypen werden exportiert . Bitmap-, funktionale und erweiterbare Indizes werden exportiert . Exportieren von Posttable-Aktionen . Materialized Views werden exportiert . Snapshot-Log werden exportiert . Exportieren von Job-Queues . Exportieren von Refresh-Gruppen und untergeordneten Gruppen . Dimensionen werden exportiert . Prozedurale Post-Schema-Objekte und -Aktionen werden exportiert . Statistiken werden exportiert Export erfolgreich ohne Warnungen beendet.

csv-Ausgabe

 select LPAD(name,30)||LPAD(vorname,30) from kurs.dummy;
 select NAME||';'||VORNAME from kurs.dummy;
 

Daten in Tabelle laden

load data
infile 'dummy.csv'
into TABLE KURS.DUMMY2
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(NACHNAME,VORNAME)

Offline Backup:

shutdown immediate
copy files /oradata/.../

Online Backup RMAN: RMAN> show all;

RMAN configuration parameters for database with db_unique_name DB11G are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_DB11G.f'; # default

Control-File-Sicherung (nicht RMAN-Katalog) - Autobackup von OFF auf ON

shutdown immediate startup mount alter database archivelog; alter database open;

RMAN> connect target

run { allocate channel dev1 type disk; backup full database include current controlfile format '/oracle/backup/db11g/%d_DBF_ONLINE_s%s_p%p_t%t' tag='ONLINE_FULL_BACKUP' filesperset=2; release channel dev1; }

RMAN> sql “alter system switch logfile”; SQL-Anweisung: alter system switch logfile

RMAN> run { 2> allocate channel dev1 type disk; 3> backup archivelog all not backed up 2 times 4> tag = 'ARCH_BACKUP' 5> format='D:\backup\Kurs1\%d_s%s_p%p_t%t'; 6> release channel dev1; 7> }

RMAN> run { 2> allocate channel dev1 type disk; 3> backup archivelog all delete input 4> tag = 'ARCH_DEL_BACKUP' 5> format='D:\backup\Kurs1\%d_DARCH_s%s_p%p_t%t'; 6> release channel dev1; 7> }

RECOVERY:

RMAN> connect target

Bei Zieldatenbank angemeldet: KURS1 (DBID=3090761539, nicht ge÷ffnet)

RMAN> restore database;

Starten restore um 30.09.11 Kontrolldatei der Zieldatenbank wird anstelle des Recovery-Katalogs verwendet Zugewiesener Kanal: ORA_DISK_1 Kanal ORA_DISK_1: SID=133 Device-Typ=DISK

restore redo alter database open; → error