SQL> show release
release 1002000300
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 – Production
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> conn system/manager
SQL> select DBID, NAME from v$database;
DBID NAME
---------- ---------
3235629315 WORK
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select current_scn from v$database;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------
db_name string vinay
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> conn scott/tiger@sample;
SQL> select owner,table_name from all_tables order by owner,table_name;
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PLAN_TABLE USERS
DEPT USERS
EMP USERS
BONUS USERS
SALGRADE USERS
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select file_name, tablespace_name from dba_data_files;
SQL> select tablespace_name,file_name from dba_temp_files;
SQL> select group#,member from v$logfile order by group#;
SQL> select * from v$log;
SQL> select log_mode from v$database;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> startup mount
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1292036 bytes
Variable Size 201328892 bytes
Database Buffers 402653184 bytes
Redo Buffers 7094272 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL>SELECT OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS where status='INVALID'
group by object_type; /*to see what objects are invalid*/
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> show sga
Total System Global Area 612368384 bytes
Fixed Size 1292036 bytes
Variable Size 184551676 bytes
Database Buffers 419430400 bytes
Redo Buffers 7094272 bytes
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 1292036
Variable Size 184551676
Database Buffers 419430400
Redo Buffers 7094272
SQL> select component,current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 163577856
large pool 4194304
java pool 16777216
streams pool 0
DEFAULT buffer cache 419430400
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
ASM Buffer Cache 0
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select * from dba_tablespace_usage_metrics;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX 37888 4194302 .903320743
SYSTEM 61560 4194302 1.46770547
TEMP 0 4194302 0
UNDOTBS1 288 4194302 .006866458
USERS 112 4194302 .002670289
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select tablespace_name,status,contents,extent_management extents,segment_space_management free_
space from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS EXTENTS FREE_S
------------------------------ --------- --------- ---------- ------
SYSTEM ONLINE PERMANENT LOCAL MANUAL
UNDOTBS1 ONLINE UNDO LOCAL MANUAL
SYSAUX ONLINE PERMANENT LOCAL AUTO
TEMP ONLINE TEMPORARY LOCAL MANUAL
USERS READ ONLY PERMANENT LOCAL AUTO
SQL> select tablespace_name, ((bytes/1024)/1024) MB from dba_data_files;
TABLESPACE_NAME MB
------------------------------ ----------
USERS 5
SYSAUX 300
UNDOTBS1 55
SYSTEM 490
For small file tablespaces:
SQL> alter database datafile
'E:\ORACLE\ORADATA\SAMPLE\UNDOTBS01.DBF'
RESIZE 75M;
SQL> ALTER DATABASE DATAFILE '/u07/oracle/oradata/train/media01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
For bigfile tablespaces:
SQL> ALTER TABLESPACE media RESIZE 1G;
SQL> ALTER TABLESPACE media AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select DBMS_METADATA.GET_DDL('TABLESPACE','USERS') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
-----------------------------------------------------------------------
CREATE TABLESPACE "USERS" DATAFILE
'E:\ORACLE\ORADATA\SAMPLE\USERS01.DBF'
Note that TABLE and TEST are case-sensitive
SQL> select DBMS_METADATA.GET_DDL('TABLE', 'TEST') from dual;
DBMS_METADATA.GET_DDL('TABLE','TEST')
----------------------------------------------------------------------------
CREATE TABLE "SAMPLE"."TEST"
( "TNAME" VARCHAR2(10)
) PCTFREE 10 PCTUS
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> comment on table sample is ‘This is my comment’;
SQL> select owner, table_name, comments from all_tab_comments where table_name='SAMPLE';
OWNER TABLE_NAME
------------------------------ ------------------------------
COMMENTS
-----------------------------------------------------------------------------------------
SCOTT SAMPLE
This is my comment
SQL> comment on column sample.sname is 'sample column comment';
SQL> select table_name,column_name, comments from all_col_comments where table_name='SAMPLE';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
COMMENTS
----------------------------------------------------------------------------------------------
SAMPLE SNAME
sample column comment
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select constraint_name,constraint_type, r_constraint_name from all_constraints where table_name='EMP';
CONSTRAINT_NAME C R_CONSTRAINT_NAME
------------------------------ - ------------------------------
PK_EMP P
FK_DEPTNO R PK_DEPT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL>GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
SQL>ALTER USER SCOTT DEFAULT TABLESPACE USERS;
SQL>ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select sid,serial#,username,server,program from v$session;
select status, substr(username,1,22) username, substr(osuser,1,15) osuser,
substr(machine,1,15) machine, substr(program,1,15) program
from v$session
where username is not null
order by 2, 1;
SQL> select sessions_current,sessions_highwater from v$license;
SESSIONS_CURRENT SESSIONS_HIGHWATER
---------------- ------------------
2 5
SQL> select name,status,messages,idle,busy,bytes,breaks from v$dispatcher;
NAME STATUS MESSAGES IDLE BUSY BYTES BREAKS
---- ---------------- ---------- ---------- ---------- ---------- ----------
D000 WAIT 0 531628 8 0 0
SQL> select * from v$queue;
PADDR TYPE QUEUED WAIT TOTALQ
-------- ---------- ---------- ---------- ----------
00 COMMON 0 0 0
3464F39C DISPATCHER 0 0 0
SQL> select circuit,dispatcher,server,waiter WTR,status,queue,bytes from v$circuit;
SQL> select name,status,messages,bytes,idle,busy,requests from v$shared_server;
NAME STATUS MESSAGES BYTES IDLE BUSY REQUESTS
---- ---------------- ---------- ---------- ---------- ---------- ----------
S000 WAIT(COMMON) 0 0 596321 0 0
SQL> select username,program,server from v$session;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2612432
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select username, default_tablespace from dba_users; To get the default tablespace for a user
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select property_name, property_value from database_properties where
property_name like ‘%TABLESPACE’;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> alter session recyclebin=on;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select reason, metric_value from dba_outstanding_alerts;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Enable logging. On your primary database, instruct Oracle Database to force all logging of changes to the redo, even if nologging or unrecoverable data loads are performed:
SQL> alter database force logging;
Verify that forced logging has been enabled on your primary database, by issuing the following:
SQL> select force_logging from v$database;
Enable supplemental logging. Enabling supplemental logging will direct Oracle Database to add a small amount of extra information to the redo stream. The SQL Apply process uses this additional information to maintain tables being replicated. On your primary database, enable supplemental logging as follows:
SQL> alter database add supplemental log data (primary key, unique index) columns;
SQL> alter system archive log current;
You can verify that supplemental logging has been enabled, by issuing the following on your primary database:
SQL> select supplemental_log_data_pk, supplemental_log_data_ui
from v$database;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/admin/myDB/arch
Oldest online log sequence 345
Next log sequence to archive 347
Current log sequence 347
SQL> select * from log_history;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> explain plan for select * from emp;
Explained.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 448 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
OR
SQL> select * from table (dbms_xplan.display);
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#Access system statistics values stored in dynamic performance tables
SQL> conn system/manager
SQL> select statistic#, name, value from v$sysstat where rownum<=20;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE SYNONYM "SCOTT"."SAMPLE" FOR "SCOTT"."EMP";
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Get the file name and max size of datafile
SQL> select file_name, bytes from dba_data_files where bytes=(select max(bytes) from dba_data_files);
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The user can view the dba_ and V$ tables if granted the following role
SQL> grant select_catalog_role to sample;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Grant unlimited quota on tablespace
SQL> alter user sample quota unlimited on users;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select tablespace_name, sum((bytes/1024)/1024) from dba_data_files group by tablespace_name;
SQL> select tablespace_name, sum((bytes/1024)/1024) from dba_free_space group by tablespace_name;
SQL> select * from system_privilege_map;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select next_run_date from sys.dba_scheduler_jobs;
NEXT_RUN_DATE
-----------------------------------------------------------------
13-11-08 03:00:00.000000 AM US/PACIFIC
12-11-08 04:24:40.000000 PM -05:00
12-11-08 02:17:06.000000 PM -07:00
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL > select * from nls_session_parameters;
PARAMETER VALUE
------------------------------ -----------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY INDIA
NLS_CURRENCY Rs
NLS_ISO_CURRENCY INDIA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MM-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH12:MI:SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MM-RR HH12:MI:SSXFF AM
PARAMETER VALUE
------------------------------ -----------------------------------
NLS_TIME_TZ_FORMAT HH12:MI:SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MM-RR HH12:MI:SSXFF AM TZR
NLS_DUAL_CURRENCY Rs
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select to_char(sysdate, 'MM-DD-yyyy HH24:MI') from dual;
TO_CHAR(SYSDATE,
----------------
11-12-2008 18:19
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> STARTUP RESTRICT // One with RESTRICTED SESSION privilege can only connect to db
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
To see which archived logs are applied to the database:
SQL> select sequence#, applied from v$archivedlog order by sequence#;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Copying data from one column to another column in same table
SQL> select * from sample;
SNO SNAME LOCATION
---------- -------------------- --------------------
1 vinay NJ
2 suman KS
3 ravi WA
SQL> update [table_name] set [new_column]=[old_column];
The entries from old column will be copied to the new column.
SQL> update sample set location=sname;
4 rows updated.
SQL> select * from sample;
SNO SNAME LOCATION
---------- -------------------- --------------------
1 vinay vinay
2 suman suman
3 ravi ravi
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> create table test(tno, tname) as select sno,location from sample;
Table created.
Primary Key and Foreign Key:
SQL> create table master (mno number(3), mname varchar(20), dept varchar(20),
2 constraint master_mno_pk primary key (mno));
SQL> create table child1 (cno number(3), cname varchar(20),
2 constraint child1_cno_fk foreign key (cno)
3 references master(mno));
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Identify Locks
SQL> select oracle_username os_user_name, locked_mode, object_name, object_type from v$locked_object a,dba_objects b where a.object_id = b.object_id;
OS_USER_NAME LOCKED_MODE OBJECT_NAME
------------------------------ ----------- ---------------------------------------------------------
SCOTT 3 SAMPLE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Current time
SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
2 from dual;
Current Time
------------------------
Tue 10-Feb-2009 00:40:27
[OR]
SQL> alter session set nls_date_format='dd-mon-yyyy HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------------------
10-feb-2009 00:47:48
[OR]
SQL> select current_date from dual;
CURRENT_DATE
--------------------
10-feb-2009 00:48:52
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
----------------------------------------------------
-05:00
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query the DBA_STREAMS_UNSUPPORTED data dictionary view to determine which database objects are not supported by Streams. If unsupported database objects are not excluded, then capture errors will result
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select platform_name from v$database;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
From Metalink Supoort to resume ur backups:
BACKUP NOT BACKED UP SINCE TIME 'SYSDATE-1' DATABASE for resume your backup.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select * from v$session where type ='BACKGROUND';
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Partitioning:
SQL> select partition_name, high_value from user_tab_partitions where table_name = 'DAILY_SALES' order by partition_position;
SQL> alter table daily_sales merge partitions for(to_date('01-JAN-2007','dd-MON-yyyy')) , for(to_date('01-FEB-2007','dd-MON-yyyy'))into partition p_31_2007;
SQL> alter table daily_sales rename partition sys_p41 to p_Jan_2007;
-- Look at the partitioned tables.
select table_name, partitioning_type, ref_ptn_constraint_name
from user_part_tables where table_name in ('CUSTOMER_ORDERS','CUSTOMER_ORDER_ITEMS');
TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ --------- ------------------------------
CUSTOMER_ORDERS RANGE
CUSTOMER_ORDER_ITEMS REFERENCE CUSTOMER_ORDER_ITEMS_ORDERS_FK
-- Look at the partitions created.
select table_name, partition_name, high_value
from user_tab_partitions where table_name in ('CUSTOMER_ORDERS','CUSTOMER_ORDER_ITEMS')
order by partition_position, table_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------- --------------------
CUSTOMER_ORDERS P_BEFORE_JAN_2007 TO_DATE(' 2007-01-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
CUSTOMER_ORDER_ITEMS P_BEFORE_JAN_2007
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
info about registered databases in RMAN catalog.
select dbid, name, resetlogs_time from rc_database;
RMAN> CONNECT CATALOG rman/cat@catdb
RMAN> SQL 'SELECT NAME FROM RC_DATABASE';
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Below Query you can check the rman backup status of all databases from catalog database.
set pagesize 5000;
set linesize 400;
col OBJECT_TYPE for a10;
col STATUS for a15;
col operation for a15;
select DB_KEY,DB_NAME,STATUS,START_TIME,END_TIME,INPUT_BYTES/1024/1024/1024 INPUT_BYTES,OUTPUT_BYTES/1024/1024/1024 OUTPUT_BYTES,OBJECT_TYPE,OPERATION from rc_rman_status where START_TIME >= (sysdate-1) and OBJECT_TYPE not like '%ARCH%' and OPERATION not in ('DELETE','LIST') order by db_name,START_TIME;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select * from v$recover_file; // to see corrupted datafiles;
SELECT TABLESPACE_NAME, SUM (BYTES)/1024/1024 FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Check user sessions:
select status, substr(username,1,22) username, substr(osuser,1,15) osuser,
substr(machine,1,15) machine, substr(program,1,15) program
from v$session
where username is not null
order by 2, 1;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
set scan on
set pagesize 500
set linesize 500
column sid format a5
column osuser format a15
column program format a30
column opname format a30
column elapsed format a9
column remaining format a9
column updated format a8
column Comp format a5
SQL> clear columns //to clear formatted columns
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
First zero out the raw device before using it for new datafiles.
$ dd if=/dev/*zero* of=/dev/*raw*/raw2
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Size of a table:
select segment_name, sum(bytes)/(1024*1024) table_size_meg
from user_extents
where segment_type='TABLE' and segment_name = 'TABLE_NAME' group by segment_name;
OR
CREATE OR REPLACE FUNCTION get_table_size
(t_table_name VARCHAR2)RETURN NUMBER IS
l_size NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024)
INTO l_size
FROM user_extents
WHERE segment_type='TABLE'
AND segment_name = t_table_name;
RETURN l_size;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/
Example:
SELECT get_table_size('EMP') Table_Size from dual;
Result:
Table_Size
0.0625
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RMAN backup status in 10g:
SELECT JD.COMMAND_ID,JD.STATUS,JD.OUTPUT_DEVICE_TYPE,JD.START_TIME,JD.TIME_TAKEN_DISPLAY,JD.END_TIME--,JD.*
FROM V$RMAN_BACKUP_JOB_DETAILS JD;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sizing Redo Logs:
The value for optimal_logfile_size is expressed in megabytes and it changes frequently, based on the DML load on your database. For example,
SQL> SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;
OPTIMAL_LOGFILE_SIZE
--------------------
256
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Determine size of database:
select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;
Used space within the database:
SQL> SELECT SUM(bytes)/1024/1024 "Meg" FROM dba_segments;
Database size and free space:
col "Database Size" format a20
col "Free space" format a20
select round(sum(used.bytes) / 1024 / 1024 ) || ' MB' "Database Size"
, round(free.p / 1024 / 1024) || ' MB' "Free space"
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p
/
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS where status='INVALID'
group by object_type;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT segment_name, tablespace_name
FROM dba_segments
WHERE segment_name IN ('OBJ$', 'COL$', 'IND$', 'TAB$');
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from v$session_longops where username='SCOTT';
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='AUD$';
TABLESPACE_NAME
--------------------------------------------------------------------------------
SYSTEM
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT NAME, VALUE, ISDEFAULT, DESCRIPTION, ISSES_MODIFIABLE SES_MODIFIABLE,
ISSYS_MODIFIABLE SYS_MODIFIABLE, UPDATE_COMMENT,ISINSTANCE_MODIFIABLE,
ISDEPRECATED
FROM v$parameter ORDER BY name;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment