v$ tables perf, create user , pfile

todays date SELECT SYSDATE FROM DUAL
Oracle provides CPU statistics in V$SQL
check V$SESS_TIME_MODEL for database CPU usage
buffer gets at the database level; check V$SESSTAT and V$SQL
wait events listed in V$SESSION_WAIT
V$ACTIVE_SESSION_HISTORY view contains a sampled history of session activity

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

SELECT
/*+
first_ro
*/
s.module
FROM
v$sessio
WHERE
s.sql_ad
'HOST' )
ORDER BY
t.piece#


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

alter user hr acount unlock; /unlock etme
alter user hr identified by hr; /şifre koyma

sqlplus hr/hr@fer

select table_name from tabs;
desc departments;
select * from departments;
create table test (id int, name varchar2(20));
inser into test values(1, 'test1');
exp system/oracle@orcl file=c:\exp_hr.dmp owner=hr
possible varsa türkçe karakterler gidiyor.
hlm\software\oracle\key_OraDb10g_home1\ NLS_LANG sunucudaki ile aynı olacak.
nvarchar2 varchar2 den daha fazla dil destekler


select user from dual; /dual table

shutdwon immediate / db kapatıldı
startup /db aç

sqlplus-
ORA-12514 - listener.ora
(SID_DESC =
(SID_NAME = fer)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(GLOBAL_DBNAME = fer)


lsnrctl status
lsnrctl stop
lsnrctl start

emctl status dbconsole
emca -config dbcontrol db -repos create
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos recreate



sqlplus sys/sys@oracle as sysdba
tnsping fer

select a+nvl(b,0) from table

set pagesize 10000
select ...

select count(*) from table
where rownum<=1 -kayıt var mı?


select sysdate from dual -bir tane donuyor

--restricted
SELECT LOGINS FROM V$INSTANCE
SELECT b.grantee, a.grantee || '(Role)' AS granted
FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = 'RESTRICTED SESSION'
AND a.grantee = b.granted_role
UNION
SELECT b.username, 'User (Direct)'
FROM dba_sys_privs a, dba_users b
WHERE a.privilege = 'RESTRICTED SESSION'
AND a.grantee = b.username;

GRANT RESTRICTED SESSION TO DB_USER;
REVOKE RESTRICTED SESSION FROM DB_USER;
ALTER SYSTEM ENABLE RESTRICTED SESSION;

SELECT 'ALTER SYSTEM KILL SESSION''' || sid || ', ' || serial#|| ''' IMMEDIATE; '
FROM V$SESSION WHERE TYPE!= 'BACKGROUND' AND USERNAME NOT IN
( SELECT b.grantee FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = 'RESTRICTED SESSION' AND a.grantee = b.granted_role
UNION
SELECT b.username FROM dba_sys_privs a, dba_users b
WHERE a.privilege = 'RESTRICTED SESSION' AND a.grantee = b.username );
ALTER SYSTEM DISABLE RESTRICTED SESSION;

SHUTDOWN IMMEDIATE
SHUTDOWN ABORT hard crash gibi

ALTER SYSTEM CHECKPOINT; - ALTER SYSTEM SWITCH LOGFILE;
SHUTDOWN ABORT;
STARTUP RESTRICT;
SHUTDOWN IMMEDAITE;

STARTUP NOMOUNT
STARTUP MOUNT
STARTUP
STARTUP RESTRICT
STARTUP MIGRATE
STARTUP FORCE

COLUMN PLATFORM_NAME FORMAT A32
SELECT * FROM V$TRANSPORTABLE_PLATFORM;
SQL> COLUMN tablespace_name FORMAT A20
SQL> COLUMN name FORMAT A50
SQL> select tablespace_name, name from v$datafile_header;

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(' USERS, DATA_TS', TRUE); -self contained
SELECT * FROM TRANSPORT_SET_VIOLATIONS; --self contained
no rows selected olmalı

show parameter job_queue_process;
desc v$parameter;
create pfile from spfile;
1. SPFILE dosyasından, metin formatında bir PFILE oluşturuyoruz:
SQL> create pfile='/tmp/deneme.text' from spfile;
2. PFILE dosyasını açıp, log_archive_dest parametresinde ilgili değişiklikleri yapıyoruz.
SQL> ! vi /tmp/deneme.text
...
3. Çalışmakta olan bir veritabanının spfile'ini ezemeyeceğimiz için veritabanını kapatıyoruz.
SQL> shutdown immediate;
4. Değişiklik yaptığımız pfile'i kullanarak yeni bir spfile oluşturuyoruz.
SQL> create spfile from pfile='/tmp/deneme.text';
5. Veritabanını açıyoruz.:
SQL> alter database open;

create spfile='/data2/spfile_yedek.ora'
from pfile='/tmp/deneme.text';
1. spfile.ora
2. spfile.ora
3. init.ora
4. init.ora
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';

STARTUP PFILE='/tmp/deneme.text';

SPFILE=/klasor/adresi/spfile_adi.ora
STARTUP PFILE='/tmp/calistir.text';

select ad||' '||soyad from table
where department.id is null -- boş mu







CREATE USER "HR" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCK
GRANT "CONNECT" TO "HR"

CREATE SMALLFILE TABLESPACE "HR_TS" DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\HR_TS' SIZE 10M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

ALTER USER "HR" DEFAULT TABLESPACE "HR_TS"
GRANT CREATE TABLE TO "HR"



ERR--CREATE TABLE "SYS"."USERS" ( "USER_ID" NUMBER(8), "USER_ROLE" VARCHAR2(10), "EMAIL" VARCHAR2(50), "FIRST_NAME" VARCHAR2(30), "LAST_NAME" VARCHAR2(30), "STREET_ADDRESS" VARCHAR2(40), "CITY" VARCHAR2(30), "STATE_PROVINCE" VARCHAR2(25), "POSTAL_CODE" VARCHAR2(12), "COUNTRY_ID" CHAR(2), CONSTRAINT "USR_PK" PRIMARY KEY ("USER_ID") VALIDATE , CONSTRAINT "USR_UK" UNIQUE ("EMAIL") VALIDATE )

REVOKE CREATE TABLE FROM "HR"
GRANT CREATE TABLE TO "HR" WITH ADMIN OPTION

GRANT ALTER TABLESPACE TO "HR" WITH ADMIN OPTION

REVOKE ALTER TABLESPACE FROM "HR"
REVOKE CREATE TABLE FROM "HR"
GRANT UNLIMITED TABLESPACE TO "HR"

GRANT "RESOURCE" TO "HR"
ALTER USER "HR" DEFAULT ROLE ALL

CREATE TABLE "HR"."USERS" ( "USER_ID" NUMBER(8), "USER_ROLE" VARCHAR2(10), "EMAIL" VARCHAR2(50), "FIRST_NAME" VARCHAR2(30), "LAST_NAME" VARCHAR2(30), "STREET_ADDRESS" VARCHAR2(40), "CITY" VARCHAR2(30), "STATE_PROVINCE" VARCHAR2(25), "POSTAL_CODE" VARCHAR2(12), "COUNTRY_ID" CHAR(2), CONSTRAINT "USR_PK" PRIMARY KEY ("USER_ID") VALIDATE , CONSTRAINT "USR_UK" UNIQUE ("EMAIL") VALIDATE )

CREATE TABLE "HR"."PRODUCTS" ( "PROD_ID" NUMBER(8), "NAME" VARCHAR2(50), "IMAGE" VARCHAR2(4000), "DESCRIPTION" VARCHAR2(4000), CONSTRAINT "PRD_PK" PRIMARY KEY ("PROD_ID") VALIDATE ) TABLESPACE "HR_TS"

CREATE TABLE "HR"."EXPERTISE_AREAS" ( "PROD_ID" NUMBER(8), "USER_ID" NUMBER(8), "EXPERTISE_LEVEL" VARCHAR2(30), "NOTES" VARCHAR2(4000), CONSTRAINT "EXA_PK" PRIMARY KEY ("PROD_ID", "USER_ID") VALIDATE , CONSTRAINT "EXA_PRD_FK" FOREIGN KEY ("PROD_ID") REFERENCES "HR"."PRODUCTS" ("PROD_ID") VALIDATE , CONSTRAINT "EXA_USR_FK" FOREIGN KEY ("USER_ID") REFERENCES "HR"."USERS" ("USER_ID") VALIDATE ) TABLESPACE "HR_TS"

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