Oracle RAC Global View

GV_$ACCESS	GV_$FIXED_VIEW_DEFINITION	GV_$XSTREAM_APPLY_SERVER
GV_$ACTIVE_INSTANCES	GV_$FLASHBACK_DATABASE_LOG	GV_$XSTREAM_CAPTURE
GV_$ACTIVE_SERVICES	GV_$FLASHBACK_DATABASE_LOGFILE	GV_$XSTREAM_MESSAGE_TRACKING
GV_$ACTIVE_SESSION_HISTORY	GV_$FLASHBACK_DATABASE_STAT	GV_$PQ_SESSTAT
GV_$ACTIVE_SESS_POOL_MTH	GV_$FOREIGN_ARCHIVED_LOG	GV_$PQ_SLAVE
GV_$ADVISOR_CURRENT_SQLPLAN	GV_$FS_FAILOVER_HISTOGRAM	GV_$PQ_SYSSTAT
GV_$ADVISOR_PROGRESS	GV_$FS_FAILOVER_STATS	GV_$PQ_TQSTAT
GV_$ALERT_TYPES	GV_$FS_OBSERVER_HISTOGRAM	GV_$PROCESS
GV_$AQ	GV_$GCSHVMASTER_INFO	GV_$PROCESS_GROUP
GV_$AQ1	GV_$GCSPFMASTER_INFO	GV_$PROCESS_MEMORY
GV_$ARCHIVE	GV_$GC_ELEMENT	GV_$PROCESS_MEMORY_DETAIL
GV_$ARCHIVED_LOG	GV_$GC_ELEMENTS_W_COLLISIONS	GV_$PROCESS_MEMORY_DETAIL_PROG
GV_$ARCHIVE_DEST	GV_$GES_BLOCKING_ENQUEUE	GV_$PROPAGATION_RECEIVER
GV_$ARCHIVE_DEST_STATUS	GV_$GES_ENQUEUE	GV_$PROPAGATION_SENDER
GV_$ARCHIVE_GAP	GV_$GG_APPLY_COORDINATOR	GV_$PROXY_ARCHIVEDLOG
GV_$ARCHIVE_PROCESSES	GV_$GG_APPLY_READER	GV_$PROXY_DATAFILE
GV_$ASH_INFO	GV_$GG_APPLY_RECEIVER	GV_$PWFILE_USERS
GV_$ASM_ACFSSNAPSHOTS	GV_$GG_APPLY_SERVER	GV_$PX_BUFFER_ADVICE
GV_$ASM_ACFSVOLUMES	GV_$GLOBALCONTEXT	GV_$PX_INSTANCE_GROUP
GV_$ASM_ACFS_ENCRYPTION_INFO	GV_$GLOBAL_BLOCKED_LOCKS	GV_$PX_PROCESS
GV_$ASM_ACFS_SECURITY_INFO	GV_$GLOBAL_TRANSACTION	GV_$PX_PROCESS_SYSSTAT
GV_$ASM_ALIAS	GV_$GOLDENGATE_CAPABILITIES	GV_$PX_SESSION
GV_$ASM_ATTRIBUTE	GV_$GOLDENGATE_CAPTURE	GV_$PX_SESSTAT
GV_$ASM_CLIENT	GV_$GOLDENGATE_MESSAGETRACKING	GV_$QMON_COORDINATOR_STATS
GV_$ASM_DISK	GV_$GOLDENGATE_TABLE_STATS	GV_$QMON_SERVER_STATS
GV_$ASM_DISKGROUP	GV_$GOLDENGATE_TRANSACTION	GV_$QMON_TASKS
GV_$ASM_DISKGROUP_STAT	GV_$HANG_STATISTICS	GV_$QMON_TASK_STATS
GV_$ASM_DISK_IOSTAT	GV_$HM_CHECK	GV_$QUEUE
GV_$ASM_DISK_STAT	GV_$HM_CHECK_PARAM	GV_$QUEUEING_MTH
GV_$ASM_FILE	GV_$HM_FINDING	GV_$RECOVERY_FILE_STATUS
GV_$ASM_FILESYSTEM	GV_$HM_INFO	GV_$RECOVERY_LOG
GV_$ASM_OPERATION	GV_$HM_RECOMMENDATION	GV_$RECOVERY_PROGRESS
GV_$ASM_TEMPLATE	GV_$HM_RUN	GV_$RECOVERY_STATUS
GV_$ASM_USER	GV_$HS_AGENT	GV_$RECOVER_FILE
GV_$ASM_USERGROUP	GV_$HS_PARAMETER	GV_$REDO_DEST_RESP_HISTOGRAM
GV_$ASM_USERGROUP_MEMBER	GV_$HS_SESSION	GV_$REPLPROP
GV_$ASM_VOLUME	GV_$HVMASTER_INFO	GV_$REPLQUEUE
GV_$ASM_VOLUME_STAT	GV_$INCMETER_CONFIG	GV_$REQDIST
GV_$AUTO_BMR_STATISTICS	GV_$INCMETER_INFO	GV_$RESERVED_WORDS
GV_$AW_AGGREGATE_OP	GV_$INCMETER_SUMMARY	GV_$RESOURCE
GV_$AW_ALLOCATE_OP	GV_$INDEXED_FIXED_COLUMN	GV_$RESOURCE_LIMIT
GV_$AW_CALC	GV_$INSTANCE	GV_$RESTORE_POINT
GV_$AW_LONGOPS	GV_$INSTANCE_CACHE_TRANSFER	GV_$RESULT_CACHE_DEPENDENCY
GV_$AW_OLAP	GV_$INSTANCE_LOG_GROUP	GV_$RESULT_CACHE_MEMORY
GV_$AW_SESSION_INFO	GV_$INSTANCE_RECOVERY	GV_$RESULT_CACHE_OBJECTS
GV_$BACKUP	GV_$IOFUNCMETRIC	GV_$RESULT_CACHE_STATISTICS
GV_$BACKUP_ASYNC_IO	GV_$IOFUNCMETRIC_HISTORY	GV_$RESUMABLE
GV_$BACKUP_CORRUPTION	GV_$IOSTAT_CONSUMER_GROUP	GV_$RFS_THREAD
GV_$BACKUP_DATAFILE	GV_$IOSTAT_FILE	GV_$RMAN_COMPRESSION_ALGORITHM
GV_$BACKUP_DEVICE	GV_$IOSTAT_FUNCTION	GV_$RMAN_CONFIGURATION
GV_$BACKUP_PIECE	GV_$XSTREAM_APPLY_COORDINATOR	GV_$RMAN_OUTPUT
GV_$BACKUP_REDOLOG	GV_$XSTREAM_APPLY_READER	GV_$ROLLSTAT
GV_$BACKUP_SET	GV_$XSTREAM_APPLY_RECEIVER	GV_$ROWCACHE
GV_$BACKUP_SPFILE	GV_$IOSTAT_FUNCTION_DETAIL	GV_$ROWCACHE_PARENT
GV_$BACKUP_SYNC_IO	GV_$IOSTAT_NETWORK	GV_$ROWCACHE_SUBORDINATE
GV_$BGPROCESS	GV_$IO_CALIBRATION_STATUS	GV_$RO_USER_ACCOUNT
GV_$BH	GV_$IR_FAILURE	GV_$RSRCMGRMETRIC
GV_$BLOCKING_QUIESCE	GV_$IR_FAILURE_SET	GV_$RSRCMGRMETRIC_HISTORY
GV_$BSP	GV_$IR_MANUAL_CHECKLIST	GV_$RSRC_CONSUMER_GROUP
GV_$BUFFERED_PUBLISHERS	GV_$IR_REPAIR	GV_$RSRC_CONSUME_GROUP_CPU_MTH
GV_$BUFFERED_QUEUES	GV_$JAVAPOOL	GV_$RSRC_CONS_GROUP_HISTORY
GV_$BUFFERED_SUBSCRIBERS	GV_$JAVA_LIBRARY_CACHE_MEMORY	GV_$RSRC_PLAN
GV_$BUFFER_POOL	GV_$JAVA_POOL_ADVICE	GV_$RSRC_PLAN_CPU_MTH
GV_$BUFFER_POOL_STATISTICS	GV_$LATCH	GV_$RSRC_PLAN_HISTORY
GV_$CALLTAG	GV_$LATCHHOLDER	GV_$RSRC_SESSION_INFO
GV_$CELL	GV_$LATCHNAME	GV_$RULE
GV_$CELL_CONFIG	GV_$LATCH_CHILDREN	GV_$RULE_SET
GV_$CELL_OFL_THREAD_HISTORY	GV_$LATCH_MISSES	GV_$RULE_SET_AGGREGATE_STATS
GV_$CELL_REQUEST_TOTALS	GV_$LATCH_PARENT	GV_$SCHEDULER_RUNNING_JOBS
GV_$CELL_STATE	GV_$LIBCACHE_LOCKS	GV_$SECUREFILE_TIMER
GV_$CELL_THREAD_HISTORY	GV_$LIBRARYCACHE	GV_$SEGMENT_STATISTICS
GV_$CHANNEL_WAITS	GV_$LIBRARY_CACHE_MEMORY	GV_$SEGSPACE_USAGE
GV_$CIRCUIT	GV_$LICENSE	GV_$SEGSTAT
GV_$CLASS_CACHE_TRANSFER	GV_$LISTENER_NETWORK	GV_$SEGSTAT_NAME
GV_$CLASS_PING	GV_$LOADISTAT	GV_$SERVICEMETRIC
GV_$CLIENT_STATS	GV_$LOADPSTAT	GV_$SERVICEMETRIC_HISTORY
GV_$CLONEDFILE	GV_$LOBSTAT	GV_$SERVICES
GV_$CLUSTER_INTERCONNECTS	GV_$LOCK	GV_$SERVICE_EVENT
GV_$CONFIGURED_INTERCONNECTS	GV_$LOCKED_OBJECT	GV_$SERVICE_STATS
GV_$CONTEXT	GV_$LOCKS_WITH_COLLISIONS	GV_$SERVICE_WAIT_CLASS
GV_$CONTROLFILE	GV_$LOCK_ACTIVITY	GV_$SERV_MOD_ACT_STATS
GV_$CONTROLFILE_RECORD_SECTION	GV_$LOCK_ELEMENT	GV_$SESSION
GV_$COPY_CORRUPTION	GV_$LOCK_TYPE	GV_$SESSION_BLOCKERS
GV_$CORRUPT_XID_LIST	GV_$LOG	GV_$SESSION_CONNECT_INFO
GV_$CPOOL_CC_INFO	GV_$LOGFILE	GV_$SESSION_CURSOR_CACHE
GV_$CPOOL_CC_STATS	GV_$LOGHIST	GV_$SESSION_EVENT
GV_$CPOOL_CONN_INFO	GV_$LOGMNR_CALLBACK	GV_$SESSION_FIX_CONTROL
GV_$CPOOL_STATS	GV_$LOGMNR_CONTENTS	GV_$SESSION_LONGOPS
GV_$CR_BLOCK_SERVER	GV_$LOGMNR_DICTIONARY	GV_$SESSION_OBJECT_CACHE
GV_$CURRENT_BLOCK_SERVER	GV_$LOGMNR_DICTIONARY_LOAD	GV_$SESSION_WAIT
GV_$DATABASE	GV_$LOGMNR_LATCH	GV_$SESSION_WAIT_CLASS
GV_$DATABASE_BLOCK_CORRUPTION	GV_$LOGMNR_LOGFILE	GV_$SESSION_WAIT_HISTORY
GV_$DATABASE_INCARNATION	GV_$LOGMNR_LOGS	GV_$SESSMETRIC
GV_$DATABASE_KEY_INFO	GV_$LOGMNR_PARAMETERS	GV_$SESSTAT
GV_$DATAFILE	GV_$LOGMNR_PROCESS	GV_$SESS_IO
GV_$DATAFILE_COPY	GV_$LOGMNR_REGION	GV_$SESS_TIME_MODEL
GV_$DATAFILE_HEADER	GV_$LOGMNR_SESSION	GV_$SES_OPTIMIZER_ENV
GV_$DATAGUARD_CONFIG	GV_$LOGMNR_STATS	GV_$SGA
GV_$DATAGUARD_STATS	GV_$LOGMNR_TRANSACTION	GV_$SGAINFO
GV_$DATAGUARD_STATUS	GV_$LOGSTDBY	GV_$SGASTAT
GV_$WLM_PC_STATS	GV_$LOGSTDBY_PROCESS	GV_$SGA_CURRENT_RESIZE_OPS
GV_$WORKLOAD_REPLAY_THREAD	GV_$LOGSTDBY_PROGRESS	GV_$SGA_DYNAMIC_COMPONENTS
GV_$XML_AUDIT_TRAIL	GV_$LOGSTDBY_STATE	GV_$SGA_DYNAMIC_FREE_MEMORY
GV_$DATAPUMP_JOB	GV_$LOGSTDBY_STATS	GV_$XSTREAM_OUTBOUND_SERVER
GV_$DATAPUMP_SESSION	GV_$LOGSTDBY_TRANSACTION	GV_$XSTREAM_TABLE_STATS
GV_$DBFILE	GV_$LOG_HISTORY	GV_$XSTREAM_TRANSACTION
GV_$DBLINK	GV_$MANAGED_STANDBY	GV_$SGA_RESIZE_OPS
GV_$DB_CACHE_ADVICE	GV_$MAP_COMP_LIST	GV_$SGA_TARGET_ADVICE
GV_$DB_OBJECT_CACHE	GV_$MAP_ELEMENT	GV_$SHARED_POOL_ADVICE
GV_$DB_PIPES	GV_$MAP_EXT_ELEMENT	GV_$SHARED_POOL_RESERVED
GV_$DB_TRANSPORTABLE_PLATFORM	GV_$MAP_FILE	GV_$SHARED_SERVER
GV_$DEAD_CLEANUP	GV_$MAP_FILE_EXTENT	GV_$SHARED_SERVER_MONITOR
GV_$DELETED_OBJECT	GV_$MAP_FILE_IO_STACK	GV_$SORT_SEGMENT
GV_$DETACHED_SESSION	GV_$MAP_LIBRARY	GV_$SORT_USAGE
GV_$DIAG_INFO	GV_$MAP_SUBELEMENT	GV_$SPPARAMETER
GV_$DISPATCHER	GV_$MAX_ACTIVE_SESS_TARGET_MTH	GV_$SQL
GV_$DISPATCHER_CONFIG	GV_$MEMORY_CURRENT_RESIZE_OPS	GV_$SQLAREA
GV_$DISPATCHER_RATE	GV_$MEMORY_DYNAMIC_COMPONENTS	GV_$SQLAREA_PLAN_HASH
GV_$DLM_ALL_LOCKS	GV_$MEMORY_RESIZE_OPS	GV_$SQLCOMMAND
GV_$DLM_CONVERT_LOCAL	GV_$MEMORY_TARGET_ADVICE	GV_$SQLFN_ARG_METADATA
GV_$DLM_CONVERT_REMOTE	GV_$METRIC	GV_$SQLFN_METADATA
GV_$DLM_LATCH	GV_$METRICGROUP	GV_$SQLPA_METRIC
GV_$DLM_LOCKS	GV_$METRICNAME	GV_$SQLSTATS
GV_$DLM_MISC	GV_$METRIC_HISTORY	GV_$SQLSTATS_PLAN_HASH
GV_$DLM_RESS	GV_$MTTR_TARGET_ADVICE	GV_$SQLTEXT
GV_$DLM_TRAFFIC_CONTROLLER	GV_$MUTEX_SLEEP	GV_$SQLTEXT_WITH_NEWLINES
GV_$DNFS_CHANNELS	GV_$MUTEX_SLEEP_HISTORY	GV_$SQL_BIND_CAPTURE
GV_$DNFS_FILES	GV_$MVREFRESH	GV_$SQL_BIND_DATA
GV_$DNFS_SERVERS	GV_$MYSTAT	GV_$SQL_BIND_METADATA
GV_$DNFS_STATS	GV_$NFS_CLIENTS	GV_$SQL_CS_HISTOGRAM
GV_$DYNAMIC_REMASTER_STATS	GV_$NFS_LOCKS	GV_$SQL_CS_SELECTIVITY
GV_$EMON	GV_$NFS_OPEN_FILES	GV_$SQL_CS_STATISTICS
GV_$ENABLEDPRIVS	GV_$NLS_PARAMETERS	GV_$SQL_CURSOR
GV_$ENCRYPTED_TABLESPACES	GV_$NLS_VALID_VALUES	GV_$SQL_FEATURE
GV_$ENCRYPTION_WALLET	GV_$OBJECT_DEPENDENCY	GV_$SQL_FEATURE_DEPENDENCY
GV_$ENQUEUE_LOCK	GV_$OBJECT_DML_FREQUENCIES	GV_$SQL_FEATURE_HIERARCHY
GV_$ENQUEUE_STAT	GV_$OBSOLETE_PARAMETER	GV_$SQL_HINT
GV_$ENQUEUE_STATISTICS	GV_$OFFLINE_RANGE	GV_$SQL_JOIN_FILTER
GV_$EVENTMETRIC	GV_$OPEN_CURSOR	GV_$SQL_MONITOR
GV_$EVENT_HISTOGRAM	GV_$OPTION	GV_$SQL_OPTIMIZER_ENV
GV_$EVENT_NAME	GV_$OSSTAT	GV_$SQL_PLAN
GV_$EXECUTION	GV_$PARALLEL_DEGREE_LIMIT_MTH	GV_$SQL_PLAN_MONITOR
GV_$FAST_START_SERVERS	GV_$PARAMETER	GV_$SQL_PLAN_STATISTICS
GV_$FAST_START_TRANSACTIONS	GV_$PARAMETER2	GV_$SQL_PLAN_STATISTICS_ALL
GV_$FILEMETRIC	GV_$PARAMETER_VALID_VALUES	GV_$SQL_REDIRECTION
GV_$FILEMETRIC_HISTORY	GV_$PERSISTENT_PUBLISHERS	GV_$SQL_SHARED_CURSOR
GV_$FILESPACE_USAGE	GV_$PERSISTENT_QMN_CACHE	GV_$SQL_SHARED_MEMORY
GV_$FILESTAT	GV_$PERSISTENT_QUEUES	GV_$SQL_WORKAREA
GV_$FILE_CACHE_TRANSFER	GV_$PERSISTENT_SUBSCRIBERS	GV_$SQL_WORKAREA_ACTIVE
GV_$FILE_HISTOGRAM	GV_$PGASTAT	GV_$SQL_WORKAREA_HISTOGRAM
GV_$FILE_OPTIMIZED_HISTOGRAM	GV_$PGATARGET_ADVICE_HISTOGRAM	GV_$SSCR_SESSIONS
GV_$FILE_PING	GV_$PGA_TARGET_ADVICE	GV_$STANDBY_LOG
GV_$FIXED_TABLE	GV_$POLICY_HISTORY	GV_$STATISTICS_LEVEL
GV_$STATNAME	GV_$SYSTEM_FIX_CONTROL	GV_$TIMEZONE_FILE
GV_$STREAMS_APPLY_COORDINATOR	GV_$SYSTEM_PARAMETER	GV_$TIMEZONE_NAMES
GV_$STREAMS_APPLY_READER	GV_$SYSTEM_PARAMETER2	GV_$TOPLEVELCALL
GV_$STREAMS_APPLY_SERVER	GV_$SYSTEM_WAIT_CLASS	GV_$TRANSACTION
GV_$STREAMS_CAPTURE	GV_$SYS_OPTIMIZER_ENV	GV_$TRANSACTION_ENQUEUE
GV_$STREAMS_MESSAGE_TRACKING	GV_$SYS_TIME_MODEL	GV_$TRANSPORTABLE_PLATFORM
GV_$STREAMS_POOL_ADVICE	GV_$TABLESPACE	GV_$TSM_SESSIONS
GV_$STREAMS_POOL_STATISTICS	GV_$TEMPFILE	GV_$TYPE_SIZE
GV_$STREAMS_TRANSACTION	GV_$TEMPORARY_LOBS	GV_$UNDOSTAT
GV_$SUBCACHE	GV_$TEMPSTAT	GV_$VERSION
GV_$SUBSCR_REGISTRATION_STATS	GV_$TEMP_CACHE_TRANSFER	GV_$VPD_POLICY
GV_$SYSAUX_OCCUPANTS	GV_$TEMP_EXTENT_MAP	GV_$WAITCLASSMETRIC
GV_$SYSMETRIC	GV_$TEMP_EXTENT_POOL	GV_$WAITCLASSMETRIC_HISTORY
GV_$SYSMETRIC_HISTORY	GV_$TEMP_PING	GV_$WAITSTAT
GV_$SYSMETRIC_SUMMARY	GV_$TEMP_SPACE_HEADER	GV_$WALLET
GV_$SYSSTAT	GV_$THREAD	GV_$WLM_PCMETRIC
GV_$SYSTEM_CURSOR_CACHE	GV_$THRESHOLD_TYPES	GV_$WLM_PCMETRIC_HISTORY
GV_$SYSTEM_EVENT	GV_$TIMER	GV_$_LOCK

EXPDP AND IMPDP COMMANDS

----------------IMPDP FULL SCHEMA------------------------------
impdp system/<syspassword>  transform=segment_attributes:n DUMPFILE=<dumpfile>.dmp logfile=<logfilename>.log remap_schema=<expdp schema>:<impdp schema> PARALLEL=5

--------------IMPDP for table--------------------------------
impdp system/<syspassword> tables=<schema name>.<tablename> DUMPFILE=<dumpfile>.dmp logfile=<dumpfile>.log transform=segment_attributes:n 

--------------expdp for full database with parallel--------------
expdp system/<syspassword> FULL=y DUMPFILE=<filename>.dmp PARALLEL=5 LOGFILE=<logfilename>.log JOB_NAME=<jobname>

----------------expdp for a table----------------------------------
expdp system/<syspassword> tables=<schemaname>.<tablename> DUMPFILE=<filename>.dmp PARALLEL=5 LOGFILE=<logfilename>.log JOB_NAME=<jobname>

--------------how to stop and start job-----------------------------
IMPORT>STOP_JOB=IMMEDIATE;
IMPORT>START_JOB;
IMPORT>STATUS;
IMPORT>HELP;

---------------EXPDP for a schema--------------------
expdp system/<systempasword> SCHEMAS=<schemaname> DUMPFILE=<dumpfilename>.dmp LOGFILE=<logfilename>.log PARALLEL=5

--------------IMPDP for a schema---------------------------------
impdp system/<systempasword> SCHEMAS=<schemadname> DUMPFILE=<dumpfilename>.dmp LOGFILE=<logfilename>.log PARALLEL=5

------------IMPDP with exclude option----------------------------
impdp system/<password> SCHEMAS=<schemaname> REMAP_SCHEMA=<expdp schema>:<impdpschema>
DUMPFILE=<dumpfilename>.dmp 
EXCLUDE=constraint, ref_constraint, index,materialized_view  
logfile=<logfilename>.log 

-----------IMPDP with SQLFILE option------------------------
impdp <schemaname>/<password> DUMPFILE=<dumpfile>.dmp SQLFILE=<filename>.sql

----------How to use Exdp and Impdp over Network Link : Oracle DB--------------------

#Login in sqlplus 
sqlplus / as sysdba

#Create a connection link for the database which you want to export 
create database link remotelink connect to targetdbuser identified by targetdbpassword using 'hostname:port/sid'
#check connection 
select * from dual@remotelink

# create a local directory where the dump file will be stored and map it to your database dir by creating 
create directory dumpdir as '<path>';
# give permission to local user by whom we will be running expdp
GRANT read, write ON DIRECTORY dumpdir TO localdb;
# check if the dir is created 
 select directory_name, directory_path from dba_directories ;

# You might get following error , if you do not grant read write access of directory to the user .
#ORA-39002: invalid operation
#ORA-39070: Unable to open the log file.
#ORA-39087: directory name dumpdir is invalid

GRANT EXP_FULL_DATABASE to targetdbuser;

expdp userid=localdb/localdb@//localhost:1521/ORCL dumpfile=testdump.dmp logfile=testdump.log SCHEMAS=myschema directory=dumpdir 

grant imp_full_database to myschema ;

impdp myschema/mytest@//localhost:1521/orcl schemas=myschema directory=dumpdir dumpfile=testdump.dmp logfile=impdpnewtest1.log 


------------------------------Schema level with content dataonly and table exist action truncate--------------
impdp system/<password> schemas=<schemaname> content=data_only directory=<directoryname> dumpfile=<dumpfilename>.dmp logfile='<logfilename>.log' TABLE_EXISTS_ACTION=TRUNCATE

-----------------------------Schema level with content dataonly and table exist action replace---------------------
impdp system/<password> schemas=<schema> directory=<dictionary> dumpfile=<dumpfilename>.dmp logfile='<logfilename>.log' TABLE_EXISTS_ACTION=REPLACE

Note: TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE} 

SP2-0308: cannot close spool file

SP2-0308: cannot close spool file

This Error popup due to disk partition is going to full so to remove this error delete something from your hard disk partition..

Database properties views

Below are some query that will help you to check current database properties:

SQL>SELECT * FROM DATABASE_PROPERTIES;

SQL> SELECT  * FROM DATABASE_SUMMARY;

If you want the full version information for your DB then:

SQL>SELECT * FROM v$version;

If you want your DB parameters then:

SQL>SELECT * FROM v$parameter;

If you want more information about your DB instance then:

SQL>SELECT * FROM v$database;

SQL>SELECT * FROM v$instance;

If you want the "size" of your database then this will give you a close enough calculation:

SQL>SELECT SUM(bytes / (1024*1024)) "DB Size in MB" FROM dba_data_files;

ORA-02391:exceeded simultaneous SESSIONS_PER_USER limit

ORA-02391 is user session error. It means you need to kill some user session if its not required or you need to increase sessions per use limit. Killing sessions can be very destructive if you kill the wrong session so be careful when killing session. for that first we need to identify that sessions for that query is given below. . SQL>SELECT s.inst_id, s.sid,s.serial#,p.spid,s.username,s.program FROM   gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE  s.type != 'BACKGROUND'; The basic syntax for killing sessions is given below. SQL>ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' immediate; if you want to increase user session process are given below: 1) SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; 2) ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; 3) SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; 4) ALTER PROFILE <profile name>  LIMIT sessions_per_user 80;