Daily Archives: November 10, 2013
Oracle proseslerinde yaddash proseslerinin izlenmesi
/* Hansi RAC-lardan istifade olunub baxmaq uchun*/ $ olsnodes -n ——————— anartestdb 1 1 anartestdb 2 2 ———————– # ps -afe | grep ora_smon ————————————————————————- oracle 832118 1 0 […]
Generate AWR Reports
[oracle@BAKUANARDBDB dpdump]$ sqlplus “/ as sysdba”; SQL*Plus: Release 11.2.0.3.0 Production on Sun Nov 10 19:57:46 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> select name from v$database; NAME ——— […]
Size of the table
select segment_name, sum(bytes)/(1024*1024) as SIZE_IN_MB from dba_extents where segment_type = ‘TABLE’ and segment_name = ‘ANAR_TABLE’ group by segment_name;
Truncate multiple tables using PL/SQL block
BEGIN FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN (‘ANAR_TABLE1′,’ANAR_TABLE2′,’—‘ )) LOOP EXECUTE IMMEDIATE ‘TRUNCATE TABLE ‘ || i.table_name; END LOOP; END;
Generate DDL of the Tablespace
SQL> SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’,’USERS’) FROM dual; Result: CREATE TABLESPACE “USERS” DATAFILE SIZE 1073741824 AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
Find duplicate columns from all tables in a schema
SELECT column_name,data_type,data_length,nullable,table_name FROM dba_tab_columns WHERE column_name IN ( SELECT column_name FROM dba_tab_columns GROUP BY column_name HAVING COUNT(1) > 1 — more than one value ) and owner = ‘ANAR’ AND COLUMN_NAME LIKE ‘%TIME%’ ORDER BY column_name
OUI-35000: Fatal cluster error encountered while adding a node.
PROBLEM: OU1-35000: Fatal cluster error encountered (). Correct the problem and try the operation again. Solution: Occurs during installation or adding a node in RAC environment. Make sure the private node name or virtual hostnames are reachable. If they are not reachable, make sure they exist in DNS or /etc/hosts file Due to non availability […]
SQL JOIN
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. Tables in a database are often related to each other with keys. A primary key is a column (or a combination of columns) with a unique value for […]
ALTER TABLE Statement
The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table. Renaming a table The basic syntax for renaming a table is: ALTER TABLE table_name RENAME TO new_table_name; For example: ALTER TABLE suppliers RENAME TO vendors; This will rename […]
Find Lock Objects
Script1: SELECT a.object_name, a.object_type, a.status, b.locked_mode L_M, b.oracle_username USERNAME, b.session_id SID, b.process PID, ‘ALTER SYSTEM KILL SESSION ”’||s.sid||’,’||s.serial#||”’;’ kill_statement FROM DBA_OBJECTS A, V$LOCKED_OBJECT B, v$session s WHERE a.object_id = b.object_id AND b.session_id=s.sid; Script2: SELECT lo.oracle_username dbuser,lo.os_user_name os_user–,’ALTER SYSTEM KILL SESSION ”’ ||s1.sid||’,’||s1.serial#||”’;’ kill_statement FROM DBA_OBJECTS o, V$LOCKED_OBJECT lo, dba_waiters w, v$session s2–, v$session s1 WHERE […]
anargodjaev.wordpress.com 