Articles
Windows FAQ
"ucmapi.exe" Process on Windows 7 - What is the "ucmapi.exe" pro...
"FMAPP.exe" Process on Windows 7 - What is the "FMAPP.exe" proce...
"soffice.exe" Process on Windows 7 - What is the "soffice.exe" p...
"Pulse.exe" Process on Windows 7 - What is the "Pulse.exe" proce...
"jusched.exe" Process on Windows 7 - What is the "jusched.exe" p...
Who's Online
11 user(s) are online (3 user(s) are browsing Forum)

Members: 0
Guests: 11

more...

Browsing this Thread:   1 Anonymous Users





important dba queries
Home away from home
Joined:
2007/11/4 20:11
Group:
Registered Users
Posts: 334
Level : 17; EXP : 7
HP : 0 / 401
MP : 111 / 10827
Offline

How do you know the database name from normal user(connected) in the database:-. view plainprint?

SQL> conn system/manager

Database Details
SELECT * FROM v$database

Instance Details
SELECT * FROM v$instance

License Details
SELECT * FROM v$license

Version Details
SELECT * FROM v$version

Release Details
SELECT * FROM apps.fnd_product_groups

Patch Details
SELECT * FROM ad_applied_patches
SELECT * FROM ad_bugs

Concurrent Manager
SELECT concurrent_queue_name,
user_concurrent_queue_name,
description,
enabled_flag
FROM apps.fnd_concurrent_queues_vl fcq
WHERE user_concurrent_queue_name LIKE ‘AA%’

Partitioning Installed

SELECT DECODE (COUNT (*), 0, ‘No’, ‘Yes’) partitioning
FROM (SELECT 1
FROM dba_part_tables
WHERE owner NOT IN (‘SYSMAN’, ‘SH’, ‘SYS’, ‘SYSTEM’) AND ROWNUM = 1);

Spatial Installed
SELECT DECODE (COUNT (*), 0, ‘No’, ‘Yes’) spatial
FROM (SELECT 1
FROM all_sdo_geom_metadata
WHERE ROWNUM = 1);

RAC Installed
SELECT DECODE (COUNT (*), 0, ‘No’, ‘Yes’) rac
FROM (SELECT 1
FROM v$active_instances
WHERE ROWNUM = 1);

Unix Product Top Value
SELECT variable_name, value
FROM apps.fnd_env_context
WHERE variable_name = ‘AP_TOP’
AND concurrent_process_id =
(SELECT MAX (concurrent_process_id) FROM apps.fnd_env_context);

Command to Kill Session for Releasing Lock
ALTER SYSTEM KILL SESSION ‘(sid, serial#)’;

Create Temporary Table space
create tablespace mynew tempfile ‘/star/oradata/STARDEV/droptemp.ora’ size 1000m;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE drop1temp;

Drop Table space with all data
DROP TABLESPACE STA01T INCLUDING CONTENTS AND DATAFILES;

Increasing the size of the temporary tablespace:

CREATE TEMPORARY TABLESPACE STA01T TEMPFILE ‘/stage/oradata/STARTST/STA01.dbf’ SIZE 6000M;

Add a Temporary file in Database
ALTER TABLESPACE STA01T ADD TEMPFILE ‘/stage/oradata/STARTST/star01t.dbf’ SIZE 5000M;

Shutdown normal database and start the Database for changes to take effect.
SQL>Shut immediate;
SQL>startup

Check Datafile size and Tempfile Sizes.

select file_name||’ ‘||tablespace_name||’ ‘||(bytes)/1024/1024 from dba_data_files;
select file_name||’ ‘||tablespace_name||’ ‘||(bytes)/1024/1024 from dba_temp_files;

USE IN ORACLE 11g ony to know all temporay tablespaces details:
select tablespace_name,
tablespace_size/1024/1024 “Total Space”,
allocated_space/1024/1024 “Alloc Space”,
free_space/1024/1024 “Free Space”
from dba_temp_free_space;

SELECT tablespace_name, SUM(bytes_used)/1024/1024, SUM(bytes_free)/1024/1024
FROM V$temp_space_header
GROUP BY tablespace_name;

Default temporary tablespace
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_TEMP_TABLESPACE’;

Check Default Tablespace and Temporary Tablespace
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like ‘%DEFAULT%’;

Check Default Tablespace & Temporary Tablespace for User
select username,temporary_tablespace,default_tablespace from dba_users where username=’STARREP’;

Check Tablespace size from Query
SQL>set linesize 1000

SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) – (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
uni-on
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name;

Check Table space Usage
SELECT F.TABLESPACE_NAME,TO_CHAR ((T.TOTAL_SPACE – F.FREE_SPACE),’999,999′) “USEDMB”,
TO_CHAR (F.FREE_SPACE, ‘999,999’) “FREEMB”,
TO_CHAR (T.TOTAL_SPACE, ‘999,999’) “TOTALMB”,
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),’999′)||’ %’ FREE
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = ‘db_block_size’)/1024) ) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;

Resizing Tablespace without adding datafile:
ALTER DATABASE DATAFILE ‘/work/oradata/STARTST/STAR02D.dbf’ resize 2000M;

Checking autoextend on/off for Tablespaces:
select substr(file_name,1,50), AUTOEXTENSIBLE from dba_data_files
(OR)
SQL> select tablespace_name,AUTOEXTENSIBLE from dba_data_files;

Adding datafile to a tablespace:
alter tablespace star02D add datafile ‘/work/oradata/STARTST/sta05d.dbf’ size 1000M autoextend off;

Increasing Datafile size:
Alter Database datafile ‘/u01/app/Test1_data_01.dbf’ resize 2G;

Check The Default Tablespace And Default Temp Tablespace For All Users
SQL>set linesize 1000
SQL> select default_tablespace,temporary_tablespace,username from dba_users;

Check Undo Usage Details
SELECT TO_CHAR(s.sid)||’,’||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, ‘None’) orauser,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||’K’ “Undo”
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = ‘db_block_size’;

Check Who Is Using a Temp Segment
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||’M’ “SIZE”,
a.sid||’,’||a.serial# SID_SERIAL,
a.username,
a.program

Posted on: 2015/12/9 18:00
Transfer the post to other applications Transfer






You can view topic.
You cannot start a new topic.
You cannot reply to posts.
You cannot edit your posts.
You cannot delete your posts.
You cannot add new polls.
You cannot vote in polls.
You cannot attach files to posts.
You cannot post without approval.

[Advanced Search]


Copyright (c) 2015 FYIcenter.com
Search
Main Menu
Login
Username:

Password:

Remember me



Lost Password?

Register now!