ORACLE kullanıcı bilgileri

çalışma süresi
set timing on
select * from dual;

-------------------
kullanıcılar hakkında bilgiler
SELECT username, created, PROFILE, default_tablespace, temporary_tablespace
FROM dba_users
ORDER BY username
desc dba_users;
---------------------------oracle alan hesaplaması
SELECT tablespace_name, SUM (BYTES) total_free_space,
MAX (BYTES) largest_free_extent
FROM dba_free_space
GROUP BY tablespace_name
-------------------------------DATABASE BAĞLI KULLANICILARIN ÇALIŞTIRDIĞI SORGULAR
SELECT /*+ ORDERED */
SUBSTR (RPAD (s.sql_text, 64), 1, 64) sqltxt
FROM v$session v, v$sqltext_with_newlines s
WHERE v.osuser = ' ferhan '
AND s.address = v.sql_address
AND s.hash_value = v.sql_hash_value
ORDER BY piece

Ayrıca hangi user ile bağlı olduğunu bulmak için

select sys_context('USERENV','OS_USER')
from dual;
---------------------------------------
SELECT owner, NAME, TYPE
FROM dba_source
------------------------------------------HER ZAMAN EL ALTINDA OLMASI GEREKENLERDEN BAZILARI

Server/OS Information

Server identification Representative Query

Host name where the instance is running -select host_name from v$instance;

Operating system platform - select platform_name from v$database –-(10g)

File Information

Oracle file locations - Representative Query

Control files - select name from v$controlfile;

Datafiles - select file_name from Dba_data_files;

Temp files - select file_name from Dba_temp_files;

Log files - select member from v$logfile;

Archived logs - select name from v$archived_log

Flash recovery area- select name from v$recovery_file_dest

Other points of access on the file system indicated by parameters - select * from v$parameter where value like '%/%' or value like '%/%';

Programmatic access to the file system - select directory_path from dba_directories

Process Information

Processor/Processes - Representative Query

Session Processes - select p.spid, s.username, s.program from v$process p, v$session s where p.addr=s.paddr order by 2, 3, 1

Processes related to parallelism - select slave_name, status from v$PQ_SLAVE

Memory Information

Memory- Representative Query

Program Global Area - select * from V$PGASTAT

System Global Area - select * from v$sga

--------------------- table space bilgileri
SELECT tablespace_name, initial_extent, next_extent, min_extents,
max_extents, pct_increase, status, CONTENTS
FROM dba_tablespaces
desc dba_tablespaces;
-------------VERİTABANINDAKİ DOSYALARIN YERİNİ BULMA
SELECT owner, table_name
FROM dba_tables dt
WHERE NOT EXISTS (
SELECT 'TRUE'
FROM dba_constraints dc
WHERE dc.table_name = dt.table_name
AND dc.constraint_type = 'P')
AND owner = 'HR'
ORDER BY owner, table_name

SELECT banner product_versions
FROM v$version

SELECT 'Archived Log Directory' "Filename", VALUE "Location"
FROM v$parameter
WHERE NAME = 'log_archive_dest'
UNION
SELECT 'Control Files' "Filename", VALUE "Location"
FROM v$parameter
WHERE NAME = 'control_files'
UNION
SELECT 'Datafile' "Filename", NAME "Location"
FROM v$datafile
UNION
SELECT 'LogFile Member' "Filename", MEMBER "Location"
FROM v$logfile

Burada

SELECT 'Datafile' "Filename", NAME "Location"
FROM v$datafile
where name like '%kriterim%'
ORDER BY tablespace_name
------------------------İNDEX BULUNAN TABLOLARIN LİSTESİ
İndex bulunan tabloların listesini aşağida script ile bulabiliriz. Ayrıca bu tabloda kaç adet index olduğunuda listeler.

SELECT table_owner, table_name, column_name, COUNT (*) index_count
FROM dba_ind_columns
WHERE table_owner = 'HR'
GROUP BY table_owner, table_name, column_name

İndex bulunmayan tabloların listesini aşağida script ile bulabiliriz.
SELECT owner, table_name
FROM (SELECT owner, table_name
FROM dba_tables
--Burada where kosulu sema sinirlandirmasi yapilabilir.
MINUS
SELECT table_owner, table_name
FROM dba_indexes) orasnap_noindex
WHERE owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, table_name

Ağaıdaki sorguda PK (birincil anahtar) olmayan tabloları döndürür.

SELECT owner, table_name
FROM dba_tables dt
WHERE NOT EXISTS (
SELECT 'TRUE'
FROM dba_constraints dc
WHERE dc.table_name = dt.table_name
AND dc.constraint_type = 'P')
AND owner = 'HR'
ORDER BY owner, table_name
Bu sorguda aşağıdaki kriterlere göre farklı aramalarda yapılabilir. Tercih sizin.

CONSTRAINT_TYPE VARCHAR2(1) Type of constraint definition:

* C (check constraint on a table)
* P (primary key)
* U (unique key)
* R (referential integrity)
* V (with check option, on a view)
* O (with read only, on a view)

SQL> desc all_constraints
----------------------------Hangi tablespace ne kadar alan kullanıyor
SELECT ts.tablespace_name, "File Count", TRUNC ("SIZE(MB)", 2) "Size(MB)",
TRUNC (fr."FREE(MB)", 2) "Free(MB)",
TRUNC ("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",
RPAD ('*',
TRUNC (CEIL ((fr."FREE(MB)" / df."SIZE(MB)") * 100) / 10),
'*'
) "Graph"
FROM (SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) "SIZE(MB)",
COUNT (*) "File Count",
SUM (maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name(+)
AND fr.tablespace_name = ts.tablespace_name(+)
ORDER BY "% Free"
cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> QUIT

Check the log files that were created by catbundle.sql. These log files are available in the following directory:

$ORACLE_HOME/cfgtoollogs/catbundle/
catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log
------------------------------
> $ORACLE_HOME/OPatch/opatch apply

> $ORACLE_HOME/OPatch/opatch rollback -id [CPU_No]

> $ORACLE_HOME/OPatch/opatch lsinventory
PATH=/oracle/BPQ/102_64/OPatch:$PATH

http://onlineappsdba.com/index.php/2007/06/16/upgrade-oracle-database-to-10202-soa-suite-install-part-ii/
--------------------------------------

OracleDatabase10g-OCP_Certification All-in-One Exam Guide.pdf
21-08.2009 chapter 1 Basic Oracle Concepts
02-09.2009 chapter 2 Installing Oracle Database 10gs 70

ADM100_-_2005-Q2_-_A4_-_SAP_Web_AS_Administration_I.pdf
21-08.2009 Unit 1 - Basics 130
http://www.ceturk.com/veri-tabani/oracle-high-availability-real-application-clustersrac-data-guard-flashback-video.html

http://www.mcs.csueastbay.edu/support/oracle/doc/10.2/server.102/b14210/architectures.htm#i1008376
http://books.google.com.tr/books?id=VrW4_e-BDg0C&pg=PA29&lpg=PA29&dq=Oracle+Streams&source=bl&ots=v6chqbB1ZX&sig=-6Y-1rb2lFm7joXUHpVKwG7SP0w&hl=tr&ei=WUONSrfcNqLUmgPPqZz9DQ&sa=X&oi=book_result&ct=result&resnum=6#v=onepage&q=&f=false

SELECT
NAME,
DETECTED_USAGES,
CURRENTLY_USED,
FIRST_USAGE_DATE
FROM
DBA_FEATURE_USAGE_STATISTICS
WHERE
VERSION = (SELECT VERSION FROM V$INSTANCE) AND
(DETECTED_USAGES > 0 OR CURRENTLY_USED != 'FALSE');
*-----------------------------------------------------
SELECT
obj.inst_id as inst_id, obj.TO_OWNER as owner,
obj.TO_NAME as table_name,
sum(ds.bytes/1024)/(max(sq.child_number)+1) as size_kb,
obj.to_type as type, ds.buffer_pool as bufferpool,
upper(substr(sq.sql_text,1,6)) as operation, sq.sql_text as sql_text,
sq.module as sql_module, sq.action as action,
sum(sq.executions) as executions,
CASE WHEN sum(sq.disk_reads)>sum(sq.buffer_gets) THEN 9999
ELSE decode(sum(sq.buffer_gets), 0, 0,
100*(1-(sum(sq.disk_reads)/sum(sq.buffer_gets))))
END as cache_hit_rat, sum(sq.disk_reads) as disk_reads, decode(sy.value, 0, 0,
sum(sq.disk_reads/sy.value)) as disk_read_rat,
sum(sq.buffer_gets) as buffer_gets,
decode(sum(sq.buffer_gets)+sum(sq.disk_reads), 0, 0,
sum(sq.buffer_gets)/(sum(sq.buffer_gets)+sum(sq.disk_reads))) as log_read_rat, sum(sq.rows_processed) as rows_proc,
decode(sum(sq.executions), 0, 0,
sum(sq.rows_processed)/sum(sq.executions)) as rows_per_exec,
decode(sum(sq.rows_processed), 0, 0,
sum(sq.buffer_gets)/sum(sq.rows_processed)) as buffgets_per_row,
sum(sq.sorts) as sorts, sum(sq.cpu_time/1000) as cpu_time,
sum(sq.users_opening) as user_open,
sum(sq.open_versions) as opening_vers,
sum(sq.loaded_versions) as load_vers,
max(sq.child_number)+1 as nb_childs, sum(sq.sharable_mem) as sh_mem,
sum(sq.persistent_mem) as pers_mem,
sum(sq.runtime_mem) as runtime_mem,
sum(sq.invalidations) as invalidations,
sum(sq.parse_calls) as parse_call
FROM
gv$sql sq, gv$object_dependency obj, dba_segments ds, gv$sysstat sy
WHERE
sq.address=obj.from_address and sq.hash_value=obj.from_hash and
obj.TO_NAME=ds.segment_name and sq.inst_id=obj.inst_id and sq.inst_id=
sy.inst_id and ds.segment_type='TABLE' and sy.name='physical reads'
GROUP BY
obj.inst_id, obj.to_owner, obj.to_name, obj.to_type, ds.buffer_pool,
sq.sql_text, sq.module, sq.action, sy.value
ORDER BY
obj.to_name,obj.inst_id, operation


---------------
select * from v$license;

select banner from v$version where BANNER like '%Edition%';

select decode(count(*), 0, 'No', 'Yes')
from dba_part_tables
where owner not in ('SYSMAN', 'SH', 'SYS', 'SYSTEM') and rownum = 1;

select decode(count(*), 0, 'No', 'Yes')
from all_sdo_geom_metadata where rownum = 1;

select decode(count(*), 0, 'No', 'Yes')
from v$active_instances where rownum <= 2;
-----------------------