Basic SQLs

Random Basic SQLs for DBA Usage

Create primary key after unique index

CREATE UNIQUE INDEX "SchemaName"."PK_INVPART" ON "Schema"."INVENTORY_PART" ("INVENTORY_ID", "SOURCE_INSTANCE")

TABLESPACE "SMAX" ONLINE;

alter table SchemaName.SEAEDI_INVENTORY_PART add primary key( "INVENTORY_ID", "SOURCE_INSTANCE") USING INDEX;

Kill active session

--find with the gid --

select * from v$session where osuser like '%username_id%'

-- kill the current session with SID , serial

exec kill_mysession(1057,57557); (procedure wrote by seagate programmer)

Sql -- Alter system kill session ‘SID, serial’;

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' FROM v$session where username ='schema_name';

alter system disconnect session '1042,54569' immediate;

Kill session in OS level

Check SID and serial

select a.sid, a.serial#,

from v$session a, v$locked_object b, dba_objects c

where b.object_id = c.object_id

and a.sid = b.session_id

and OBJECT_NAME='MEDIA_MDW_FACT';

select a.username, b.spid from v$session a, v$process b where a.paddr = b.addr and a.username='ITDBA';

Check pid with sid

SELECT p.program, p.spid

FROM v$session s, v$process p

WHERE s.paddr = p.addr

AND s.sid IN (3491)

-- (lets say results pid is 187445)

Go to OS, putty, ps -ef|grep 187445

Then kill -9 187445

Change database user passwords

alter user "434714" identified by SeaHate123;

Check user expired account

select * from dba_users where USERNAME = '527794';

Unlock user

ALTER USER APPLSYSPUB ACCOUNT UNLOCK;

Testing user login by set schema

SQL> alter session set current_schema="414272";

Session altered.

SQL> select count(*) from STAGE.RMA_SERIAL_STG;

Data insertion

alter session enable parallel dml;

alter session enable parallel query;

alter session set db_file_multiblock_read_count = 255;

insert /*+APPEND*/into drive.PartitionName partition (P20171005) select /*+parallel(a,16) full(a)*/* from drive.P2109_DFS_BER_STATS_FACT partition (P20171005) a; commit;

insert /*+APPEND*/into drive.PartitionName partition (P20171006) select /*+parallel(a,16) full(a)*/* from drive.P2109_DFS_BER_STATS_FACT partition (P20171006) a; commit;

EDW database maintainance

Update table delete param and set parallel_req to 8;

UPDATE itdba.delete_param set parallel_req=8 where table_name='Table_Name'; commit;

Check datafile autoextensible

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files where TABLESPACE_NAME like 'TableSpace_Name';

Gathering table statistics

-- To check whether the statistic has locked--

select STATTYPE_LOCKED from dba_tab_statistics where table_name='Table_Name';

select * from dba_tab_statistics where table_name='Table_Name';

-- Run gather statistic --

exec dbms_stats.gather_table_stats('DRIVE','Table_Name');

Check DB Link

select * from dba_db_links where DB_LINK='DL_Name';

select * from dual@DL_Name;

drop public database link DL_Name;

Alter session close database link DL_Name;

create public database link DL_Name connect to "SMAREAD" identified by "Password" using 'TNSName';

Create from the source db
Schema name , password and dbname from host destination
password need to readpwd from destination host

Add tablespace for ASM

https://docs.google.com/document/d/1KKEs-pWx_B_gRlVkiD7uNAYst7c9d9BqcTA-zWuxIyg/edit

CREATE TABLESPACE TEST_DAT DATAFILE ‘+DATA’;

select * from dba_Data_files where TABLESPACE_NAME like 'TableSpace_Name';

SELECT

a.tablespace_name,

ROUND((a.used_space * b.block_size) / 1048576, 2) AS "Used space (MB)",

ROUND((a.tablespace_size * b.block_size) / 1048576, 2) AS "Tablespace size (MB)",

ROUND(a.used_percent, 2) AS "Used %"

FROM dba_tablespace_usage_metrics a

JOIN dba_tablespaces b ON a.tablespace_name = b.tablespace_name

select name from v$asm_diskgroup;

select *

from dba_data_files

where tablespace_name like 'GLSTG_D';

desc dba_data_files;

create tablespace SCPDATA_FPG_DATA datafile '+DATA' size 2G;

ALTER TABLESPACE SCPDATA_FPG_DATA DROP DATAFILE '+DATA/OKDSCP1/DATAFILE/scpdata_fpg_data.842.982201113';

Drop tablespace SCPDATA_FPG_DATA including contents and datafiles;

alter tablespace GLSTG_D add datafile '+DATA' size 3000M;

select file_name, bytes

from dba_data_files

where tablespace_name like 'GLSTG_D'

alter database datafile '+DATA/OKTEBI1/DATAFILE/glstg_d.2403.972448099' resize 3000M;

select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

select file_name||' '|| bytes/1024/1024||' '|| autoextensible||' '|| maxbytes/1024/1024 from dba_data_files where tablespace_name=upper('GLSTG_D');

SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;

Add normal tablespace

alter tablespace POCCLARK_D_A01 add datafile '+DATA' size 32767M;

DB profiles and user sessions

select * from dba_users where USERNAME = 'QISSERVWARR' ; -- check user details

select username, profile from dba_users where username='EDW_EBO'; -- check user profile

select * from dba_profiles where profile='APP_20_PROF'; -- check profile setting

alter profile APP_40_PROF LIMIT IDLE_TIME 480; -- sample to change profile setting

show parameter resource_limit; -- should set to true to enable the session limit

ALTER PROFILE APP_20_PROF LIMIT SESSIONS_PER_USER 100; -- sample to change limit at profile level

ALTER USER QISSERVWARR PROFILE APP_40_PROF; -- change user profile

select count(*) from gv$session where USERNAME = 'QISSERVWARR'; -- count current number

select username, sid, serial#, status, LOGON_TIME from v$session where username ='EDW_EBO' order by 5; -- check current number details

select count(*) from gv$session where USERNAME = 'QISSERVWARR' and status='INACTIVE'; -- check inactive sessions

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session where username ='EDW_EBO' and STATUS='INACTIVE'; -- kill inactive sessions immediately

Tablespace size for ASM

--check tablespace size --

SELECT

a.tablespace_name,

ROUND((a.used_space * b.block_size) / 1048576, 2) AS "Used space (MB)",

ROUND((a.tablespace_size * b.block_size) / 1048576, 2) AS "Tablespace size (MB)",

ROUND(a.used_percent, 2) AS "Used %"

FROM dba_tablespace_usage_metrics a

JOIN dba_tablespaces b ON a.tablespace_name = b.tablespace_name

-- check current datafile--

select file_name, bytes

from dba_data_files

where tablespace_name like 'SIB_DATA'

-- add datafile --

alter tablespace BOOMI_SCP1_APPS_DATA

add datafile '+DATA' size 100M;

-- resize datafile --

alter database datafile '+DATA/oktedw1/datafile/sib_data.1365.973829621' resize 3000M;

Change time format

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Materialized View

select * from all_snapshots where owner='WEBFACTS';

alter session set nls_date_format = 'DD-MON-YYYY HH:MI:SS';

select sysdate from dual;

select * from dba_mviews where owner='WEBFACTS';

select count(*) from WEBFACTS.QPM_PRODUCT_DIM_MV;

select * from WEBFACTS.QPM_PRODUCT_DIM_MV;

execute dbms_mview.refresh('QPM_PRODUCT_DIM_MV','f');

execute dbms_mview.refresh('QPM_PRODUCT_DIM_MV');

Check DBA tables that belong to a Schema

desc dba_tables

select distinct OWNER from dba_tables;

select OWNER,TABLE_NAME,LOGGING from dba_tables where OWNER='QISAPPS';

Set no logging

select table_name, partitioned, logging from all_tables

where owner = 'HLMSTG'

and (table_name like '%STG' or table_name like '%STAGE'

or table_name like '%stage')

and (logging = 'YES')

;

ASM disk size

https://docs.google.com/document/d/1SGSnhhcaJ3_7e5Id1Lmr544TXiAbt_xdvrUT9TgVFGo/edit?ts=5af55980

Check partitions

desc DBA_TAB_PARTITIONS

select * from DBA_TAB_PARTITIONS where TABLE_NAME='P172_AFH_ADAPTS_SUMMARY_FACT' order by PARTITION_NAME desc

select max(PARTITION_NAME) from DBA_TAB_PARTITIONS where TABLE_NAME='P172_AFH_DH_WORKING_ADAPT_FACT';

Add partition

ALTER TABLE DRIVE.P172_AFH_ADAPTS_SUMMARY_FACT add PARTITION "P20180509" VALUES LESS THAN (20180509) TABLESPACE "DRIVE_FACT_DP_A26";

alter table drive.P172_AFH_DH_WORKING_ADAPT_FACT add partition P20180510 values less than (20180510) tablespace DRIVE_FACT_DP_A22;

https://docs.google.com/document/d/1SqicKcHepMS2Ighitch5qzLcjH8PGORxLh3Qofr-mu8/edit

Drop partition

ALTER TABLE SCPDATA.ICS_PART DROP PARTITION ICS_2015Q3 UPDATE GLOBAL INDEXES;

select TABLE_OWNER, PARTITION_NAME,LAST_ANALYZED,NUM_ROWS,BLOCKS,HIGH_VALUE from DBA_TAB_PARTITIONS where TABLE_NAME='PP_RMS_API_AUDIT' order by PARTITION_NAME desc

Split Partitions

alter table SMADBA.PP_RMS_API_AUDIT SPLIT PARTITION APIAUDIT_MAXPART

AT (TO_DATE('2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

INTO ( PARTITION APIAUDIT_2016_12,

PARTITION APIAUDIT_MAXPART);

exec dbms_stats.gather_table_stats('SMADBA','PP_RMS_API_AUDIT');

Set table retention

desc DELETE_PARAM;

desc event_log;

select OWNER,TABLE_NAME,FLIP_TYPE,DURATION,EXTRA_PARTITIONS from DELETE_PARAM where OWNER='STAGE' AND TABLE_NAME='STACK_ATTR_STG';

select * from DELETE_PARAM where OWNER='STAGE' AND TABLE_NAME='STACK_ATTR_STG';

select * from event_log where OWNER='STAGE' AND TABLE_NAME='STACK_ATTR_STG';

ALTER TABLE STAGE.STACK_ATTR_STG ADD PARTITION P20180520 VALUES LESS THAN ( TO_DATE( '20-MAY-2018', 'DD-MON-YYYY') ) TABLESPACE FACT_DP_A21;

select max(PARTITION_NAME) from DBA_TAB_PARTITIONS where TABLE_NAME='STACK_ATTR_STG';

select * from DBA_TAB_PARTITIONS where TABLE_NAME='STACK_ATTR_STG' order by PARTITION_NAME desc

UPDATE DELETE_PARAM SET DURATION=14 where OWNER='STAGE' AND TABLE_NAME='STACK_ATTR_STG';

UPDATE DELETE_PARAM SET EXTRA_PARTITIONS=14 where OWNER='STAGE' AND TABLE_NAME='STACK_ATTR_STG';

-- to keep data from not deleted by flip job

-- enabled_flag set to KEEP once confirm can set back to YES.

-- HR Table

select * from CBSHRDW_INTERFACE.HRDH_EHS_INCIDENT_DATA where GLOBAL_ID='512934';

Grant role privileges

select * from dba_users where USERNAME = '414272';

desc dba_role_privs;

select * from dba_role_privs where GRANTEE = '437551';

select * from dba_role_privs where GRANTEE = '526598';

GRANT TECHMAN_DATA_ROLE to "526598";

alter user "526598" default role ALL;

Table privileges

select * from dba_tab_privs where table_name='CURRENCY_EXCHANGE_RATE_LKP'

select * from dba_tab_privs where GRANTEE = 'SMA_SFDC_ROLE';

Sys privileges

select * from dba_sys_privs where GRANTEE = 'SMA_ROLE';

select DISTINCT GRANTEE,PRIVILEGE from dba_sys_privs where PRIVILEGE='CREATE PROCEDURE';

select * from dba_sys_privs where GRANTEE = 'DIM_TABLES_ROLE';

Rebuild index

alter index SCPDATA.PK_ICS_PART_DOCNO rebuild online parallel 63 nologging;

Rebuild unusable indexes

1. Checking index related to the partitioned table

select distinct(index_name) from dba_indexes where table_name='POS_PP_INVENTORY' and owner = 'SMADBA';

--POS_PP_INVENTORY_PK

2. Checking status of unusable indexes

select status, count(1) from dba_ind_partitions where index_owner = 'SMADBA' and index_name = 'POS_PP_INVENTORY_PK' group by status;

USABLE 34

UNUSABLE 5

3. Execute the PL/SQL to rebuild partition indexes.

declare

cursor main is

SELECT 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' tablespace '||tablespace_name||' parallel(degree 24) online' s

FROM ALL_IND_PARTITIONS

WHERE status = 'UNUSABLE'

and index_owner = 'SMADBA'

AND INDEX_NAME = 'POS_PP_INVENTORY_PK'

order by 1;

begin

for m in main loop

execute immediate m.s;

end loop;

end;

After this, you can execute statement 2. for verification on the status.

Check user last password change

SELECT

name,

ctime,

ptime

FROM

sys.user$

WHERE

name = 'SMADATA';

User last login audit

select to_char(timestamp,'DD MON YYYY hh24:mi') logon_time,

to_char(logoff_time,'DD MON YYYY hh24:mi') logoff

from dba_audit_session where USERNAME='SMADATA';

Check table/view owner

select * from all_tables where upper(table_name) = 'MN_DISTRIBUTOR_WAREHOUSE_VW';

select * from all_views where upper(view_name) = 'MN_DISTRIBUTOR_WAREHOUSE_VW';

Check current running sessions and sql text

select username,SQL_ID, sid, serial#, status, LOGON_TIME,machine from v$session where username ='SFDCEDW' order by LOGON_TIME;

select sesion.sid,

sql_text

from v$sqltext sqltext, v$session sesion

where sesion.sql_hash_value = sqltext.hash_value

and sesion.sql_address = sqltext.address

and sesion.username is not null

and sesion.sid=4192

order by sqltext.piece

;

SELECT SQL_ID,SQL_TEXT,FIRST_LOAD_TIME,EXECUTIONS,LAST_ACTIVE_TIME,

ELAPSED_TIME,CPU_TIME,APPLICATION_WAIT_TIME,CONCURRENCY_WAIT_TIME,

CLUSTER_WAIT_TIME,USER_IO_WAIT_TIME,PLSQL_EXEC_TIME,JAVA_EXEC_TIME,

ROWS_PROCESSED FROM V$SQL where SQL_TEXT LIKE '%SALES_PROGRAM_DIM%';

SELECT SQL_ID,SQL_TEXT,FIRST_LOAD_TIME,EXECUTIONS,LAST_ACTIVE_TIME,

ELAPSED_TIME,ROWS_PROCESSED,CPU_TIME,APPLICATION_WAIT_TIME,USER_IO_WAIT_TIME

FROM V$SQL where SQL_ID='cg5ksfdnpfxu7';

select * from v$sqltext_with_newlines where SQL_ID='bunvx480ynf57' order by PIECE;

Tablespace quotas

ORA-01950: no privileges on tablespace '

SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS

WHERE GRANTEE = 'EDWLOAD' --- check user has which privilegs

select * from dba_users where username='EDWLOAD';

select * from dba_ts_quotas where TABLESPACE_NAME='FACT_DP_A10'; -- check privileges granted on the specific tablespace.

alter user DRIVE quota unlimited on FACT_DP_A10;

alter user FACTS quota unlimited on FACT_DP_A10;

alter user STAGE quota unlimited on FACT_DP_A10;

alter user SLIDER quota unlimited on FACT_DP_A10;

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

For Audit login logout

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

desc dba_audit_session

select to_char(timestamp,'DD MON YYYY hh24:mi') logon_time,

to_char(logoff_time,'DD MON YYYY hh24:mi') logoff

from dba_audit_session where USERNAME='651682';

select OS_USERNAME,USERNAME,ACTION_NAME,SESSIONID,

to_char(timestamp,'DD MON YYYY hh24:mi') logon_time,

to_char(logoff_time,'DD MON YYYY hh24:mi') logoff

from dba_audit_session where OS_USERNAME='oper' order by logon_time desc ;

select OS_USERNAME,USERNAME,ACTION_NAME,SESSIONID,

to_char(timestamp,'DD MON YYYY hh24:mi') logon_time,

to_char(logoff_time,'DD MON YYYY hh24:mi') logoff

from dba_audit_session where OS_USERNAME='651682';

select * from dba_audit_session where OS_USERNAME='oper' and USERNAME='651682';

select * from dba_audit_session where OS_USERNAME='oper' and USERNAME='534963';

select * from dba_audit_session where OS_USERNAME='oper' and USERHOST='okmdev1135' and username <> 'SYSTEM' order by TIMESTAMP desc;

Issues collection:

Date: 10 Jan 2019

Prob: User claimed that the view is showing 'ORA-00942: table or view does not exist' error

Resolution : Grant and rebuild view

CREATE OR REPLACE VIEW User1.NewView (Column1) AS SELECT DISTINCT Column1 FROM User2.Table

GRANT SELECT ON user2.table TO user1

GRANT SELECT ON SMADBA.ICS_PART TO SMADTS

CREATE OR REPLACE VIEW SMADTS.INVOICE_CREDIT_SHIPMENT

AS SELECT * FROM SMADBA.ICS_PART PARTITION (ICS_CURRNEXT)

UNION ALL SELECT * FROM SMADBA.ICS_PART PARTITION (ICS_2019Q1)

UNION ALL SELECT * FROM SMADBA.ICS_PART PARTITION (ICS_2018Q4)

UNION ALL SELECT * FROM SMADBA.ICS_PART PARTITION (ICS_2018Q3)

UNION ALL SELECT * FROM SMADBA.ICS_PART PARTITION (ICS_2018Q2)

UNION ALL SELECT * FROM SMADBA.ICS_PART PARTITION (ICS_2018Q1)

UNION ALL SELECT * FROM SMADBA.ICS_PART PARTITION (ICS_2017Q4)

UNION ALL SELECT * FROM SMADBA.ICS_PART PARTITION (ICS_2017Q3);

Others

Change MTU

Check MTU by command ifconfig
View /etc/sysconfig/network-scripts/ifcfg-eth0

IPADDR=10.9.112.48

NETMASK=255.255.252.0

BOOTPROTO=static

ONBOOT=yes

DEVICE=eth0

USERCTL=no

PEERDNS=no

#MTU=8900

MTU=1500

Linux add usergroup

[root@okmprd1134 etc]# sudo groupadd appwd

[root@okmprd1134 etc]# usermod -a -G appwd edwfinha

[root@okmprd1134 etc]# usermod -a -G appwd boomi_sftp

dba_datapump_jobs

desc dba_datapump_jobs

SELECT owner_name, job_name, rtrim(operation) "OPERATION",

rtrim(job_mode) "JOB_MODE", state, attached_sessions

FROM dba_datapump_jobs

WHERE job_name NOT LIKE 'BIN$%' and owner_name='CSO';

Google Sites

Report abuse