Find out how much database space you used up

If you ever want to know, in Oracle database, how much space you have used up so far (in terms of GB) by all of the objects in your schema, you can use the below SQL

SELECT a.owner, tablespace_name ,
sum(a.blocks*16/1024/1024) AS GB
FROM all_tables a
WHERE owner = 'YOUR_SCHEMA_NAME'
group by a.owner, tablespace_name;

Query keeps running, Not sure if it’s done

There would be instances at work when you are querying against a big table, or creating a big table, and you have executed the SQL, and it keeps running for a long time, and status is showing nothing, except it’s running.

And you want to find out how long would it run ? Will it end ever ? or at the minimum, is it even running or the tool just got hung ?

In Oracle database, You can use the below query in another window or most likely another query tool, to see if there are any records. Usually there are records that shows up for active query that run under your user name.

select * from v$session_longops where username = ‘YOUR_ORACLE_SCHEMANAME‘
and (Time_Remaining > 0 or Time_Remaining is null)
ORDER BY START_TIME DESC;

You can use the below query to find the percentage completion, although most times it’s not accurate, because the long running query triggers multiple record reads, and so the percentage may come and go (but on the positive side, you at least know the query is running ,and will complete and the application isn’t hung)

select sum(sofar), sum(totalwork), round(((sum(sofar)/sum(totalwork))*100),2)|| ‘ %’ “Percent Complete”, sum(Time_Remaining) from v$session_longops
where username = ‘YOUR_ORACLE_SCHEMANAME‘
and time_remaining > 0;

Hope this helps 🙂

Execute SQL script using Windows Task Scheduler

Say, you have a SQL script ready, and you want that to run automatically without you kick starting it every single time. You can do this via ‘adding the script to Windows Task Scheduler’, and configuring it.

Example script (saved as test.sql):

set echo on
set time on
set timing on

spool C:\some_folder\logfile\test.log;

drop table test_table;

create table test_table
(name varchar2(30), age int);

insert into test_table (name, age)
select ‘senthamizh selvan’, 20 from dual
union
select ‘Kanmani Anbodu’, 35 from dual;

commit;

spool off
exit;

Note: Spool commands where I’m writing the logs has the ‘Full Path’ of the log file.

We are going to use ‘sqlplus’ – a command line utility for Oracle Database / Query and Windows Task Scheduler to automate this SQL.

You can find ‘Task Scheduler’ application in the ‘Administrative Tools’ section (in Control Panels) for Windows server.

Open ‘Task Scheduler’ and Choose’Create Basic Task’ under Action.

Give a Name to your task, and Click ‘Next’

 

Choose a schedule -daily, weekly, etc ….

And Click Next

I chose ‘Daily’ and gave the ‘Time’ – Click Next once done

Click Next

Enter ‘sqlplus’ and in the arguments add “username/password@databse_name @C:\somefolder\….\test.sql”

Make sure you enter the full path of the location to the file in the arguments.

Click Finish

And your job is ready to run.

Click on ‘Task Scheduler Library’ to confirm if your Job is there in the scheduler

You can ‘right-click’ on the job to ‘Run’ manually once to verigy if its executing to completion or if you want to change any schedule/command/arguments – click on properties, and you will be able to change and Edit as needed.

Also, you can make the job to run in the server, even when you are not logged into the server, by choosing that option from the ‘General’ tab. But to do so, you should be an administrator or have the ‘batch’ access privilege.

See if you can make you ‘batch access’ by doing this

This policy is accessible by opening the Control Panel , Administrative Tools , and then Local Security Policy . In the Local Security Policy window, click Local Policy , User Rights Assignment , and then Logon as batch job

or you will be need to check with your system administrator

 

Also, you can check your log file to confirm you are seeing what you are expecting to !!

 

 

Connect to sqlplus when tnsnames.ora file is not there

You want to connect to  Oracle databse using sqlplus utility. You can do so by, entering this command in your terminal/shell/other command line program.

sqlplus username/password@servicename

For this to work, you or your environment should know where your tnsnames.ora file is. If not, this command won’t work. You can try finding this file in the server box, or using the environment variable $TNS_ADMIN

But if you couldn’t find, you could use the below command, to specify your Oracle database credentials directly to sqlplus command, and connect to the Oracle database.

1. Enter command: sqlplus ‘your_username@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=your_host_name_value)(Port=your_port))(CONNECT_DATA=(SERVICE_NAME=your_service_name)))’
2. Enter the Password

Hope this helps;