-- view oracle internal parameters, note this can be run by SYS user -- name, value, default value, session modifiable, system modifiable, description select a.ksppinm name, b.ksppstvl value, b.ksppstdf deflt, decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD, decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD, a.ksppdesc description from sys.x$ksppi a, sys.x$ksppcv b where a.indx = b.indx and a.ksppinm like '\_%' escape '\' order by … Continue reading Internal parameters
Category: Scripts
How to extract the DB LINKS DDL with the password
Here is a simple script to extract the DB Links DDL with the encripted password. SET LONG 9000 -- to print the complete string SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM dba_db_links a;
Creation SQL script for DB Link within a database
The following can be used to create a script for DB Links with work on 8i/9i and will contain the password but not in 10g. The SQL script for creating the DB Link would need to be run the user who owns the DB Link if it's not a PUBLIC. SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link … Continue reading Creation SQL script for DB Link within a database
How to move datafiles in temp tablespace?
One can't move the temp tablespace during mount stage like other datafiles using "ALTER DATABASE RENAME FILE.." so a workaround this issue is to create a new temp tablespace. SQL> drop tablespace temp; drop tablespace temp * ERROR at line 1: ORA-12906: cannot drop default temporary tablespace -- create a new temp tablespace SQL> CREATE … Continue reading How to move datafiles in temp tablespace?
TEMP space usage overall and by session
Get current total TEMP tablespace currently allocated, used and free: SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, (SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# … Continue reading TEMP space usage overall and by session
Script to move Oracle Datafiles
Here is a nice script if you need to move your datafiles to a new location. SQL>spool rename_datafile.sql set line 140 set pagesize 2000 set heading off select 'alter database rename file ' ||''''|| file_name || '''' || ' to '|| ''''|| '/oracle/IMPRD/data' || substr(file_name, instr(file_name, '/', -1)) ||'''' || ';' from dba_data_files / SQL>spool … Continue reading Script to move Oracle Datafiles
Grant Full Access to all tables and views within a Schema
set serveroutput on set serveroutput on size 1000000 DECLARE vcount1 number := 0; vcount2 number := 0; v_tablename varchar2(32); v_viewname varchar2(32); vschema varchar2(30) := '&schema'; vrole varchar2(30) := '&role'; # -- vprivilege varchar2(30) := '&privilege'; BEGIN vschema := UPPER(vschema); vrole := UPPER(vrole); for ctable in ( select owner, table_name from dba_tables where owner like vschema … Continue reading Grant Full Access to all tables and views within a Schema
Check Oracle Password for Expiration
This simple script will check if a user password is expiring in the next 120 days. set pagesize 500 set linesize 200 set trimspool on column "EXPIRE DATE" format a20 select username as "USER NAME", expiry_date as "EXPIRE DATE", account_status from dba_users where expiry_date < sysdate+120 and account_status IN ( 'OPEN', 'EXPIRED(GRACE)' ) order by … Continue reading Check Oracle Password for Expiration
Archive log history by the hour
-- Print # of archive logs for yesterday by the hour -- can be used to determine to schedule archive logs backups, to view number of times log switches occurs set linesize 200 set trimspool on set feedback off Column 00 format 999 Column 01 format 999 Column 02 format 999 Column 03 format 999 … Continue reading Archive log history by the hour
How to find materialized view log entries?
We have faced a situation where replication with materialized view needs to be checked before the shutdown outage between source and destination. This is a little script where you can find if there is entries in the MV logs. declare --use dba_mview_logs for 9i and 10g cursor cur is select log_table from dba_snapshot_logs; num_rows number; … Continue reading How to find materialized view log entries?
How to find datafiles with same name?
Sometimes you want to check for datafiles with the same name in the database, maybe you are new to the env and have a major storage migration project or for any other reason. Here is a little script to check if the database has datafiles with the same name in different directories. set line 140 … Continue reading How to find datafiles with same name?
How to get Help from SQL*Plus
A lot of people actually ask me why sql*plus doesn't have a help feature like man in UNIX. Actually it does for all sql*plus command but not for the SQL language. Below I will show some help commands for SQL*Plus: SQL> ? HELP ---- Accesses this command line help system. Enter HELP INDEX or ? … Continue reading How to get Help from SQL*Plus
SQL*PLUS change buffer command
There are some cool SQL*PLUS command to help speed up your typing. Today I am showing the change and append buffer in the command line. C:\oraclexe\app\oracle\product\10.2.0\server\BIN>sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 23 23:04:08 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn / as sysdba Connected. SQL> #To change … Continue reading SQL*PLUS change buffer command
alexlima.com 