Question

​​​​​​

1. Write a SQL statement to retrieve user account information (username, account_status, default_tablespace, temporary_tablespace, and created date) of your account.

2. Write a SQL statement to retrieve the “USERS” table space information (tablespace name, block_size, initial extent, max_extents).

3. Write a SQL statement to retrieve the physical file information (file name, tablespace name, bytes, blocks, and status).  

Answer 1

I use the field name in caps. Any doubt please comment me. The sql queries are correct. I test it.

Write a SQL statement to retrieve user account information (username, account_status, default_tablespace, temporary_tablespace, and created date) of your account.

Answer 1)

select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED "CREATED DATE" FROM DBA_USERS;

Write a SQL statement to retrieve the “USERS” table space information (tablespace name, block_size, initial extent, max_extents).

Answer 2)

select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,MAX_EXTENTS from dba_tablespaces where TABLESPACE_NAME='USERS';

Write a SQL statement to retrieve the physical file information (file name, tablespace name, bytes, blocks, and status).

Answer 3)

select df.name "FILE NAME",tbl.name "TABLESPACE NAME",df.bytes,df.blocks,df.status from v$datafile df,v$tablespace tbl where df.ts#=tbl.ts#;