Important DBA Script:
========================
====Extended Data Types in Oracle Database 12c Release 1 (12.1) ======
https://oracle-base.com/articles/12c/extended-data-types-12cR1
Login as "sys" user as sysdba role
conn / as sysdba
show parameter max_string_size
MAX_STRING_SIZE----> STANDARD
shutdown immediate;
startup upgrade;
ALTER SYSTEM SET max_string_size=extended;
@?/rdbms/admin/utl32k.sql
shutdown immediate;
startup;
showparameter MAX_STRING_SIZE
MAX_STRING_SIZE----> EXTENDED
======Change Oracle character set WE8MSWIN1252 to AL32UTF8 in 11g R2 and 12c ===
Dont forget to take full database backup before following below steps.
Login as: "sys" user
Check current character set
select * from nls_database_parameters ORDER BY PARAMETER;
NLS_CHARACTERSET----->WE8MSWIN1252
Target NLS_CHARACTERSET--->AL32UTF8
Stop the Database and startup in 'restrict' mode.
shutdown immediate;
startup restrict;
Now change to the desired character set.
alter database character set internal_use AL32UTF8;
alter database character set AL32UTF8;
Now stop the database and open as normal
shutdown immediate;
startup;
select * from nls_database_parameters ORDER BY PARAMETER;
NLS_CHARACTERSET-----> AL32UTF8
========= Kill datapump Export/Import ========
Identifying datapump jobs
Do a select from dba_datapump_jobs in sqlplus to get the job name:
> expdp system full=y
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING
Or when you use the JOB_NAME parameter when datapumping, you already identified the job with a name. You don’t need to look up afterwards…
expdp system full=y JOB_NAME=EXP_FULL
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM EXP_FULL EXPORT FULL EXECUTING
Killing or stopping a running datapump job
The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed!
When exporting (or importing), press Ctrl-c to show the datapump prompt and type KILL_JOB or STOP_JOB[=IMMEDIATE]. You will be prompted to confirm if you are sure…
Adding ‘=IMMEDIATE‘ to STOP_JOB will not finish currently running ‘sub-job’ and must be redone when starting it again.
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> KILL_JOB
..or..
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes
Resuming a stopped job
Identify your job with SQL or you already knew it because you used ‘JOB_NAME=‘ ;)
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM EXP_FULL EXPORT FULL NOT RUNNING
Now we can ATTACH to the job using it as a parameter to the expdp or impdp command, and a lot of gibberish is shown:
> expdp system ATTACH=EXP_FULL
Job: EXP_FULL
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: A5441357B472DFEEE040007F0100692A
Start Time: Thursday, 08 June, 2011 20:23:39
Mode: FULL
Instance: db1
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** full=y JOB_NAME=EXP_FULL
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /u01/app/oracle/admin/db1/dpdump/expdat.dmp
bytes written: 520,192
Worker 1 Status:
Process Name: DW00
State: UNDEFINED
(Re)start the job with START_JOB, use ‘=SKIP_CURRENT‘ if you want to skip the current job. To show progress again, type CONTINUE_CLIENT (Job will be restarted if idle).
Export> START_JOB[=SKIP_CURRENT]
Export> CONTINUE_CLIENT
Job EXP_FULL has been reopened at Thursday, 09 June, 2011 10:26
Restarting "SYSTEM"."EXP_FULL": system/******** full=y JOB_NAME=EXP_FULL
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Oracle: How to kill data pump jobs (Below example is for import (impdp) the same is applicable for export (expdp) )
When you import or export using data pump impdp or expdp tools, the import/export is done by a job. You have an option to provide a job name using JOB_NAME parameter too
Following sql will give you the list of data pump jobs
select * from dba_datapump_jobs;
If you want to kill your impdp or expdp
1) Make sure that your impdp/expdp command prompt window is active
2) Press Control-C , It will pause the job. Don’t press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background
3) Type Kill_Job
ex: Import>kill_job
Are you sure you wish to stop this job (y/n): y
If by mistake, you closed the window and your import/export job is still running,
1) Get the name of the job using
select * from dba_datapump_jobs
2) Open a new command prompt window. If you want to kill your import job type
impdp username/password@database attach=name_of_the_job (Get the name_of_the_job using the above query)
3) Once you are attached to job, Type Kill_Job
ex: Import>kill_job
Are you sure you wish to stop this job (y/n): y
And your job is killed, it will no longer show in dba_datapump_jobs
======== import key word for oracle by using imp command ======
imp system/sys123 file=HEMIS_2017_06_10.dmp log=imphemis.log fromuser=HEMIS
imp system/sys123 file=spagobi_foodmart_2017_06_10.dmp log=impspagobi.log fromuser=spagobi,foodmart
imp system/sys123 file=Faisal_12112014.dmp log=faisal.log fromuser=Faisal_hrm
======== Check tablespace in oracle =======
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by df.tablespace_name;
======== Check data file name wise size =======
set lines 100
col file_name format a70
select file_name,ceil(bytes / 1024 / 1024) "size MB",AUTOEXTENSIBLE
from dba_data_files;
========= Resize data file size =======
alter database datafile '/home/oracle/app/oracle/oradata/orcl/system01.dbf' resize 5g;
====== Table wise row count ========
select table_name,
to_number( extractvalue( xmltype( dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) count
from user_tables
order by table_name;
=== Remote machine DB connection string from sqlplus command prompt ======
sqlplus user/pwd@//IP:1521/orcl
== Displays CPU usage for each User. Useful to understand database load by user ====
SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.STATISTIC# = sn.STATISTIC#
AND NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID
AND ss.status = 'ACTIVE'
AND ss.username IS NOT NULL
ORDER BY VALUE DESC;
======= Drop Tablespace with datafile ===============
DROP TABLESPACE <TablespaceName> INCLUDING CONTENTS AND DATAFILES;
======== Export all tables without specific tables =========
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
expdp scott/tiger@dell schemas=SCOTT EXCLUDE=TABLE:\"IN\(\'BONUS\',\'SALGRADE\'\)\" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
============
GRANT READ, WRITE ON DIRECTORY test_dir TO system;
impdp system/sys123@dell directory=TEST_DIR remap_schema=scott:imam dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
====== only table import and export ==========
expdp scott/tiger@dell directory=TEST_DIR dumpfile=multi_table.dmp tables=scott.BONUS,scott.SALGRADE
impdp system/sys123@dell directory=TEST_DIR remap_schema=scott:imam dumpfile=multi_table.dmp logfile=impdpMULT.log
=======================================================
If you find an oracle process is consuming a high amount of CPU, you can find the sql_text that is related to that process id using the following code:
SELECT SQL_TEXT
FROM v$sql
WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (sELECT addr
FROM v$process
WHERE spid = '&process_id'));
You will need to find the process id using either top (linux), topas -P (aix), or task manager (windows). Equally you can use Enterprise Manager to find this information, by looking under the performance monitor and any alerts regarding tuning / high CPU consumption.
To disable gather_stats_job in 10g:
BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/
To disable gather_stats_job (also known as optimizer stats) in 11g:
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
If you wish to find the source code for a view, you can look in the user_views table:
SQL> SELECT view_name, text FROM user_views;
You can check the v$fixed_view_definition table to check the definition of fixed views (views starting with v$).
SQL> SELECT view_name, view_definition FROM v$fixed_view_definition;
Further Reading
To retrieve the instance name of the database instance you are currently connected to, you can perform a number of queries:
Non-public single-node query
SQL> SELECT name FROM v$instance;
INSTANCE_NAME
-----------------------
DEV
Non-public RAC node query
SQL> SELECT instance_name FROM gv$instance;
INSTANCE_NAME
-----------------------
DEV
Public query
SQL> SELECT sys_context('USERENV','DB_NAME') AS instance_name FROM dual;
INSTANCE_NAME
-----------------------
DEV
MMON (Memory Monitor) is an Oracle background process that gathers memory statistics (snapshots) and stores this information in the AWR.
MMON is also responsible for issuing alerts for metrics that exceed their thresholds.
MMON is available from Oracle 10g.
The default for recording an AWR snapshot is hourly.
SMON (system monitor) is the Oracle background process which performs instance recovery, cleans up after unclean shutdowns and coalesces adjacent free extents into larger free extents.
SMON wakes up every 5 minutes to perform housekeeping duties, and the database instance will terminate if SMON is not running.
You can check if SMON is running from the command line in Linux/Unix:
$ ps -ef | grep pmon
PMON is an Oracle background process created when you start the database instance. It is responsible for freeing up resources if a user process fails.
PMON normally wakes up every 3 seconds to perform housekeeping.
If PMON is not running then the database instance will terminate.
To speed up house-keeping you can wake-up PMON manually:
SQL> oradebug setmypid
SQL> oradebug wakeup 2
Sometimes it is necessary to do work on a database without any other users being logged in. It is possible to restrict the database session in such a case. When the database starts in restricted mode only users with restricted session privileges can get access to the database even though it is technically in open mode.
Enable / Disable Restricted Session
SQL> startup restrict ORACLE instance started.
Total System Global Area 504366872 bytes
Fixed Size 743192 bytes
Variable Size 285212672 bytes
Database Buffers 218103808 bytes
Redo Buffers 307200 bytes
Database mounted.
Database opened.
Startup the database in restricted mode
The alter system command can be used to put the database in and out of restricted session once it is open:
SQL> alter system enable restricted session;
system altered
SQL> alter system disable restricted session;
system altered
Find and disconnect users connected during restricted session
Any users connected to the database when restricted session is enabled will remain connected and need to be manually disconnected
To check which users are connected to the database run the following:
SQL> SELECT username, logon_time, process from v$session;
USERNAME LOGON_TIM PROCESS
-------- --------- -------
17-NOV-10 606252
17-NOV-10 598054
17-NOV-10 540690
17-NOV-10 421948
17-NOV-10 561182
17-NOV-10 512046
17-NOV-10 1257542
SYS 17-NOV-10 1310796
8 rows selected.
By querying the process id you can then issue a kill -9 <process_id> at the operating system level to disconnect the connected user. The blank usernames in v$session refer to background database processes.
Check if database in restricted mode
If you are unsure whether the database is in restricted session or not you can run the following query to check:
SQL> SELECT logins from v$instance;
LOGINS
----------
RESTRICTED
Opatch is an Oracle utility for the application and rolling back of interim patches to the Oracle RDBMS. In order to use opatch it is recommended to set its location in $PATH.
Location
OPatch can be found under $ORACLE_HOME/OPatch
Current Patch History
To find the current patch history run the following:
$ opatch lsinventory [ -all ] [ -i[nvPtrLoc] <Path to oraInst.loc> ][ -oh <ORACLE_HOME>]
-all
– list all patches currently applied.
OPatch Version
$ sh opatch version
Invoking OPatch 10.2.0.4.2
OPatch Version: 10.2.0.4.2
OPatch succeeded.
OPatch Apply
$ opatch apply [-f[orce]] [-i[nvPtrLoc]<path to oraInst.log] [-m[inimize_downtime]] [-n[o_inventory]] [-oh<ORACLE_HOME>] [<patch_location>]
-force
– used to remove any conflicting patches and force the patch to be applied
-invPtrLoc
– used to locate the oraInst.loc file
-minimize_downtime
– used only in RAC instances, allowing for the remaining instances to remain up during patching. It is the responsibility of the user to shutdown each instance before applying the patches, after the last instance is patched then all instances can be brought back up.
-no_inventory
– This only works if the inventory is unavailable and allows for bypassing the inventory for reading and updates.
-oh
– Directory to be used instead of the default $ORACLE_HOME
patch_location
– Where to install the patch from
OPatch Rollback
Sometimes it is necessary to rollback a patch once it has been applied.
$ opatch rollback -id patch_id [-i[nvPtrLoc]<Path to oraInst.loc>] [-oh <ORACLE_HOME>] -pj patch_dir
-id
– The id of the patch that requires rollback
-ph
– The directory that is a valid patch area.
One of the tasks a DBA undertakes is to monitor the amount of space consumed by the database. If a tablespace becomes spacebound than the database will freeze up as data cannot be written down to disk.
Check Tablespace Free Space
The below script will identify the available free space for each tablespace.
SELECT a.tablespace_name
, a.fileCount
, a.MaxTSBytes
, b.TSDataBytes
, a.MaxTSBytes - b.TSDataBytes FreeSpace
, ROUND((a.TotalFilesizeMB/a.MaxTSBytes)*100,2) DFAlloc
, ROUND((b.TSDataBytes/a.MaxTSBytes)*100,2) AS DATAAlloc
FROM
(SELECT tablespace_name
, COUNT(*) filecount
, SUM(CASE WHEN maxbytes > bytes THEN maxbytes/1048576 ELSE bytes/1048576 end) AS MaxTSBytes
, SUM(bytes)/1048576 TotalFilesizeMB
FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name
, SUM(bytes)/(1048576) AS TSDataBytes
FROM dba_extents GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY 1
/
TABLESPACE_NAME FILECOUNT MAXTSBYTES TSDATABYTES FREESPACE DFALLOC DATAALLOC
--------------- --------- ---------- ----------- --------- ------- ---------
INTERFACE 13 22250 21906.4375 343.5625 100 98.46
LOAD 6 8550 7629.125 920.875 100 89.23
MLOG 1 1250 617.5625 632.4375 100 49.41
NOTES 3 4200 3728.6875 471.3125 100 88.78
SYSTEM 1 500 406.625 93.375 100 81.33
UNDO 3 4500 323.085938 4176.91406 100 7.18
XDB 1 100 44.9375 55.0625 100 44.94
6 rows selected.
Query Datafile Sizes
The below script queries how much free space is available per datafile. In this example only the system tablespace has been queried:
SELECT a.file_id,
SUBSTR(a.tablespace_name,1,10) tablespace,
a.autoextensible,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) MaxFileSize,
a.bytes/1048576 filesize,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) - nvl(b.usedbytes,0) free_Mb,
round(100*(1-(nvl(b.Usedbytes,0))/(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 else bytes/1048576 END)),2) "%_FREE",
a.file_name
FROM dba_data_files a,
(SELECT file_id
, sum(bytes)/1048576 Usedbytes
FROM dba_extents GROUP BY file_id) b
WHERE a.file_id = b.file_id(+)
AND a.tablespace_name = '&tablespace_name'
ORDER BY
a.tablespace_name,
a.file_id
/
FILE_ID TABLESPACE AUT MAXFILESIZE FILESIZE FREE_MB %_FREE FILE_NAME
------- ---------- --- ----------- -------- ------- ------ ---------
1 SYSTEM NO 500 500 93.375 18.68 /u05/oracle/oradata/SYSTM_01.dbf
Once you have found the file_id for the datafile in the tablespace that needs expanding then you can increase the size of the tablespace.
